Some DBs have support for selecting trees, like Oracle and Postgres' CONNECT_BY functions. I don't have my O'Reilly SQL Cookbook handy, but it's got a chapter on the different DBs (although it completely skips over the Postgres CONNECT_BY function), if you want some pointers.
If your DB has that kind of thing, you could use it and a row handler to pull the results into what you need. -----Original Message----- From: Brad Handy [mailto:[EMAIL PROTECTED] Sent: Mon 2/19/2007 8:17 PM To: user-java@ibatis.apache.org Subject: Re: Avoiding N+1 Selects I guess I'll have to take a different approach. The depth isn't set to be a defined level; so if I go more than two levels below the grandparent, it won't work. On 2/19/07, Clinton Begin <[EMAIL PROTECTED]> wrote: > > You'll need one query with 3 result maps. The result maps will be chained > together with collection properties using the resultMap attribute. > > > <resultMap id="Child"> > ... > <resultMap id="Parent"> > <result ... resultMap="Child"/> > ... > <resultMap id="GrandParent"> > <result ... resultMap="Parent"/> > ... > <select ... resultMap="GrandParent"> > ... > > The select statement should join the tables together and you may need to > be very explicit with the column names. > > Clinton > > On 2/19/07, Brad Handy <[EMAIL PROTECTED]> wrote: > > > > I have a table which has all of the parent/child relationships in the > > same table. I would like to avoid the N+1 selects with this construct, but > > it's unclear from the documentation if this can be done. > > > > Let's say I have the following relationships defined in the table: > > > > Grand Parent > > Parent 1 > > Child 1 > > Child 2 > > Parent 2 > > Parent 3 > > Child 3 > > Child 4 > > > > > > When creating the child objects for "Grand Parent", will the same > > "Parent*" objects be used to add the children "Child*" objects to the > > appropriate parents? > > > > > > > > Brad > > > >
<<winmail.dat>>