Return-Path: 
 <user-java-return-10284-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 21495 invoked from network); 14 Jan 2007 19:23:52 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 14 Jan 2007 19:23:52 -0000
Received: (qmail 77604 invoked by uid 500); 14 Jan 2007 19:23:57 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 77585 invoked by uid 500); 14 Jan 2007 19:23:57 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 77574 invoked by uid 99); 14 Jan 2007 19:23:57 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 14 Jan 2007 11:23:57 -0800
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: local policy)
Received: from [203.167.246.5] (HELO mx2.e2-media.co.nz) (203.167.246.5)
    by apache.org (qpsmtpd/0.29) with ESMTP; Sun, 14 Jan 2007 11:23:47 -0800
Received: from [203.167.246.200] (account ben HELO [192.168.234.64])
  by mx2.e2-media.co.nz (CommuniGate Pro SMTP 5.0)
  with ESMTPA id 30910656 for user-java@ibatis.apache.org;
 Mon, 15 Jan 2007 08:23:25 +1300
Message-ID: <45AA834E.1030500@e2-media.co.nz>
Date: Mon, 15 Jan 2007 08:23:58 +1300
From: Ben Schmidt <ben@e2-media.co.nz>
User-Agent: Thunderbird 1.5.0.9 (Macintosh/20061207)
MIME-Version: 1.0
To: user-java@ibatis.apache.org
Subject: N+1 select problem over three tables.
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Checked: Checked by ClamAV on apache.org

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.