What version of iBATIS are you using?
On Tue, 2006-09-26 at 10:19 +1000, Zoran Avtarovski wrote: > I’m having an issue with my N+1 resultset. It’s throwing up a quirk I > haven’t seen before. > > I’m building a menu-submenu structure. The resultset I’m getting from > mysql is as expected (see below). Each main menu item (label, value > set) has a list of submenu items (test_id, common_name set) > > The problem I have is that submenu items appear in multiple main > menus, but when I apply the n+1 ibatis solution sub menu items only > appear for the first main menu they are in and not in the rest. Using > the example below Hepatitis C is only included in the Gastroenterology > main menu but it should also be in Hepatitis and Infectious diseases. > > I'm sure I've done something wrong but I can't see what. Any help > would be greatly appreciated. > > Below is all the relevant info: > > > <resultMap class="com.sparecreative.entity.ListForm" id="test-menu" > groupBy="label" > <result property="label" > javaType="java.lang.String" column="label" /> <result > property="value" javaType="java.lang.String" column="value" /> > <result property="subList" resultMap="Test.test-submenu" /> > </resultMap> <resultMap class="test" id="test-submenu" > groupBy="testId" > <result property="testId" > javaType="java.lang.Integer" column="test_id" /> <result > property="commonName" javaType="java.lang.String" > column="common_name" /> </resultMap> > <select id="getTestMenu" resultMap="test-menu" > cacheModel="test-cache"> SELECT T.test_id, T.common_name, > L.label, TC.category_id as value FROM tests as T > LEFT OUTER JOIN test_categories as TC on T.test_id = > TC.test_id INNER JOIN list as L ON L.value = > TC.category_id WHERE T.type = #value# ORDER BY > L.weight, L.label </select> > > Resultset from mysql > "test_id","common_name","label","value" > 17,"Paternity/Relationship","DNA Relationship Testing","DNA > Relationship Testing" > 6,"Haemochromatosis","Gastroenterology","Gastroenterology" > 48,"Pharmacogenetics","Gastroenterology","Gastroenterology" > 16,"Coeliac Disease","Gastroenterology","Gastroenterology" > 19,"Gilbert's syndrome","Gastroenterology","Gastroenterology" > 27,"Hepatitis C","Gastroenterology","Gastroenterology" > 28,"Hepatitis C","Gastroenterology","Gastroenterology" > 29,"Hepatitis C","Gastroenterology","Gastroenterology" > 31,"Hepatitis B","Gastroenterology","Gastroenterology" > 6,"Haemochromatosis","Genetics","Genetics" > 48,"Pharmacogenetics","Genetics","Genetics" > 16,"Coeliac Disease","Genetics","Genetics" > 18,"Thrombophilia","Genetics","Genetics" > 19,"Gilbert's syndrome","Genetics","Genetics" > 20,"MTHFR gene test","Genetics","Genetics" > 27,"Hepatitis C","Hepatitis","Hepatitis" > 28,"Hepatitis C","Hepatitis","Hepatitis" > 29,"Hepatitis C","Hepatitis","Hepatitis" > 31,"Hepatitis B","Hepatitis","Hepatitis" > 5,"Bordetella pertussis","Infectious Diseases","Infectious Diseases" > 47,"Human Papilloma Virus (HPV)","Infectious Diseases","Infectious > Diseases" > 24,"Tuberculosis","Infectious Diseases","Infectious Diseases" > 25,"Chlamydia and gonorrhoeae","Infectious Diseases","Infectious > Diseases" > 26,"HIV viral load","Infectious Diseases","Infectious Diseases" > 27,"Hepatitis C","Infectious Diseases","Infectious Diseases" > 28,"Hepatitis C","Infectious Diseases","Infectious Diseases" > 29,"Hepatitis C","Infectious Diseases","Infectious Diseases" > 31,"Hepatitis B","Infectious Diseases","Infectious Diseases" > 46,"Herpes Simplex","Infectious Diseases","Infectious Diseases" > 21,"(PGS) Screening for Aneuploidy","Infertility","Infertility" > 48,"Pharmacogenetics","Oncology","Oncology" > 48,"Pharmacogenetics","Oncology","Oncology" > 48,"Pharmacogenetics","Oncology","Oncology" > 48,"Pharmacogenetics","Pharmacogenomics","Pharmacogenomics" > 21,"(PGS) Screening for Aneuploidy","Prenatal Testing","Prenatal > Testing" > 48,"Pharmacogenetics","Psychiatry","Psychiatry" > 48,"Pharmacogenetics","Psychiatry","Psychiatry" > 47,"Human Papilloma Virus (HPV)","Sexual Health","Sexual Health" > 25,"Chlamydia and gonorrhoeae","Sexual Health","Sexual Health" > 26,"HIV viral load","Sexual Health","Sexual Health" > 6,"Haemochromatosis","Gastroenterology","Gastroenterology" > 48,"Pharmacogenetics","Gastroenterology","Gastroenterology" > 16,"Coeliac Disease","Gastroenterology","Gastroenterology" > 19,"Gilbert's syndrome","Gastroenterology","Gastroenterology" > 27,"Hepatitis C","Gastroenterology","Gastroenterology" > 28,"Hepatitis C","Gastroenterology","Gastroenterology" > 29,"Hepatitis C","Gastroenterology","Gastroenterology" > 31,"Hepatitis B","Gastroenterology","Gastroenterology" > 5,"Bordetella pertussis","Infectious Diseases","Infectious Diseases" > 47,"Human Papilloma Virus (HPV)","Infectious Diseases","Infectious > Diseases" > 24,"Tuberculosis","Infectious Diseases","Infectious Diseases" > 25,"Chlamydia and gonorrhoeae","Infectious Diseases","Infectious > Diseases" > 26,"HIV viral load","Infectious Diseases","Infectious Diseases" > 27,"Hepatitis C","Infectious Diseases","Infectious Diseases" > 28,"Hepatitis C","Infectious Diseases","Infectious Diseases" > 29,"Hepatitis C","Infectious Diseases","Infectious Diseases" > 31,"Hepatitis B","Infectious Diseases","Infectious Diseases" > 46,"Herpes Simplex","Infectious Diseases","Infectious Diseases" > 21,"(PGS) Screening for Aneuploidy","Infertility","Infertility" >