Return-Path: 
 <user-java-return-7050-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 79918 invoked from network); 12 May 2006 01:14:36 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199)
  by minotaur.apache.org with SMTP; 12 May 2006 01:14:36 -0000
Received: (qmail 16629 invoked by uid 500); 12 May 2006 01:11:44 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 15947 invoked by uid 500); 12 May 2006 01:11:36 -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 13922 invoked by uid 99); 12 May 2006 01:11:11 -0000
Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 11 May 2006 18:11:11 -0700
X-ASF-Spam-Status: No, hits=1.4 required=10.0
	tests=SPF_NEUTRAL
X-Spam-Check-By: apache.org
Received-SPF: neutral (asf.osuosl.org: 65.32.5.134 is neither permitted nor
 denied by domain of rickcr@gmail.com)
Received: from [65.32.5.134] (HELO ms-smtp-04.tampabay.rr.com) (65.32.5.134)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 11 May 2006 17:17:04 -0700
Received: from [192.168.0.174] (189-32.73-24.tampabay.res.rr.com
 [24.73.32.189])
	by ms-smtp-04.tampabay.rr.com (8.13.6/8.13.6) with ESMTP id k4C0Gc6N021427
	for <user-java@ibatis.apache.org>; Thu, 11 May 2006 20:16:41 -0400 (EDT)
Message-ID: <4463D3EC.9050001@gmail.com>
Date: Thu, 11 May 2006 20:16:44 -0400
From: Rick Reumann <rickcr@gmail.com>
User-Agent: Thunderbird 1.4 (Windows/20050908)
MIME-Version: 1.0
To: user-java@ibatis.apache.org
Subject: Re: One query populating *multiple* lists per object returned
References: <583d4dff0605090808p7ae8fe15p9daff670de1a4daa@mail.gmail.com>
	 <583d4dff0605110802m54505157v34f104262d079ae@mail.gmail.com>
 <c593b8750605111403l47c19f99x1ff8f4949e457aaa@mail.gmail.com>
In-Reply-To: <c593b8750605111403l47c19f99x1ff8f4949e457aaa@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Symantec AntiVirus Scan Engine
X-Virus-Checked: Checked by ClamAV on apache.org
X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N


Jeff Butler wrote:
> It's not really a stupid question.  The problem is that adding the Dog 
> table to the join list will, in effect, create a cross join between dog 
> and cat - causing lots of data to be repeated as you've seen.
>  
> There's not a great solution that I can think of.  One solution would be 
> to use the iBATIS group by solution and a join for one of the lists 
> (cats) - as you've already accomplished.  For the other list (dogs), you 
> can populate it with a second query sort of like this:
>  
> <resultMap id="personMap" class="foo.bar.Person" groupBy="personID">
>   <result property="personID"                  column="personID"/>
>   <result property="personName"             column="personName" />
>   <result property="cats"       resultMap="Persons.catsMap"/>
>   <result property="dogs" column="personId"   select="getDogsByPersonId"/>
> </resultMap>
>  
> I haven't tried this for real, but I think it will work.  This is still 
> an N+1 query, but at least it's not 2N+1!
>  
> Another thought is that you could write your own List implementation 
> that would not allow duplicates.  Then it could all be done in one query 
> because you would catch and throw out the duplicates in Java code.  As I 
> think about it, I might like this solution better.  There's still a 
> bunch of duplicate data coming back from the DB, but there's only on DB 
> call.

Thanks Jeff for your comments. Makes perfect sense. I forgot about using 
a custom List Implementation approach. That would work out nicely.

-- 
Rick