ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: N+1 select problem over three tables.
Date Mon, 15 Jan 2007 19:52:02 GMT
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