ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ben Schmidt <...@e2-media.co.nz>
Subject Re: N+1 select problem over three tables.
Date Mon, 15 Jan 2007 20:01:49 GMT
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 <ben@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 <ben@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.
>> >>
>>
>>


Mime
View raw message