RE: One query populating multiple lists per object returned

Daniel Pitts Thu, 11 May 2006 19:01:06 -0700

Honestly, the solution I would choose is very different....

Make a "Pets" table that has both dog and cat :-)

Or, if you must use this schema... When you query for people, do one query for "People with Cats", and one for "People with Dogs" and then merge the results.

From: Jeff Butler [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 11, 2006 2:03 PM
To: user-java@ibatis.apache.org
Subject: Re: One query populating *multiple* lists per object returned

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:

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.

Jeff Butler

On 5/11/06, Rick Reumann <[EMAIL PROTECTED]> wrote:

I let my stupid question sit for a few days so now... Bueller,
Bueller... anyone, anyone ... :)

On 5/9/06, Rick Reumann <[EMAIL PROTECTED]> wrote:
> 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
>
> ===================================
>
> Person Object
> -------------
> List cats;
> List dogs;
> int personID;
> String personName;
>
> (Cat class and Dog class as well)
>
> ===================================
>
> 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="personMap" class="foo.bar.Person" groupBy="personID">
>    <result property="personID"                  column="personID"/>
>    <result property="personName"             column="personName" />
>    <result property="cats"       resultMap="Persons.catsMap"/>
>    
> </resultMap>
>
> <resultMap id="catsMap" class="foo.bar.Cat">
>     <result property="catID" column="catID"/>
>     <result property="catName" column="catName"/>
> </resultMap>
>
> <resultMap id="dogsMap" class="foo.bar.Dog">
>     <result property="dogID" column="dogID"/>
>     <result property="dogName" column="dogName"/>
> </resultMap>
>
> 
> <select id="getPersons" resultMap="personMap">
>     SELECT
>         p.personID,
>         p.pesonName,
>         c.catID,
>         c.catName
>      FROM Person p
>      LEFT JOIN Cat c ON p.personID = 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 = 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.
>

--
Rick

RE: One query populating *multiple* lists per object returned

Reply via email to

RE: One query populating multiple lists per object returned