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 N+1 select problem over three tables.
Date Sun, 14 Jan 2007 19:23:58 GMT
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