You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Nathan Maves <Na...@Sun.COM> on 2005/02/09 18:21:54 UTC
N+1 with a twist?
I dont think that the current implementation will work for this but I
was looking for some best practices on how to do it.
Parent Class A has n number of List properties that all come for their
own tables.
A from table_a
List of B from table_b
List of C from table_c
.
.
.
Nathan
Re: N+1 with a twist?
Posted by Nathan Maves <Na...@Sun.COM>.
PERFECT!
On Feb 10, 2005, at 4:07 PM, Mike Fagan wrote:
> If you are using oracle the following is legal:
>
> select p.*, c.name child_name, c.description
> from parent p, child c
> WHERE p.id = c.parent_id;
>
>
> Nathan Maves wrote:
>
>> That is how I was going to handle them. I was just looking to see if
>> there was some tricky way to use "select *" from multiple tables. I
>> have one table that has 30 columns in it. It is very painful to list
>> out each column :)
>>
>> Nathan
>> On Feb 10, 2005, at 3:50 PM, Kris A. Jenkins wrote:
>>
>>> Nathan,
>>>
>>> Ambiguous column names are easily handled. Just
>>> differentiate the names in the query, then you can
>>> assign them explicitly in the resultMaps. Here's an
>>> example with an ambiguous 'name' column:
>>>
>>> <sqlMap namespace="Example">
>>> <resultMap id="parentResult" class="com.eg.Parent"
>>> groupBy="parentId">
>>> <result property="parentId" column="parent_id"/>
>>> <result property="name" column="parent_name"/>
>>> <result property="children"
>>> resultMap="Example.childResult"/>
>>> </resultMap>
>>>
>>> <resultMap id="childResult" class="com.eg.Child">
>>> <result property="name" column="child_name"/>
>>> <result property="description"
>>> column="description"/>
>>> column="broadcastEndDate"/>
>>> </resultMap>
>>>
>>> <select id="getFamilies" resultMap="parentResult">
>>> select
>>> p.id AS parent_id,
>>> p.name AS parent_name,
>>> c.name AS child_name,
>>> c.desc AS description
>>> FROM child c, parent p
>>> WHERE p.id = c.parent_id
>>> </select>
>>> </sqlMap>
>>>
>>> In this example both the column and the property names
>>> are ambiguous, but it's gracefully handled. :-)
>>>
>>> HTH,
>>> Kris
>>>
>>> --- Nathan Maves <Na...@Sun.COM> wrote:
>>>
>>>> Great! I will give it a try.
>>>>
>>>> What do you suggest when you have ambiguously
>>>> defined column names in a
>>>> n+1 query?
>>>>
>>>> nathan
>>>> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
>>>>
>>>>> If you can join the data into a single resultset
>>>>
>>>> with repeating
>>>>
>>>>> groups, the N+1 solution will work, no matter how
>>>>
>>>> the collections are
>>>>
>>>>> arranged.
>>>>>
>>>>> As long as you group by some column in table_a,
>>>>
>>>> you can separately map
>>>>
>>>>> 2 collections on the same class.
>>>>>
>>>>> This will work.
>>>>>
>>>>> Try it out!
>>>>>
>>>>> Clinton
>>>>>
>>>>> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
>>>>> <Na...@sun.com> wrote:
>>>>>
>>>>>> I dont think that the current implementation will
>>>>>
>>>> work for this but I
>>>>
>>>>>> was looking for some best practices on how to do
>>>>>
>>>> it.
>>>>
>>>>>>
>>>>>> Parent Class A has n number of List properties
>>>>>
>>>> that all come for their
>>>>
>>>>>> own tables.
>>>>>>
>>>>>> A from table_a
>>>>>> List of B from table_b
>>>>>> List of C from table_c
>>>>>> .
>>>>>> .
>>>>>> .
>>>>>>
>>>>>>
>>>>>> Nathan
>>>>>>
>>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> ___________________________________________________________
>>> ALL-NEW Yahoo! Messenger - all new features - even more fun!
>>> http://uk.messenger.yahoo.com
>>
>>
>>
>
>
Re: N+1 with a twist?
Posted by Mike Fagan <mf...@tde.com>.
If you are using oracle the following is legal:
select p.*, c.name child_name, c.description
from parent p, child c
WHERE p.id = c.parent_id;
Nathan Maves wrote:
> That is how I was going to handle them. I was just looking to see if
> there was some tricky way to use "select *" from multiple tables. I
> have one table that has 30 columns in it. It is very painful to list
> out each column :)
>
> Nathan
> On Feb 10, 2005, at 3:50 PM, Kris A. Jenkins wrote:
>
>> Nathan,
>>
>> Ambiguous column names are easily handled. Just
>> differentiate the names in the query, then you can
>> assign them explicitly in the resultMaps. Here's an
>> example with an ambiguous 'name' column:
>>
>> <sqlMap namespace="Example">
>> <resultMap id="parentResult" class="com.eg.Parent"
>> groupBy="parentId">
>> <result property="parentId" column="parent_id"/>
>> <result property="name" column="parent_name"/>
>> <result property="children"
>> resultMap="Example.childResult"/>
>> </resultMap>
>>
>> <resultMap id="childResult" class="com.eg.Child">
>> <result property="name" column="child_name"/>
>> <result property="description"
>> column="description"/>
>> column="broadcastEndDate"/>
>> </resultMap>
>>
>> <select id="getFamilies" resultMap="parentResult">
>> select
>> p.id AS parent_id,
>> p.name AS parent_name,
>> c.name AS child_name,
>> c.desc AS description
>> FROM child c, parent p
>> WHERE p.id = c.parent_id
>> </select>
>> </sqlMap>
>>
>> In this example both the column and the property names
>> are ambiguous, but it's gracefully handled. :-)
>>
>> HTH,
>> Kris
>>
>> --- Nathan Maves <Na...@Sun.COM> wrote:
>>
>>> Great! I will give it a try.
>>>
>>> What do you suggest when you have ambiguously
>>> defined column names in a
>>> n+1 query?
>>>
>>> nathan
>>> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
>>>
>>>> If you can join the data into a single resultset
>>>
>>> with repeating
>>>
>>>> groups, the N+1 solution will work, no matter how
>>>
>>> the collections are
>>>
>>>> arranged.
>>>>
>>>> As long as you group by some column in table_a,
>>>
>>> you can separately map
>>>
>>>> 2 collections on the same class.
>>>>
>>>> This will work.
>>>>
>>>> Try it out!
>>>>
>>>> Clinton
>>>>
>>>> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
>>>> <Na...@sun.com> wrote:
>>>>
>>>>> I dont think that the current implementation will
>>>>
>>> work for this but I
>>>
>>>>> was looking for some best practices on how to do
>>>>
>>> it.
>>>
>>>>>
>>>>> Parent Class A has n number of List properties
>>>>
>>> that all come for their
>>>
>>>>> own tables.
>>>>>
>>>>> A from table_a
>>>>> List of B from table_b
>>>>> List of C from table_c
>>>>> .
>>>>> .
>>>>> .
>>>>>
>>>>>
>>>>> Nathan
>>>>>
>>>>>
>>>
>>>
>>
>>
>>
>>
>>
>> ___________________________________________________________
>> ALL-NEW Yahoo! Messenger - all new features - even more fun!
>> http://uk.messenger.yahoo.com
>
>
>
Re: N+1 with a twist?
Posted by Nathan Maves <Na...@Sun.COM>.
That is how I was going to handle them. I was just looking to see if
there was some tricky way to use "select *" from multiple tables. I
have one table that has 30 columns in it. It is very painful to list
out each column :)
Nathan
On Feb 10, 2005, at 3:50 PM, Kris A. Jenkins wrote:
> Nathan,
>
> Ambiguous column names are easily handled. Just
> differentiate the names in the query, then you can
> assign them explicitly in the resultMaps. Here's an
> example with an ambiguous 'name' column:
>
> <sqlMap namespace="Example">
> <resultMap id="parentResult" class="com.eg.Parent"
> groupBy="parentId">
> <result property="parentId" column="parent_id"/>
> <result property="name" column="parent_name"/>
> <result property="children"
> resultMap="Example.childResult"/>
> </resultMap>
>
> <resultMap id="childResult" class="com.eg.Child">
> <result property="name" column="child_name"/>
> <result property="description"
> column="description"/>
> column="broadcastEndDate"/>
> </resultMap>
>
> <select id="getFamilies" resultMap="parentResult">
> select
> p.id AS parent_id,
> p.name AS parent_name,
> c.name AS child_name,
> c.desc AS description
> FROM child c, parent p
> WHERE p.id = c.parent_id
> </select>
> </sqlMap>
>
> In this example both the column and the property names
> are ambiguous, but it's gracefully handled. :-)
>
> HTH,
> Kris
>
> --- Nathan Maves <Na...@Sun.COM> wrote:
>> Great! I will give it a try.
>>
>> What do you suggest when you have ambiguously
>> defined column names in a
>> n+1 query?
>>
>> nathan
>> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
>>
>>> If you can join the data into a single resultset
>> with repeating
>>> groups, the N+1 solution will work, no matter how
>> the collections are
>>> arranged.
>>>
>>> As long as you group by some column in table_a,
>> you can separately map
>>> 2 collections on the same class.
>>>
>>> This will work.
>>>
>>> Try it out!
>>>
>>> Clinton
>>>
>>> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
>>> <Na...@sun.com> wrote:
>>>> I dont think that the current implementation will
>> work for this but I
>>>> was looking for some best practices on how to do
>> it.
>>>>
>>>> Parent Class A has n number of List properties
>> that all come for their
>>>> own tables.
>>>>
>>>> A from table_a
>>>> List of B from table_b
>>>> List of C from table_c
>>>> .
>>>> .
>>>> .
>>>>
>>>>
>>>> Nathan
>>>>
>>>>
>>
>>
>
>
>
>
>
> ___________________________________________________________
> ALL-NEW Yahoo! Messenger - all new features - even more fun!
> http://uk.messenger.yahoo.com
Re: N+1 with a twist?
Posted by "Kris A. Jenkins" <kr...@yahoo.co.uk>.
Nathan,
Ambiguous column names are easily handled. Just
differentiate the names in the query, then you can
assign them explicitly in the resultMaps. Here's an
example with an ambiguous 'name' column:
<sqlMap namespace="Example">
<resultMap id="parentResult" class="com.eg.Parent"
groupBy="parentId">
<result property="parentId" column="parent_id"/>
<result property="name" column="parent_name"/>
<result property="children"
resultMap="Example.childResult"/>
</resultMap>
<resultMap id="childResult" class="com.eg.Child">
<result property="name" column="child_name"/>
<result property="description"
column="description"/>
column="broadcastEndDate"/>
</resultMap>
<select id="getFamilies" resultMap="parentResult">
select
p.id AS parent_id,
p.name AS parent_name,
c.name AS child_name,
c.desc AS description
FROM child c, parent p
WHERE p.id = c.parent_id
</select>
</sqlMap>
In this example both the column and the property names
are ambiguous, but it's gracefully handled. :-)
HTH,
Kris
--- Nathan Maves <Na...@Sun.COM> wrote:
> Great! I will give it a try.
>
> What do you suggest when you have ambiguously
> defined column names in a
> n+1 query?
>
> nathan
> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
>
> > If you can join the data into a single resultset
> with repeating
> > groups, the N+1 solution will work, no matter how
> the collections are
> > arranged.
> >
> > As long as you group by some column in table_a,
> you can separately map
> > 2 collections on the same class.
> >
> > This will work.
> >
> > Try it out!
> >
> > Clinton
> >
> > On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
> > <Na...@sun.com> wrote:
> >> I dont think that the current implementation will
> work for this but I
> >> was looking for some best practices on how to do
> it.
> >>
> >> Parent Class A has n number of List properties
> that all come for their
> >> own tables.
> >>
> >> A from table_a
> >> List of B from table_b
> >> List of C from table_c
> >> .
> >> .
> >> .
> >>
> >>
> >> Nathan
> >>
> >>
>
>
___________________________________________________________
ALL-NEW Yahoo! Messenger - all new features - even more fun! http://uk.messenger.yahoo.com
Re: N+1 with a twist?
Posted by Nathan Maves <Na...@Sun.COM>.
Great! I will give it a try.
What do you suggest when you have ambiguously defined column names in a
n+1 query?
nathan
On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
> If you can join the data into a single resultset with repeating
> groups, the N+1 solution will work, no matter how the collections are
> arranged.
>
> As long as you group by some column in table_a, you can separately map
> 2 collections on the same class.
>
> This will work.
>
> Try it out!
>
> Clinton
>
> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
> <Na...@sun.com> wrote:
>> I dont think that the current implementation will work for this but I
>> was looking for some best practices on how to do it.
>>
>> Parent Class A has n number of List properties that all come for their
>> own tables.
>>
>> A from table_a
>> List of B from table_b
>> List of C from table_c
>> .
>> .
>> .
>>
>>
>> Nathan
>>
>>
Re: N+1 with a twist?
Posted by Mike Fagan <mf...@tde.com>.
Nathan, are you using oracle version 4?
If you are using a newer version then the limit on outer joins is about 256.
Nathan Maves wrote:
> This was all looking great but I hit a show stopper.
>
> I have one parent table with 3 child tables. The problem is that the
> child rows might or might not be there. This means that I have to use
> an outer join to these tables. Oracle only allows a table to have one
> outer join. Suck!
>
> Nathan
>
> On Feb 11, 2005, at 1:37 PM, Clinton Begin wrote:
>
>> Doesn't matter. In series, or in parallel, any number of levels, any
>> time any place. It will work.
>>
>> Each resultMap="" mapping is treated separately.
>>
>> The coolest thing is that to do all of this it still only iterates
>> over the result set a single time... ;-)
>>
>> Cheers,
>> Clinton
>>
>>
>> On Fri, 11 Feb 2005 12:50:05 -0700, Nathan Maves
>> <Na...@sun.com> wrote:
>>
>>> How about 3 levels deep?
>>>
>>> A from request
>>> List of B from products
>>> List of C from approvals
>>> User object from users (where user's id maps to a
>>> column in the
>>> approvals table)
>>>
>>>
>>> Nathan
>>>
>>> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
>>>
>>>> If you can join the data into a single resultset with repeating
>>>> groups, the N+1 solution will work, no matter how the collections are
>>>> arranged.
>>>>
>>>> As long as you group by some column in table_a, you can separately map
>>>> 2 collections on the same class.
>>>>
>>>> This will work.
>>>>
>>>> Try it out!
>>>>
>>>> Clinton
>>>>
>>>> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
>>>> <Na...@sun.com> wrote:
>>>>
>>>>> I dont think that the current implementation will work for this but I
>>>>> was looking for some best practices on how to do it.
>>>>>
>>>>> Parent Class A has n number of List properties that all come for
>>>>> their
>>>>> own tables.
>>>>>
>>>>> A from table_a
>>>>> List of B from table_b
>>>>> List of C from table_c
>>>>> .
>>>>> .
>>>>> .
>>>>>
>>>>>
>>>>> Nathan
>>>>>
>>>>>
>>>
>>>
>
>
RE: N+1 with a twist?
Posted by roberto <ro...@theaegis.org>.
If you can, post a sample of the query. If the query is based on that
example you showed with tables three levels deep it should work (the child
tables are the ones that can be null right?).
Roberto
> -----Original Message-----
> From: Nathan Maves [mailto:Nathan.Maves@Sun.COM]
> Sent: Friday, February 11, 2005 3:55 PM
> To: ibatis-user-java@incubator.apache.org
> Subject: Re: N+1 with a twist?
>
> This was all looking great but I hit a show stopper.
>
> I have one parent table with 3 child tables. The problem is that the
> child rows might or might not be there. This means that I have to use
> an outer join to these tables. Oracle only allows a table to have one
> outer join. Suck!
>
> Nathan
>
> On Feb 11, 2005, at 1:37 PM, Clinton Begin wrote:
>
> > Doesn't matter. In series, or in parallel, any number of levels, any
> > time any place. It will work.
> >
> > Each resultMap="" mapping is treated separately.
> >
> > The coolest thing is that to do all of this it still only iterates
> > over the result set a single time... ;-)
> >
> > Cheers,
> > Clinton
> >
> >
> > On Fri, 11 Feb 2005 12:50:05 -0700, Nathan Maves
> > <Na...@sun.com> wrote:
> >> How about 3 levels deep?
> >>
> >> A from request
> >> List of B from products
> >> List of C from approvals
> >> User object from users (where user's id maps to a
> >> column in the
> >> approvals table)
> >>
> >>
> >> Nathan
> >>
> >> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
> >>
> >>> If you can join the data into a single resultset with repeating
> >>> groups, the N+1 solution will work, no matter how the collections are
> >>> arranged.
> >>>
> >>> As long as you group by some column in table_a, you can separately
> >>> map
> >>> 2 collections on the same class.
> >>>
> >>> This will work.
> >>>
> >>> Try it out!
> >>>
> >>> Clinton
> >>>
> >>> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
> >>> <Na...@sun.com> wrote:
> >>>> I dont think that the current implementation will work for this but
> >>>> I
> >>>> was looking for some best practices on how to do it.
> >>>>
> >>>> Parent Class A has n number of List properties that all come for
> >>>> their
> >>>> own tables.
> >>>>
> >>>> A from table_a
> >>>> List of B from table_b
> >>>> List of C from table_c
> >>>> .
> >>>> .
> >>>> .
> >>>>
> >>>>
> >>>> Nathan
> >>>>
> >>>>
> >>
> >>
>
Re: N+1 with a twist?
Posted by Nathan Maves <Na...@Sun.COM>.
This was all looking great but I hit a show stopper.
I have one parent table with 3 child tables. The problem is that the
child rows might or might not be there. This means that I have to use
an outer join to these tables. Oracle only allows a table to have one
outer join. Suck!
Nathan
On Feb 11, 2005, at 1:37 PM, Clinton Begin wrote:
> Doesn't matter. In series, or in parallel, any number of levels, any
> time any place. It will work.
>
> Each resultMap="" mapping is treated separately.
>
> The coolest thing is that to do all of this it still only iterates
> over the result set a single time... ;-)
>
> Cheers,
> Clinton
>
>
> On Fri, 11 Feb 2005 12:50:05 -0700, Nathan Maves
> <Na...@sun.com> wrote:
>> How about 3 levels deep?
>>
>> A from request
>> List of B from products
>> List of C from approvals
>> User object from users (where user's id maps to a
>> column in the
>> approvals table)
>>
>>
>> Nathan
>>
>> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
>>
>>> If you can join the data into a single resultset with repeating
>>> groups, the N+1 solution will work, no matter how the collections are
>>> arranged.
>>>
>>> As long as you group by some column in table_a, you can separately
>>> map
>>> 2 collections on the same class.
>>>
>>> This will work.
>>>
>>> Try it out!
>>>
>>> Clinton
>>>
>>> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
>>> <Na...@sun.com> wrote:
>>>> I dont think that the current implementation will work for this but
>>>> I
>>>> was looking for some best practices on how to do it.
>>>>
>>>> Parent Class A has n number of List properties that all come for
>>>> their
>>>> own tables.
>>>>
>>>> A from table_a
>>>> List of B from table_b
>>>> List of C from table_c
>>>> .
>>>> .
>>>> .
>>>>
>>>>
>>>> Nathan
>>>>
>>>>
>>
>>
Re: N+1 with a twist?
Posted by Clinton Begin <cl...@gmail.com>.
Doesn't matter. In series, or in parallel, any number of levels, any
time any place. It will work.
Each resultMap="" mapping is treated separately.
The coolest thing is that to do all of this it still only iterates
over the result set a single time... ;-)
Cheers,
Clinton
On Fri, 11 Feb 2005 12:50:05 -0700, Nathan Maves <Na...@sun.com> wrote:
> How about 3 levels deep?
>
> A from request
> List of B from products
> List of C from approvals
> User object from users (where user's id maps to a column in the
> approvals table)
>
>
> Nathan
>
> On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
>
> > If you can join the data into a single resultset with repeating
> > groups, the N+1 solution will work, no matter how the collections are
> > arranged.
> >
> > As long as you group by some column in table_a, you can separately map
> > 2 collections on the same class.
> >
> > This will work.
> >
> > Try it out!
> >
> > Clinton
> >
> > On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
> > <Na...@sun.com> wrote:
> >> I dont think that the current implementation will work for this but I
> >> was looking for some best practices on how to do it.
> >>
> >> Parent Class A has n number of List properties that all come for their
> >> own tables.
> >>
> >> A from table_a
> >> List of B from table_b
> >> List of C from table_c
> >> .
> >> .
> >> .
> >>
> >>
> >> Nathan
> >>
> >>
>
>
Re: N+1 with a twist?
Posted by Nathan Maves <Na...@Sun.COM>.
How about 3 levels deep?
A from request
List of B from products
List of C from approvals
User object from users (where user's id maps to a column in the
approvals table)
Nathan
On Feb 9, 2005, at 10:30 AM, Clinton Begin wrote:
> If you can join the data into a single resultset with repeating
> groups, the N+1 solution will work, no matter how the collections are
> arranged.
>
> As long as you group by some column in table_a, you can separately map
> 2 collections on the same class.
>
> This will work.
>
> Try it out!
>
> Clinton
>
> On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves
> <Na...@sun.com> wrote:
>> I dont think that the current implementation will work for this but I
>> was looking for some best practices on how to do it.
>>
>> Parent Class A has n number of List properties that all come for their
>> own tables.
>>
>> A from table_a
>> List of B from table_b
>> List of C from table_c
>> .
>> .
>> .
>>
>>
>> Nathan
>>
>>
OT: Richard Grimes on .NET
Posted by Abdullah Kauchali <ab...@isanusi.com>.
Off-topic: But hellova interesting!
(I was reading a thread on TSS the other night about .NET and Java where
Clinton spoke of
being impartial to either frameworks - I quite agree. Here's an article
that depicts some of the
silent thoughts in all of us about .NET... If you enjoy Grimes' writing,
this one would be no
different.)
http://www.ddj.com/documents/s=9205/ddj050201dnn/
Kind regards
Abdullah
Re: N+1 with a twist?
Posted by Clinton Begin <cl...@gmail.com>.
If you can join the data into a single resultset with repeating
groups, the N+1 solution will work, no matter how the collections are
arranged.
As long as you group by some column in table_a, you can separately map
2 collections on the same class.
This will work.
Try it out!
Clinton
On Wed, 09 Feb 2005 10:21:54 -0700, Nathan Maves <Na...@sun.com> wrote:
> I dont think that the current implementation will work for this but I
> was looking for some best practices on how to do it.
>
> Parent Class A has n number of List properties that all come for their
> own tables.
>
> A from table_a
> List of B from table_b
> List of C from table_c
> .
> .
> .
>
>
> Nathan
>
>