ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chris Lamey <cla...@localmatters.com>
Subject Re: N+1 problem
Date Tue, 26 Sep 2006 02:37:10 GMT
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"
> 

Mime
View raw message