ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Brad Handy" <brad.ha...@gmail.com>
Subject Re: Avoiding N+1 Selects
Date Thu, 22 Feb 2007 15:48:00 GMT
iBatis came back with an error message indicating too many objects were
being returned for executeQueryForObject.


Brad

On 2/20/07, Brad Handy <brad.handy@gmail.com> wrote:
>
> 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 <clinton.begin@gmail.com > 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 <clinton.begin@gmail.com> wrote:
> > >
> > >
> > > Oh...is it a self join?
> > >
> > > Clinton
> > >
> > > On 2/19/07, Brad Handy < brad.handy@gmail.com> 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 <clinton.begin@gmail.com> 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 <brad.handy@gmail.com> 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
> > > > > >
> > > > >
> > > > >
> > > >
> > >
> >
>

Mime
View raw message