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 Heinrich Götzger <go...@gmx.de> on 2008/07/09 15:33:13 UTC
Having two N plus 1 groups
Hello,
I'm wondering if iBATS help me for solving _two_ N plus 1 problems
within one select:
Let's assume a result as follows:
main group_A group_b
-------------------------------
parent1, child_a1, null
parent1, child_a2, null
parent1, null, child_b1
parent1, null, child_b2
parent2, child_a4, null
parent2, null, child_b3
parent3, child_a5, null
parent3, child_a6, null
parent3, child_a7, null
parent3, null, child_b4
How would I get a list of main-beans with:
- parent1: containing list child_a(a1, a2), list child_b(b1, b2)
- parent2: containing list child_a(a4), list child_b(b3)
- parent3: containing list child_a(a5, a6, a7), list child_b(b4)
Group a and group b are not related to each other. They are only related
to parent
A more convenient solution could be the usage of collection types in the
result set. This would lead to a collection of group a and one of group
b. The resulting main-beans would be the same.
Is there some support in iBATIS for collection types?
Thanks for some thoughts and some help.
Cheers
Heinrich
Re: Having two N plus 1 groups
Posted by Alexandre Ferreira <al...@gmail.com>.
user-java-*unsubscribe*@ibatis.apache.org
On 7/10/08, Larry Meadors <la...@gmail.com> wrote:
>
> I don't think you'll be able to accomplish that without using either 2
> queries or a rowhandler.
>
> RowHandlers are really simple to write (1 method in the interface),
> and then you get all the data in one query, and can do pretty much
> anything you want with it.
>
> Larry
>
>
> On Thu, Jul 10, 2008 at 5:36 AM, John Dahl <cl...@gmail.com>
> wrote:
> >
> > I am having similar problems but can't find any solution.
> > Here is my case.
> >
> > report_group can have many report_types and many reports. No relation
> exists
> > for report_type and reports. Here is the result of a query to get a
> > report_group.
> >
> > report_group_id report_id report_type_id
> > 100 35 1
> > 100 35 2
> > 100 36 1
> > 100 36 2
> > 100 37 1
> > 100 37 2
> >
> > Now, desired result is:
> > ReportGroup with
> > 3 Report {35, 36, 37} and 3 ReportType {1, 2}.
> >
> > I have put groupBy="id" in all of the 3 resultMap (report_group, reports,
> > report_types), but I am getting a ReportGroup with
> > 3 Report {35, 36, 37}
> > & 6 ReportType(1, 2, 1, 2, 1, 2) i.e. 6 ReportType object being repeated
> > with every Report.
> >
> > How can I solve the problem? Any help is greatly appreciated.
> >
> >
> >
> > Heinrich Götzger wrote:
> >>
> >> Hello,
> >>
> >> I'm wondering if iBATS help me for solving _two_ N plus 1 problems
> >> within one select:
> >>
> >> Let's assume a result as follows:
> >>
> >> main group_A group_b
> >> -------------------------------
> >> parent1, child_a1, null
> >> parent1, child_a2, null
> >> parent1, null, child_b1
> >> parent1, null, child_b2
> >> parent2, child_a4, null
> >> parent2, null, child_b3
> >> parent3, child_a5, null
> >> parent3, child_a6, null
> >> parent3, child_a7, null
> >> parent3, null, child_b4
> >>
> >> How would I get a list of main-beans with:
> >> - parent1: containing list child_a(a1, a2), list child_b(b1, b2)
> >> - parent2: containing list child_a(a4), list child_b(b3)
> >> - parent3: containing list child_a(a5, a6, a7), list child_b(b4)
> >>
> >> Group a and group b are not related to each other. They are only related
> >> to parent
> >>
> >> A more convenient solution could be the usage of collection types in the
> >> result set. This would lead to a collection of group a and one of group
> >> b. The resulting main-beans would be the same.
> >> Is there some support in iBATIS for collection types?
> >>
> >>
> >> Thanks for some thoughts and some help.
> >>
> >> Cheers
> >>
> >> Heinrich
> >>
> >>
> >
> > --
> > View this message in context:
> http://www.nabble.com/Having-two-N-plus-1-groups-tp18361780p18380979.html
> > Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >
> >
>
Re: Having two N plus 1 groups
Posted by Alexandre Ferreira <al...@gmail.com>.
*unsubscribe me, please!*
**
*Thx!*
On 7/10/08, Larry Meadors <la...@gmail.com> wrote:
>
> I don't think you'll be able to accomplish that without using either 2
> queries or a rowhandler.
>
> RowHandlers are really simple to write (1 method in the interface),
> and then you get all the data in one query, and can do pretty much
> anything you want with it.
>
> Larry
>
>
> On Thu, Jul 10, 2008 at 5:36 AM, John Dahl <cl...@gmail.com>
> wrote:
> >
> > I am having similar problems but can't find any solution.
> > Here is my case.
> >
> > report_group can have many report_types and many reports. No relation
> exists
> > for report_type and reports. Here is the result of a query to get a
> > report_group.
> >
> > report_group_id report_id report_type_id
> > 100 35 1
> > 100 35 2
> > 100 36 1
> > 100 36 2
> > 100 37 1
> > 100 37 2
> >
> > Now, desired result is:
> > ReportGroup with
> > 3 Report {35, 36, 37} and 3 ReportType {1, 2}.
> >
> > I have put groupBy="id" in all of the 3 resultMap (report_group, reports,
> > report_types), but I am getting a ReportGroup with
> > 3 Report {35, 36, 37}
> > & 6 ReportType(1, 2, 1, 2, 1, 2) i.e. 6 ReportType object being repeated
> > with every Report.
> >
> > How can I solve the problem? Any help is greatly appreciated.
> >
> >
> >
> > Heinrich Götzger wrote:
> >>
> >> Hello,
> >>
> >> I'm wondering if iBATS help me for solving _two_ N plus 1 problems
> >> within one select:
> >>
> >> Let's assume a result as follows:
> >>
> >> main group_A group_b
> >> -------------------------------
> >> parent1, child_a1, null
> >> parent1, child_a2, null
> >> parent1, null, child_b1
> >> parent1, null, child_b2
> >> parent2, child_a4, null
> >> parent2, null, child_b3
> >> parent3, child_a5, null
> >> parent3, child_a6, null
> >> parent3, child_a7, null
> >> parent3, null, child_b4
> >>
> >> How would I get a list of main-beans with:
> >> - parent1: containing list child_a(a1, a2), list child_b(b1, b2)
> >> - parent2: containing list child_a(a4), list child_b(b3)
> >> - parent3: containing list child_a(a5, a6, a7), list child_b(b4)
> >>
> >> Group a and group b are not related to each other. They are only related
> >> to parent
> >>
> >> A more convenient solution could be the usage of collection types in the
> >> result set. This would lead to a collection of group a and one of group
> >> b. The resulting main-beans would be the same.
> >> Is there some support in iBATIS for collection types?
> >>
> >>
> >> Thanks for some thoughts and some help.
> >>
> >> Cheers
> >>
> >> Heinrich
> >>
> >>
> >
> > --
> > View this message in context:
> http://www.nabble.com/Having-two-N-plus-1-groups-tp18361780p18380979.html
> > Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
> >
> >
>
Re: Having two N plus 1 groups
Posted by John Dahl <cl...@gmail.com>.
Thanks for the solution. Using two query did it and I don't have to worry
about N+1 problem here as we need to load only one reportGroup
(getReportGroupById) but not their collection. Will try rowhandler later.
Thanks once again for quick response.
Larry Meadors wrote:
>
> I don't think you'll be able to accomplish that without using either 2
> queries or a rowhandler.
>
> RowHandlers are really simple to write (1 method in the interface),
> and then you get all the data in one query, and can do pretty much
> anything you want with it.
>
> Larry
>
>
> On Thu, Jul 10, 2008 at 5:36 AM, John Dahl <cl...@gmail.com>
> wrote:
>>
>> I am having similar problems but can't find any solution.
>> Here is my case.
>>
>> report_group can have many report_types and many reports. No relation
>> exists
>> for report_type and reports. Here is the result of a query to get a
>> report_group.
>>
>> report_group_id report_id report_type_id
>> 100 35 1
>> 100 35 2
>> 100 36 1
>> 100 36 2
>> 100 37 1
>> 100 37 2
>>
>> Now, desired result is:
>> ReportGroup with
>> 3 Report {35, 36, 37} and 3 ReportType {1, 2}.
>>
>> I have put groupBy="id" in all of the 3 resultMap (report_group, reports,
>> report_types), but I am getting a ReportGroup with
>> 3 Report {35, 36, 37}
>> & 6 ReportType(1, 2, 1, 2, 1, 2) i.e. 6 ReportType object being repeated
>> with every Report.
>>
>> How can I solve the problem? Any help is greatly appreciated.
>>
>>
>>
>> Heinrich Götzger wrote:
>>>
>>> Hello,
>>>
>>> I'm wondering if iBATS help me for solving _two_ N plus 1 problems
>>> within one select:
>>>
>>> Let's assume a result as follows:
>>>
>>> main group_A group_b
>>> -------------------------------
>>> parent1, child_a1, null
>>> parent1, child_a2, null
>>> parent1, null, child_b1
>>> parent1, null, child_b2
>>> parent2, child_a4, null
>>> parent2, null, child_b3
>>> parent3, child_a5, null
>>> parent3, child_a6, null
>>> parent3, child_a7, null
>>> parent3, null, child_b4
>>>
>>> How would I get a list of main-beans with:
>>> - parent1: containing list child_a(a1, a2), list child_b(b1, b2)
>>> - parent2: containing list child_a(a4), list child_b(b3)
>>> - parent3: containing list child_a(a5, a6, a7), list child_b(b4)
>>>
>>> Group a and group b are not related to each other. They are only related
>>> to parent
>>>
>>> A more convenient solution could be the usage of collection types in the
>>> result set. This would lead to a collection of group a and one of group
>>> b. The resulting main-beans would be the same.
>>> Is there some support in iBATIS for collection types?
>>>
>>>
>>> Thanks for some thoughts and some help.
>>>
>>> Cheers
>>>
>>> Heinrich
>>>
>>>
>>
>> --
>> View this message in context:
>> http://www.nabble.com/Having-two-N-plus-1-groups-tp18361780p18380979.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
>
--
View this message in context: http://www.nabble.com/Having-two-N-plus-1-groups-tp18361780p18396957.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Having two N plus 1 groups
Posted by Heinrich Götzger <go...@gmx.de>.
Larry Meadors wrote:
> I don't think you'll be able to accomplish that without using either 2
> queries or a rowhandler.
>
> RowHandlers are really simple to write (1 method in the interface),
> and then you get all the data in one query, and can do pretty much
> anything you want with it.
>
> Larry
Ok, that's the way to go for me. I'll check it out these days, looks
much simpler as it sounds at the beginning.
But one question till remains:
Using collection-types would help making the code in the RowHanlder much
simpler.
Are there any experiences around using collection types, or is it not
supported or not working?
Or did I miss the example somewhere on the web?
Thanks for answering.
Cheers
Heinrich
>
>
> On Thu, Jul 10, 2008 at 5:36 AM, John Dahl <cl...@gmail.com> wrote:
>> I am having similar problems but can't find any solution.
>> Here is my case.
>>
>> report_group can have many report_types and many reports. No relation exists
>> for report_type and reports. Here is the result of a query to get a
>> report_group.
>>
>> report_group_id report_id report_type_id
>> 100 35 1
>> 100 35 2
>> 100 36 1
>> 100 36 2
>> 100 37 1
>> 100 37 2
>>
>> Now, desired result is:
>> ReportGroup with
>> 3 Report {35, 36, 37} and 3 ReportType {1, 2}.
>>
>> I have put groupBy="id" in all of the 3 resultMap (report_group, reports,
>> report_types), but I am getting a ReportGroup with
>> 3 Report {35, 36, 37}
>> & 6 ReportType(1, 2, 1, 2, 1, 2) i.e. 6 ReportType object being repeated
>> with every Report.
>>
>> How can I solve the problem? Any help is greatly appreciated.
>>
>>
>>
>> Heinrich Götzger wrote:
>>> Hello,
>>>
>>> I'm wondering if iBATS help me for solving _two_ N plus 1 problems
>>> within one select:
>>>
>>> Let's assume a result as follows:
>>>
>>> main group_A group_b
>>> -------------------------------
>>> parent1, child_a1, null
>>> parent1, child_a2, null
>>> parent1, null, child_b1
>>> parent1, null, child_b2
>>> parent2, child_a4, null
>>> parent2, null, child_b3
>>> parent3, child_a5, null
>>> parent3, child_a6, null
>>> parent3, child_a7, null
>>> parent3, null, child_b4
>>>
>>> How would I get a list of main-beans with:
>>> - parent1: containing list child_a(a1, a2), list child_b(b1, b2)
>>> - parent2: containing list child_a(a4), list child_b(b3)
>>> - parent3: containing list child_a(a5, a6, a7), list child_b(b4)
>>>
>>> Group a and group b are not related to each other. They are only related
>>> to parent
>>>
>>> A more convenient solution could be the usage of collection types in the
>>> result set. This would lead to a collection of group a and one of group
>>> b. The resulting main-beans would be the same.
>>> Is there some support in iBATIS for collection types?
>>>
>>>
>>> Thanks for some thoughts and some help.
>>>
>>> Cheers
>>>
>>> Heinrich
>>>
>>>
>> --
>> View this message in context: http://www.nabble.com/Having-two-N-plus-1-groups-tp18361780p18380979.html
>> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
Re: Having two N plus 1 groups
Posted by Larry Meadors <la...@gmail.com>.
I don't think you'll be able to accomplish that without using either 2
queries or a rowhandler.
RowHandlers are really simple to write (1 method in the interface),
and then you get all the data in one query, and can do pretty much
anything you want with it.
Larry
On Thu, Jul 10, 2008 at 5:36 AM, John Dahl <cl...@gmail.com> wrote:
>
> I am having similar problems but can't find any solution.
> Here is my case.
>
> report_group can have many report_types and many reports. No relation exists
> for report_type and reports. Here is the result of a query to get a
> report_group.
>
> report_group_id report_id report_type_id
> 100 35 1
> 100 35 2
> 100 36 1
> 100 36 2
> 100 37 1
> 100 37 2
>
> Now, desired result is:
> ReportGroup with
> 3 Report {35, 36, 37} and 3 ReportType {1, 2}.
>
> I have put groupBy="id" in all of the 3 resultMap (report_group, reports,
> report_types), but I am getting a ReportGroup with
> 3 Report {35, 36, 37}
> & 6 ReportType(1, 2, 1, 2, 1, 2) i.e. 6 ReportType object being repeated
> with every Report.
>
> How can I solve the problem? Any help is greatly appreciated.
>
>
>
> Heinrich Götzger wrote:
>>
>> Hello,
>>
>> I'm wondering if iBATS help me for solving _two_ N plus 1 problems
>> within one select:
>>
>> Let's assume a result as follows:
>>
>> main group_A group_b
>> -------------------------------
>> parent1, child_a1, null
>> parent1, child_a2, null
>> parent1, null, child_b1
>> parent1, null, child_b2
>> parent2, child_a4, null
>> parent2, null, child_b3
>> parent3, child_a5, null
>> parent3, child_a6, null
>> parent3, child_a7, null
>> parent3, null, child_b4
>>
>> How would I get a list of main-beans with:
>> - parent1: containing list child_a(a1, a2), list child_b(b1, b2)
>> - parent2: containing list child_a(a4), list child_b(b3)
>> - parent3: containing list child_a(a5, a6, a7), list child_b(b4)
>>
>> Group a and group b are not related to each other. They are only related
>> to parent
>>
>> A more convenient solution could be the usage of collection types in the
>> result set. This would lead to a collection of group a and one of group
>> b. The resulting main-beans would be the same.
>> Is there some support in iBATIS for collection types?
>>
>>
>> Thanks for some thoughts and some help.
>>
>> Cheers
>>
>> Heinrich
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Having-two-N-plus-1-groups-tp18361780p18380979.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: Having two N plus 1 groups
Posted by John Dahl <cl...@gmail.com>.
I am having similar problems but can't find any solution.
Here is my case.
report_group can have many report_types and many reports. No relation exists
for report_type and reports. Here is the result of a query to get a
report_group.
report_group_id report_id report_type_id
100 35 1
100 35 2
100 36 1
100 36 2
100 37 1
100 37 2
Now, desired result is:
ReportGroup with
3 Report {35, 36, 37} and 3 ReportType {1, 2}.
I have put groupBy="id" in all of the 3 resultMap (report_group, reports,
report_types), but I am getting a ReportGroup with
3 Report {35, 36, 37}
& 6 ReportType(1, 2, 1, 2, 1, 2) i.e. 6 ReportType object being repeated
with every Report.
How can I solve the problem? Any help is greatly appreciated.
Heinrich Götzger wrote:
>
> Hello,
>
> I'm wondering if iBATS help me for solving _two_ N plus 1 problems
> within one select:
>
> Let's assume a result as follows:
>
> main group_A group_b
> -------------------------------
> parent1, child_a1, null
> parent1, child_a2, null
> parent1, null, child_b1
> parent1, null, child_b2
> parent2, child_a4, null
> parent2, null, child_b3
> parent3, child_a5, null
> parent3, child_a6, null
> parent3, child_a7, null
> parent3, null, child_b4
>
> How would I get a list of main-beans with:
> - parent1: containing list child_a(a1, a2), list child_b(b1, b2)
> - parent2: containing list child_a(a4), list child_b(b3)
> - parent3: containing list child_a(a5, a6, a7), list child_b(b4)
>
> Group a and group b are not related to each other. They are only related
> to parent
>
> A more convenient solution could be the usage of collection types in the
> result set. This would lead to a collection of group a and one of group
> b. The resulting main-beans would be the same.
> Is there some support in iBATIS for collection types?
>
>
> Thanks for some thoughts and some help.
>
> Cheers
>
> Heinrich
>
>
--
View this message in context: http://www.nabble.com/Having-two-N-plus-1-groups-tp18361780p18380979.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.