Wouldn't I have to give unique column names for those identifying the child
data?

For example

table parentChild
  some_foreign_key int
  id int
  description varchar(64)
  parent_id int


rows

 id          description                                        parent_id
 0           grandparent                                       null
 1           parent 1                                             0
 2           child 1                                               1
 3           child 2                                               1
 4           parent 2                                             0
 5           child 3                                               4
 6           parent 3                                             0
 7           child 4                                               6
 8           child 5                                               6


select
   p.id as parentId
   , p.description as parentDesc
   , p.parent_id as parentAncestorId
   , c.id as childId
   , c.description as childDesc
   , c.parent_id as childAncestorId
from
  parentChild p
   , parentChild c
where
  p.some_foreign_key = c.some_foreign_key
  and p.id = c.parent_id
  and p.id <> c.id
order by
  p.id
  , c.id


should return

parentId  parentDesc               parentAncestorId  childId
childDesc                childAncestorId
0           grandparent               null                      1
parent 1                    0
0           grandparent               null                      4
parent 2                    0
0           grandparent               null                      6
parent 3                    0
1           parent 1                    0                         2
child 1                       1
1           parent 1                    0                         3
child 2                       1
4           parent 2                    0                         5
child 3                       4
6           parent 3                    0                         7
child 4                       6
6           parent 3                    0                         8
child 5                       6





On 2/20/07, Clinton Begin <[EMAIL PROTECTED]> wrote:


i.e. is it a join with a bridge table or not (1:M or M:N)?

Regardless, I wonder if a recursive result map would work....It might.

<resultMap id="Node" ... >
  <result name="children" ... resultMap="Node"/>
...

I don't see any reason why that would cause any problems...perhaps try it
and let us know.

Cheers,
Clinton

On 2/19/07, Clinton Begin <[EMAIL PROTECTED]> wrote:
>
>
> Oh...is it a self join?
>
> Clinton
>
> On 2/19/07, Brad Handy < [EMAIL PROTECTED]> wrote:
> >
> > 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
> > > >
> > >
> > >
> >
>

Reply via email to