You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Ben Schmidt <be...@e2-media.co.nz> on 2007/01/14 20:23:58 UTC
N+1 select problem over three tables.
Hi there,
I have three tables: contacts, addresses and attributes. Each contact
has a number of addresses and a number of attributes. If I just want to
get the only the addresses or only the attributes for a contact I have
no problem, but if I try to get all the addresses and all the attributes
for a contact, I end up getting either the attributes or the addresses
(whichever one is defined last in the resultmap) doubled - i.e. I will
get four address entries when there is only two. Also note that this
does not happen if there is only one matching entry in one of the
tables. Here are my sqlmaps:
<sqlMap namespace="Contact">
<resultMap id="contactSimple" class="java.util.HashMap" groupBy="id">
<result property="id" column="contact_id" />
<result property="uuid" column="contact_uuid" />
<result property="deleted" column="contact_deleted" />
<result property="login" column="contact_login" />
<result property="passwordHash" column="contact_password" />
<result property="attributes"
javaType="java.util.ArrayList" resultMap="Contact.owner_attribute" />
<result property="addresses" javaType="java.util.ArrayList"
resultMap="Address.address" />
</resultMap>
<resultMap id="owner_attribute" class="java.util.HashMap" groupBy="id">
<result property="id" column="owner_attribute_id" />
<result property="uuid" column="owner_attribute_uuid" />
<result property="value" column="owner_attribute_value" />
</resultMap>
<resultMap id="address" class="java.util.HashMap" groupBy="id">
<result property="id" column="address_id" />
<result property="uuid" column="address_uuid" />
<result property="deleted" column="address_deleted" />
<result property="name" column="address_name" />
<result property="number" column="address_number" />
<result property="street" column="address_street" />
<result property="suburb" column="address_suburb" />
<result property="city" column="address_city" />
<result property="country" column="address_country" />
<result property="postCode" column="address_post_code" />
<result property="contactUuid" column="address_contact_uuid" />
</resultMap>
<select id="listContactSimple" resultMap="contactSimple">
SELECT
*
FROM contact
LEFT OUTER JOIN address on address_contact_uuid =
contact.contact_uuid
LEFT OUTER JOIN owner_attribute ON contact.contact_uuid =
owner_attribute_owner_uuid
WHERE
(owner_attribute_deleted ISNULL OR owner_attribute_deleted =
false )
AND (address_deleted ISNULL OR address_deleted = false )
<dynamic>
<isNotNull prepend="AND " property="deleted">
contact.contact_deleted = '$deleted$'
</isNotNull> <isNotNull
prepend="AND " property="passwordHash">
contact.contact_password = #passwordHash#
</isNotNull> <isNotNull prepend="AND "
property="login">
contact.contact_login ILIKE '%$login$%'
</isNotNull>
<isNotNull prepend="AND " property="uuid">
contact.contact_uuid = #uuid#
</isNotNull>
</dynamic>
ORDER BY contact.contact_login ASC
</select>
</sqlMap>
Any help on where I'm going wrong would be greatly appreciated.
Thanks.
Ben.
Re: N+1 select problem over three tables.
Posted by Ben Schmidt <be...@e2-media.co.nz>.
OK, Thanks for the help.
Ben.
Larry Meadors wrote:
> Yes, that could be clearer - the difference here is that the examples
> are all 1 -> M -> N, but what you are trying to do is 1 -> M*N.
>
> It's not going to work, you'll need to write a rowhandler, or find
> some other solution.
>
> Larry
>
>
> On 1/15/07, Ben Schmidt <be...@e2-media.co.nz> wrote:
>> I agree that the SQL returns multiple rows for each instance of a
>> contact, but isn't that what the groupby property is supposed to handle?
>>
>> From the iBatis SQLMaps documentation:
>>
>> The resultMap element also supports the attribute groupBy. The groupBy
>> attribute is used to specify a list
>> of properties in this resultMap that are used to identify unique rows in
>> the returned result set. Rows with
>> equal values for the specified properties will only generate one result
>> object. Use groupBy in combination with nested resultMaps to solve the
>> N+1 query problem (see following discussion for examples).
>>
>> and:
>>
>> 1:N & M:N Solution
>> iBATIS fully solves the N+1 selects solution. Here is an example:
>> <sqlMap namespace="ProductCategory">
>> <resultMap id="categoryResult" class="com.ibatis.example.Category"
>> groupBy="id">
>> <result property="id" column="CAT_ID"/>
>> <result property="description" column="CAT_DESCRIPTION"/>
>> <result property="productList"
>> resultMap="ProductCategory.productResult"/>
>> </resultMap>
>>
>> <resultMap id="productResult" class="com.ibatis.example.Product">
>> <result property="id" column="PRD_ID"/>
>> <result property="description" column="PRD_DESCRIPTION"/>
>> </resultMap>
>> <select id="getCategory" parameterClass="int"
>> resultMap="categoryResult">
>> select C.CAT_ID, C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
>> from CATEGORY C
>> left outer join PRODUCT P
>> on C.CAT_ID = P.PRD_CAT_ID
>> where CAT_ID = #value#
>> </select>
>> </sqlMap>
>> When you call...
>>
>> List myList = queryForList("ProductCategory.getCategory", new
>> Integer(1002));
>>
>> ...the main query is executed, and the results are stored in the myList
>> variable as beans of type
>> "com.ibatis.example.Category". Each object in that List will have a
>> "productList" property that is also a List
>> populated from the same query, but using the "productResult" result map
>> to populate the beans in the child
>> list. So, you end up with a list containing sub-lists, and only one
>> database query is executed.
>>
>> The important items here are the...
>>
>> groupBy="id"
>>
>> ...attribute and the...
>>
>> <result property="productList"
>> resultMap="ProductCategory.productResult"/>
>>
>> ...property mapping in the "categoryResult" result map. One other
>> important detail is that the result mapping
>> for the productList property is namespace aware - had it been simply
>> "productResult" it would not work.
>> Using this approach, you can solve any N+1 problem of any depth or
>> breadth.
>>
>> Which is basically the situation I have, except that my top level result
>> map refers to two other result maps, the second of which does not seem
>> to obey the groupBy property. Am I reading the documentation wrong?
>>
>> Thanks.
>>
>> Ben.
>>
>> Larry Meadors wrote:
>> > That is what I'd expect. If you look at the results of the SQL, it
>> > becomes more apparent.
>> >
>> > Lets say you have 3 contacts, that have 2,3, and 5 addresses. When you
>> > do that join, you get 10 rows back - the first one 2 times, the second
>> > one 3 times, and the third one 5 times.
>> >
>> > Now, if those contacts have 5, 8, and 10 attributes, when you join the
>> > previous results (2+3+5=10 rows) with the attributes, you get 84 rows
>> > (2*5 + 3*8 + 5*10).
>> >
>> > What you are trying to do cannot be done in a single SQL statement,
>> > so..it can't be done in iBATIS with a single SQL statement..unless you
>> > use a row handler. I think you could make a row handler do that.
>> >
>> > Larry
>> >
>> >
>> > On 1/14/07, Ben Schmidt <be...@e2-media.co.nz> wrote:
>> >> Hi there,
>> >>
>> >> I have three tables: contacts, addresses and attributes. Each contact
>> >> has a number of addresses and a number of attributes. If I just
>> want to
>> >> get the only the addresses or only the attributes for a contact I
>> have
>> >> no problem, but if I try to get all the addresses and all the
>> attributes
>> >> for a contact, I end up getting either the attributes or the
>> addresses
>> >> (whichever one is defined last in the resultmap) doubled - i.e. I
>> will
>> >> get four address entries when there is only two. Also note that this
>> >> does not happen if there is only one matching entry in one of the
>> >> tables. Here are my sqlmaps:
>> >>
>> >> <sqlMap namespace="Contact">
>> >> <resultMap id="contactSimple" class="java.util.HashMap" groupBy="id">
>> >> <result property="id" column="contact_id" />
>> >> <result property="uuid" column="contact_uuid" />
>> >> <result property="deleted" column="contact_deleted" />
>> >> <result property="login" column="contact_login" />
>> >> <result property="passwordHash" column="contact_password" />
>> >> <result property="attributes"
>> >> javaType="java.util.ArrayList" resultMap="Contact.owner_attribute" />
>> >> <result property="addresses" javaType="java.util.ArrayList"
>> >> resultMap="Address.address" />
>> >> </resultMap>
>> >>
>> >> <resultMap id="owner_attribute" class="java.util.HashMap"
>> groupBy="id">
>> >> <result property="id" column="owner_attribute_id" />
>> >> <result property="uuid" column="owner_attribute_uuid" />
>> >> <result property="value" column="owner_attribute_value" />
>> >> </resultMap>
>> >>
>> >> <resultMap id="address" class="java.util.HashMap" groupBy="id">
>> >> <result property="id" column="address_id" />
>> >> <result property="uuid" column="address_uuid" />
>> >> <result property="deleted" column="address_deleted" />
>> >> <result property="name" column="address_name" />
>> >> <result property="number" column="address_number" />
>> >> <result property="street" column="address_street" />
>> >> <result property="suburb" column="address_suburb" />
>> >> <result property="city" column="address_city" />
>> >> <result property="country" column="address_country" />
>> >> <result property="postCode" column="address_post_code" />
>> >> <result property="contactUuid" column="address_contact_uuid" />
>> >> </resultMap>
>> >>
>> >> <select id="listContactSimple" resultMap="contactSimple">
>> >> SELECT
>> >> *
>> >> FROM contact
>> >> LEFT OUTER JOIN address on address_contact_uuid =
>> >> contact.contact_uuid
>> >> LEFT OUTER JOIN owner_attribute ON contact.contact_uuid =
>> >> owner_attribute_owner_uuid
>> >> WHERE
>> >> (owner_attribute_deleted ISNULL OR owner_attribute_deleted =
>> >> false )
>> >> AND (address_deleted ISNULL OR address_deleted = false )
>> >> <dynamic>
>> >> <isNotNull prepend="AND " property="deleted">
>> >> contact.contact_deleted = '$deleted$'
>> >> </isNotNull> <isNotNull
>> >> prepend="AND " property="passwordHash">
>> >> contact.contact_password = #passwordHash#
>> >> </isNotNull> <isNotNull prepend="AND "
>> >> property="login">
>> >> contact.contact_login ILIKE '%$login$%'
>> >> </isNotNull>
>> >> <isNotNull prepend="AND " property="uuid">
>> >> contact.contact_uuid = #uuid#
>> >> </isNotNull>
>> >> </dynamic>
>> >> ORDER BY contact.contact_login ASC
>> >> </select>
>> >> </sqlMap>
>> >>
>> >> Any help on where I'm going wrong would be greatly appreciated.
>> >>
>> >> Thanks.
>> >>
>> >> Ben.
>> >>
>>
>>
Re: N+1 select problem over three tables.
Posted by Larry Meadors <lm...@apache.org>.
Yes, that could be clearer - the difference here is that the examples
are all 1 -> M -> N, but what you are trying to do is 1 -> M*N.
It's not going to work, you'll need to write a rowhandler, or find
some other solution.
Larry
On 1/15/07, Ben Schmidt <be...@e2-media.co.nz> wrote:
> I agree that the SQL returns multiple rows for each instance of a
> contact, but isn't that what the groupby property is supposed to handle?
>
> From the iBatis SQLMaps documentation:
>
> The resultMap element also supports the attribute groupBy. The groupBy
> attribute is used to specify a list
> of properties in this resultMap that are used to identify unique rows in
> the returned result set. Rows with
> equal values for the specified properties will only generate one result
> object. Use groupBy in combination with nested resultMaps to solve the
> N+1 query problem (see following discussion for examples).
>
> and:
>
> 1:N & M:N Solution
> iBATIS fully solves the N+1 selects solution. Here is an example:
> <sqlMap namespace="ProductCategory">
> <resultMap id="categoryResult" class="com.ibatis.example.Category"
> groupBy="id">
> <result property="id" column="CAT_ID"/>
> <result property="description" column="CAT_DESCRIPTION"/>
> <result property="productList" resultMap="ProductCategory.productResult"/>
> </resultMap>
>
> <resultMap id="productResult" class="com.ibatis.example.Product">
> <result property="id" column="PRD_ID"/>
> <result property="description" column="PRD_DESCRIPTION"/>
> </resultMap>
> <select id="getCategory" parameterClass="int" resultMap="categoryResult">
> select C.CAT_ID, C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
> from CATEGORY C
> left outer join PRODUCT P
> on C.CAT_ID = P.PRD_CAT_ID
> where CAT_ID = #value#
> </select>
> </sqlMap>
> When you call...
>
> List myList = queryForList("ProductCategory.getCategory", new
> Integer(1002));
>
> ...the main query is executed, and the results are stored in the myList
> variable as beans of type
> "com.ibatis.example.Category". Each object in that List will have a
> "productList" property that is also a List
> populated from the same query, but using the "productResult" result map
> to populate the beans in the child
> list. So, you end up with a list containing sub-lists, and only one
> database query is executed.
>
> The important items here are the...
>
> groupBy="id"
>
> ...attribute and the...
>
> <result property="productList" resultMap="ProductCategory.productResult"/>
>
> ...property mapping in the "categoryResult" result map. One other
> important detail is that the result mapping
> for the productList property is namespace aware - had it been simply
> "productResult" it would not work.
> Using this approach, you can solve any N+1 problem of any depth or breadth.
>
> Which is basically the situation I have, except that my top level result
> map refers to two other result maps, the second of which does not seem
> to obey the groupBy property. Am I reading the documentation wrong?
>
> Thanks.
>
> Ben.
>
> Larry Meadors wrote:
> > That is what I'd expect. If you look at the results of the SQL, it
> > becomes more apparent.
> >
> > Lets say you have 3 contacts, that have 2,3, and 5 addresses. When you
> > do that join, you get 10 rows back - the first one 2 times, the second
> > one 3 times, and the third one 5 times.
> >
> > Now, if those contacts have 5, 8, and 10 attributes, when you join the
> > previous results (2+3+5=10 rows) with the attributes, you get 84 rows
> > (2*5 + 3*8 + 5*10).
> >
> > What you are trying to do cannot be done in a single SQL statement,
> > so..it can't be done in iBATIS with a single SQL statement..unless you
> > use a row handler. I think you could make a row handler do that.
> >
> > Larry
> >
> >
> > On 1/14/07, Ben Schmidt <be...@e2-media.co.nz> wrote:
> >> Hi there,
> >>
> >> I have three tables: contacts, addresses and attributes. Each contact
> >> has a number of addresses and a number of attributes. If I just want to
> >> get the only the addresses or only the attributes for a contact I have
> >> no problem, but if I try to get all the addresses and all the attributes
> >> for a contact, I end up getting either the attributes or the addresses
> >> (whichever one is defined last in the resultmap) doubled - i.e. I will
> >> get four address entries when there is only two. Also note that this
> >> does not happen if there is only one matching entry in one of the
> >> tables. Here are my sqlmaps:
> >>
> >> <sqlMap namespace="Contact">
> >> <resultMap id="contactSimple" class="java.util.HashMap" groupBy="id">
> >> <result property="id" column="contact_id" />
> >> <result property="uuid" column="contact_uuid" />
> >> <result property="deleted" column="contact_deleted" />
> >> <result property="login" column="contact_login" />
> >> <result property="passwordHash" column="contact_password" />
> >> <result property="attributes"
> >> javaType="java.util.ArrayList" resultMap="Contact.owner_attribute" />
> >> <result property="addresses" javaType="java.util.ArrayList"
> >> resultMap="Address.address" />
> >> </resultMap>
> >>
> >> <resultMap id="owner_attribute" class="java.util.HashMap" groupBy="id">
> >> <result property="id" column="owner_attribute_id" />
> >> <result property="uuid" column="owner_attribute_uuid" />
> >> <result property="value" column="owner_attribute_value" />
> >> </resultMap>
> >>
> >> <resultMap id="address" class="java.util.HashMap" groupBy="id">
> >> <result property="id" column="address_id" />
> >> <result property="uuid" column="address_uuid" />
> >> <result property="deleted" column="address_deleted" />
> >> <result property="name" column="address_name" />
> >> <result property="number" column="address_number" />
> >> <result property="street" column="address_street" />
> >> <result property="suburb" column="address_suburb" />
> >> <result property="city" column="address_city" />
> >> <result property="country" column="address_country" />
> >> <result property="postCode" column="address_post_code" />
> >> <result property="contactUuid" column="address_contact_uuid" />
> >> </resultMap>
> >>
> >> <select id="listContactSimple" resultMap="contactSimple">
> >> SELECT
> >> *
> >> FROM contact
> >> LEFT OUTER JOIN address on address_contact_uuid =
> >> contact.contact_uuid
> >> LEFT OUTER JOIN owner_attribute ON contact.contact_uuid =
> >> owner_attribute_owner_uuid
> >> WHERE
> >> (owner_attribute_deleted ISNULL OR owner_attribute_deleted =
> >> false )
> >> AND (address_deleted ISNULL OR address_deleted = false )
> >> <dynamic>
> >> <isNotNull prepend="AND " property="deleted">
> >> contact.contact_deleted = '$deleted$'
> >> </isNotNull> <isNotNull
> >> prepend="AND " property="passwordHash">
> >> contact.contact_password = #passwordHash#
> >> </isNotNull> <isNotNull prepend="AND "
> >> property="login">
> >> contact.contact_login ILIKE '%$login$%'
> >> </isNotNull>
> >> <isNotNull prepend="AND " property="uuid">
> >> contact.contact_uuid = #uuid#
> >> </isNotNull>
> >> </dynamic>
> >> ORDER BY contact.contact_login ASC
> >> </select>
> >> </sqlMap>
> >>
> >> Any help on where I'm going wrong would be greatly appreciated.
> >>
> >> Thanks.
> >>
> >> Ben.
> >>
>
>
Re: N+1 select problem over three tables.
Posted by Ben Schmidt <be...@e2-media.co.nz>.
I agree that the SQL returns multiple rows for each instance of a
contact, but isn't that what the groupby property is supposed to handle?
From the iBatis SQLMaps documentation:
The resultMap element also supports the attribute groupBy. The groupBy
attribute is used to specify a list
of properties in this resultMap that are used to identify unique rows in
the returned result set. Rows with
equal values for the specified properties will only generate one result
object. Use groupBy in combination with nested resultMaps to solve the
N+1 query problem (see following discussion for examples).
and:
1:N & M:N Solution
iBATIS fully solves the N+1 selects solution. Here is an example:
<sqlMap namespace="ProductCategory">
<resultMap id=”categoryResult” class=”com.ibatis.example.Category”
groupBy=”id”>
<result property=”id” column=”CAT_ID”/>
<result property=”description” column=”CAT_DESCRIPTION”/>
<result property=”productList” resultMap=”ProductCategory.productResult”/>
</resultMap>
<resultMap id=”productResult” class=”com.ibatis.example.Product”>
<result property=”id” column=”PRD_ID”/>
<result property=”description” column=”PRD_DESCRIPTION”/>
</resultMap>
<select id=”getCategory” parameterClass=”int” resultMap=”categoryResult”>
select C.CAT_ID, C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
from CATEGORY C
left outer join PRODUCT P
on C.CAT_ID = P.PRD_CAT_ID
where CAT_ID = #value#
</select>
</sqlMap>
When you call...
List myList = queryForList("ProductCategory.getCategory", new
Integer(1002));
...the main query is executed, and the results are stored in the myList
variable as beans of type
"com.ibatis.example.Category". Each object in that List will have a
"productList" property that is also a List
populated from the same query, but using the "productResult" result map
to populate the beans in the child
list. So, you end up with a list containing sub-lists, and only one
database query is executed.
The important items here are the...
groupBy="id"
...attribute and the...
<result property="productList" resultMap="ProductCategory.productResult"/>
...property mapping in the "categoryResult" result map. One other
important detail is that the result mapping
for the productList property is namespace aware - had it been simply
"productResult" it would not work.
Using this approach, you can solve any N+1 problem of any depth or breadth.
Which is basically the situation I have, except that my top level result
map refers to two other result maps, the second of which does not seem
to obey the groupBy property. Am I reading the documentation wrong?
Thanks.
Ben.
Larry Meadors wrote:
> That is what I'd expect. If you look at the results of the SQL, it
> becomes more apparent.
>
> Lets say you have 3 contacts, that have 2,3, and 5 addresses. When you
> do that join, you get 10 rows back - the first one 2 times, the second
> one 3 times, and the third one 5 times.
>
> Now, if those contacts have 5, 8, and 10 attributes, when you join the
> previous results (2+3+5=10 rows) with the attributes, you get 84 rows
> (2*5 + 3*8 + 5*10).
>
> What you are trying to do cannot be done in a single SQL statement,
> so..it can't be done in iBATIS with a single SQL statement..unless you
> use a row handler. I think you could make a row handler do that.
>
> Larry
>
>
> On 1/14/07, Ben Schmidt <be...@e2-media.co.nz> wrote:
>> Hi there,
>>
>> I have three tables: contacts, addresses and attributes. Each contact
>> has a number of addresses and a number of attributes. If I just want to
>> get the only the addresses or only the attributes for a contact I have
>> no problem, but if I try to get all the addresses and all the attributes
>> for a contact, I end up getting either the attributes or the addresses
>> (whichever one is defined last in the resultmap) doubled - i.e. I will
>> get four address entries when there is only two. Also note that this
>> does not happen if there is only one matching entry in one of the
>> tables. Here are my sqlmaps:
>>
>> <sqlMap namespace="Contact">
>> <resultMap id="contactSimple" class="java.util.HashMap" groupBy="id">
>> <result property="id" column="contact_id" />
>> <result property="uuid" column="contact_uuid" />
>> <result property="deleted" column="contact_deleted" />
>> <result property="login" column="contact_login" />
>> <result property="passwordHash" column="contact_password" />
>> <result property="attributes"
>> javaType="java.util.ArrayList" resultMap="Contact.owner_attribute" />
>> <result property="addresses" javaType="java.util.ArrayList"
>> resultMap="Address.address" />
>> </resultMap>
>>
>> <resultMap id="owner_attribute" class="java.util.HashMap" groupBy="id">
>> <result property="id" column="owner_attribute_id" />
>> <result property="uuid" column="owner_attribute_uuid" />
>> <result property="value" column="owner_attribute_value" />
>> </resultMap>
>>
>> <resultMap id="address" class="java.util.HashMap" groupBy="id">
>> <result property="id" column="address_id" />
>> <result property="uuid" column="address_uuid" />
>> <result property="deleted" column="address_deleted" />
>> <result property="name" column="address_name" />
>> <result property="number" column="address_number" />
>> <result property="street" column="address_street" />
>> <result property="suburb" column="address_suburb" />
>> <result property="city" column="address_city" />
>> <result property="country" column="address_country" />
>> <result property="postCode" column="address_post_code" />
>> <result property="contactUuid" column="address_contact_uuid" />
>> </resultMap>
>>
>> <select id="listContactSimple" resultMap="contactSimple">
>> SELECT
>> *
>> FROM contact
>> LEFT OUTER JOIN address on address_contact_uuid =
>> contact.contact_uuid
>> LEFT OUTER JOIN owner_attribute ON contact.contact_uuid =
>> owner_attribute_owner_uuid
>> WHERE
>> (owner_attribute_deleted ISNULL OR owner_attribute_deleted =
>> false )
>> AND (address_deleted ISNULL OR address_deleted = false )
>> <dynamic>
>> <isNotNull prepend="AND " property="deleted">
>> contact.contact_deleted = '$deleted$'
>> </isNotNull> <isNotNull
>> prepend="AND " property="passwordHash">
>> contact.contact_password = #passwordHash#
>> </isNotNull> <isNotNull prepend="AND "
>> property="login">
>> contact.contact_login ILIKE '%$login$%'
>> </isNotNull>
>> <isNotNull prepend="AND " property="uuid">
>> contact.contact_uuid = #uuid#
>> </isNotNull>
>> </dynamic>
>> ORDER BY contact.contact_login ASC
>> </select>
>> </sqlMap>
>>
>> Any help on where I'm going wrong would be greatly appreciated.
>>
>> Thanks.
>>
>> Ben.
>>
Re: N+1 select problem over three tables.
Posted by Larry Meadors <lm...@apache.org>.
That is what I'd expect. If you look at the results of the SQL, it
becomes more apparent.
Lets say you have 3 contacts, that have 2,3, and 5 addresses. When you
do that join, you get 10 rows back - the first one 2 times, the second
one 3 times, and the third one 5 times.
Now, if those contacts have 5, 8, and 10 attributes, when you join the
previous results (2+3+5=10 rows) with the attributes, you get 84 rows
(2*5 + 3*8 + 5*10).
What you are trying to do cannot be done in a single SQL statement,
so..it can't be done in iBATIS with a single SQL statement..unless you
use a row handler. I think you could make a row handler do that.
Larry
On 1/14/07, Ben Schmidt <be...@e2-media.co.nz> wrote:
> Hi there,
>
> I have three tables: contacts, addresses and attributes. Each contact
> has a number of addresses and a number of attributes. If I just want to
> get the only the addresses or only the attributes for a contact I have
> no problem, but if I try to get all the addresses and all the attributes
> for a contact, I end up getting either the attributes or the addresses
> (whichever one is defined last in the resultmap) doubled - i.e. I will
> get four address entries when there is only two. Also note that this
> does not happen if there is only one matching entry in one of the
> tables. Here are my sqlmaps:
>
> <sqlMap namespace="Contact">
> <resultMap id="contactSimple" class="java.util.HashMap" groupBy="id">
> <result property="id" column="contact_id" />
> <result property="uuid" column="contact_uuid" />
> <result property="deleted" column="contact_deleted" />
> <result property="login" column="contact_login" />
> <result property="passwordHash" column="contact_password" />
> <result property="attributes"
> javaType="java.util.ArrayList" resultMap="Contact.owner_attribute" />
> <result property="addresses" javaType="java.util.ArrayList"
> resultMap="Address.address" />
> </resultMap>
>
> <resultMap id="owner_attribute" class="java.util.HashMap" groupBy="id">
> <result property="id" column="owner_attribute_id" />
> <result property="uuid" column="owner_attribute_uuid" />
> <result property="value" column="owner_attribute_value" />
> </resultMap>
>
> <resultMap id="address" class="java.util.HashMap" groupBy="id">
> <result property="id" column="address_id" />
> <result property="uuid" column="address_uuid" />
> <result property="deleted" column="address_deleted" />
> <result property="name" column="address_name" />
> <result property="number" column="address_number" />
> <result property="street" column="address_street" />
> <result property="suburb" column="address_suburb" />
> <result property="city" column="address_city" />
> <result property="country" column="address_country" />
> <result property="postCode" column="address_post_code" />
> <result property="contactUuid" column="address_contact_uuid" />
> </resultMap>
>
> <select id="listContactSimple" resultMap="contactSimple">
> SELECT
> *
> FROM contact
> LEFT OUTER JOIN address on address_contact_uuid =
> contact.contact_uuid
> LEFT OUTER JOIN owner_attribute ON contact.contact_uuid =
> owner_attribute_owner_uuid
> WHERE
> (owner_attribute_deleted ISNULL OR owner_attribute_deleted =
> false )
> AND (address_deleted ISNULL OR address_deleted = false )
> <dynamic>
> <isNotNull prepend="AND " property="deleted">
> contact.contact_deleted = '$deleted$'
> </isNotNull> <isNotNull
> prepend="AND " property="passwordHash">
> contact.contact_password = #passwordHash#
> </isNotNull> <isNotNull prepend="AND "
> property="login">
> contact.contact_login ILIKE '%$login$%'
> </isNotNull>
> <isNotNull prepend="AND " property="uuid">
> contact.contact_uuid = #uuid#
> </isNotNull>
> </dynamic>
> ORDER BY contact.contact_login ASC
> </select>
> </sqlMap>
>
> Any help on where I'm going wrong would be greatly appreciated.
>
> Thanks.
>
> Ben.
>