Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user-java@ibatis.apache.org
Received-SPF: pass (asf.osuosl.org: domain of rickcr@gmail.com designates
 64.233.184.239 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
        b=hlX8c83ywU0GsNOI0sMFlL+1gv+MV7mQgxkeM54IRg5NEakTGWXSO0sg8zxngipcNzkCCbmSNRBWIopiy7YWRREqxA4eOIp0vwHJDhfBiChnSBAPrYM00rJNr6tIYovzofzjMy6GDH3OsZAMYh8mmxaYUs7oG7Bspa1J2pUgEPw=
Message-ID: <583d4dff0605090808p7ae8fe15p9daff670de1a4daa@mail.gmail.com>
Date: Tue, 9 May 2006 11:08:16 -0400
From: "Rick Reumann" <rickcr@gmail.com>
To: user-java@ibatis.apache.org
Subject: One query populating *multiple* lists per object returned
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

I can manage the n+1 stuff using groupBy when I'm populating one List
per object per level. Where I'm running into trouble is I want to have
multiple Lists populated per object. An example will hopefully help
clarify:

Table PersonDog
---------------
personID
dogID
dogName


Table PersonCat
---------------
personID
catID
catName


Table Person
------------
personID
personName

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Person Object
-------------
List cats;
List dogs;
int personID;
String personName;

(Cat class and Dog class as well)

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

Now what I want back in one iBATIS query is a way to build a
List of Person objects with the Lists of Cats and Dogs populated
per person (assuming they have cats or dogs since either can be
null).

I'm not sure if it's a certain orderby in the sql I need to do or
something I have to do with my iBATIS result maps to get
both lists populated correctly.
I CAN get this to work fine populating either Dogs or Cats (by
themself) but I can't seem to create the correct query to get
'both' populated per person.

For example for just Cats per peson, the below works (might be
a typo in the below since I changed what I'm working on to
Cats, Dogs, Person for sake of clarity):

<resultMap id=3D"personMap" class=3D"foo.bar.Person" groupBy=3D"personID">
   <result property=3D"personID"                  column=3D"personID"/>
   <result property=3D"personName"             column=3D"personName" />
   <result property=3D"cats"       resultMap=3D"Persons.catsMap"/>
   <!--<result property=3D"dogs"     resultMap=3D"Persons.dogsMap"/>-->
</resultMap>

<resultMap id=3D"catsMap" class=3D"foo.bar.Cat">
    <result property=3D"catID" column=3D"catID"/>
    <result property=3D"catName" column=3D"catName"/>
</resultMap>

<resultMap id=3D"dogsMap" class=3D"foo.bar.Dog">
    <result property=3D"dogID" column=3D"dogID"/>
    <result property=3D"dogName" column=3D"dogName"/>
</resultMap>

<!-- below query needs to also add dogs !!! -->
<select id=3D"getPersons" resultMap=3D"personMap">
    SELECT
        p.personID,
        p.pesonName,
        c.catID,
        c.catName
     FROM Person p
     LEFT JOIN Cat c ON p.personID =3D c.personID
     ORDER BY
        p.personID, c.catID
</select>


When I include the result property dogs and
try to join in DOGS - LEFT JOIN Dog d ON p.personID =3D d.personID -
I end up with too much duplicate data per Person when iBATIS
builds my objects.

I'm assuming I'm missing something simple and/or being a typical
idiot and doing soemthing stupid? Thanks for any help.