ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zoran Avtarovski <zo...@sparecreative.com>
Subject N+1 problem
Date Tue, 26 Sep 2006 00:19:35 GMT
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