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 Collin Peters <ca...@gmail.com> on 2007/07/05 03:01:26 UTC
1:N:M
Does iBatis have support to map out queries that are more than just
1:N? For example, imagine in the tutorial that a category not only
has products as children, but also has some tags as children.
So in the developer guide on page 37 the section called "1:N & M:N
Solution", if that SQL had another join against a tags table is it
still possible to use that syntax somehow to get the right data out
(which would be one category with an array of products and an array of
tags)?
Regards,
Collin Peters
Re: 1:N:M
Posted by Zoran Avtarovski <zo...@sparecreative.com>.
Hi Colin,
I know I've done a three level m2m relationship model using Ibatis, I just
can't find the code at the moment.
But simply we had a company domain object which contained multiple workplace
objects which contained multiple locations.
I don't remember there being any issues, but I'll keep looking.
Z.
Re: 1:N:M
Posted by Iwao AVE! <ha...@gmail.com>.
Hi Collin,
The depth would not to be your problem.
Because your Activity class has three lists in it, you will need to
write a RowHandler as Larry explained.
Here's the post I referenced when I did it (also posted by Larry).
http://www.mail-archive.com/user-java@ibatis.apache.org/msg04253.html
Note that you will need to use java.util.LinkedHashMap or something
for the resultClass of your <select/>.
Hope this helps.
--
To all:
Isn't it technically possible to use <resultMap/> defined in xml file
from inside the RowHandler#handleRow?
Mapping every field manually in the handleRow method is obviously not
an optimal way.
Regards,
Iwao
2007/7/6, Collin Peters <ca...@gmail.com>:
> one child list does work (but that is just regular N+1).
>
> This ticket also looks like it could be the culprit:
> https://issues.apache.org/jira/browse/IBATIS-406
>
> As a summary of what I am doing:
>
> The data structure is for a fitness program and looks like this. A
> Program object has multiple(List) ProgramActivities. Each
> ProgramActivity object has a reference to the actual Activity object
> which is the object that has 3 lists (one to specify the muscle
> groups, one for some tags, and one for the parameters).
>
> The sample query I am running returns 26 rows for a single program.
> That program has 4 activities in it. Activity 1 has 2 tags, 3
> parameters, 2 muscle groups (2x3x2=12rows). Activity 2 has 1 tag, 3
> parameters, 2 muscle groups (1x3x2=6rows). Activity 3 has 1 tag, 2
> parameters, 2 muscle groups (1x2x2=4rows). Activity 4 has 1 tag, 2
> parameters, 2 muscle groups(1x2x2=4rows) for a total of 12 + 6 + 4 + 4
> = 26 rows.
>
> The SQL config looks like:
> <resultMap id="userProgramsResult" class="UserProgramVO" groupBy="programID" >
> <result property="programID" column="program_id"/>
> snip...
> <result property="programActivities"
> resultMap="fitness.programActivityResult"/>
> </resultMap>
>
> <resultMap id="programActivityResult" class="ProgramActivityVO"
> groupBy="programActivityID" >
> <result property="programActivityID" column="program_activity_id" />
> snip...
> <result property="activity" resultMap="fitnessCommonData.activityResult" />
> </resultMap>
>
> <resultMap id="activityResult" class="ActivityVO" groupBy="activityID" >
> <result property="activityID" column="activity_id"/>
> <result property="name" column="name"/>
> snip...
> <result property="activityTags"
> resultMap="fitnessCommonData.activityTagResult"/>
> <result property="parameters"
> resultMap="fitnessCommonData.parametersResult"/>
> <result property="muscleGroups"
> resultMap="fitnessCommonData.muscleGroupResult" />
> </resultMap>
>
> <resultMap id="activityTagResult" class="ActivityTagVO"
> groupBy="activityTagID" >
> <result property="activityTagID" column="activity_tag_id" />
> <result property="name" column="activity_tag_name" />
> </resultMap>
>
> <resultMap id="muscleGroupResult" class="MuscleGroupVO"
> groupBy="muscleGroupID" >
> <result property="muscleGroupID" column="muscle_group_id" />
> <result property="name" column="muscle_group_name" />
> snip...
> </resultMap>
>
> <resultMap id="parametersResult" class="int" groupBy="parameterID" >
> <result property="parameterID" column="parameter_id" />
> snip...
> </resultMap>
>
> Please let me know if there is anything incorrect in there.
>
> Collin
>
> On 7/5/07, Larry Meadors <lm...@apache.org> wrote:
> > No, that is a different issue - like if a person has multiple cats and
> > dogs and you want person.dogList and person.catList populated.
> >
> > What you are doing should work. Have you tried simplifying it to just
> > one child list?
> >
> > Larry
> >
> >
> > On 7/5/07, Collin Peters <ca...@gmail.com> wrote:
> > > I think this is the same request:
> > > https://issues.apache.org/jira/browse/IBATIS-396
> > >
> > > On 7/5/07, Collin Peters <ca...@gmail.com> wrote:
> > > > I can't seem to get this to work. I will try to explain a full example here.
> > > >
> > > > Imagine the following data structure:
> > > > -ObjectA has a List of ObjectB's (1:M)
> > > > -ObjectB has a variable ObjectC (1:1)
> > > > -Object C has three children lists (Child1, Child2, Child3 - all 1:M)
> > > >
> > > > The SQL looks something like (postgresql syntax)
> > > > SELECT a.object_a_id, b.object_b_id, c.object_c_id
> > > > FROM object_a a
> > > > JOIN object_b b USING (object_a_id)
> > > > JOIN object_c c USING (object_b_id)
> > > > JOIN child_1 c1 USING (object_c_id)
> > > > JOIN child_2 c2 USING (object_c_id)
> > > > JOIN child_3 c3 USING (object_c_id)
> > > >
> > > > This SQL would obviously return multiple rows with duplicate data.
> > > > Assuming there was 2 rows for each of the three children you would get
> > > > 8 rows for each ObjectC
> > > >
> > > > child_1_id | child_2_id | child_3_id
> > > > 1 | 1 | 1
> > > > 2 | 1 | 1
> > > > 1 | 2 | 1
> > > > 2 | 2 | 1
> > > > 1 | 1 | 2
> > > > 2 | 1 | 2
> > > > 1 | 2 | 2
> > > > 2 | 2 | 2
> > > >
> > > > Now in iBatis I would like to get a result of one ObjectC that has a
> > > > List of 2 objects for each of the children. The result I am getting
> > > > is 2 objects for the Child1 List, 4 objects for the Child2 List, and 8
> > > > objects for the Child3 List.
> > > >
> > > > Is this example clear enough? I suppose I can do a code example if required.
> > > >
> > > > Another strange thing I have found is that iBatis does not complain if
> > > > the groupBy uses a meaningless string. Meaning I can have
> > > > groupBy="foo" and I do not get any errors ('foo' is not a column in
> > > > the table or POJO. Is this behavior to be expected?
> > > >
> > > > Regards,
> > > > Collin
> > > >
> > >
> >
>
Re: 1:N:M
Posted by Collin Peters <ca...@gmail.com>.
one child list does work (but that is just regular N+1).
This ticket also looks like it could be the culprit:
https://issues.apache.org/jira/browse/IBATIS-406
As a summary of what I am doing:
The data structure is for a fitness program and looks like this. A
Program object has multiple(List) ProgramActivities. Each
ProgramActivity object has a reference to the actual Activity object
which is the object that has 3 lists (one to specify the muscle
groups, one for some tags, and one for the parameters).
The sample query I am running returns 26 rows for a single program.
That program has 4 activities in it. Activity 1 has 2 tags, 3
parameters, 2 muscle groups (2x3x2=12rows). Activity 2 has 1 tag, 3
parameters, 2 muscle groups (1x3x2=6rows). Activity 3 has 1 tag, 2
parameters, 2 muscle groups (1x2x2=4rows). Activity 4 has 1 tag, 2
parameters, 2 muscle groups(1x2x2=4rows) for a total of 12 + 6 + 4 + 4
= 26 rows.
The SQL config looks like:
<resultMap id="userProgramsResult" class="UserProgramVO" groupBy="programID" >
<result property="programID" column="program_id"/>
snip...
<result property="programActivities"
resultMap="fitness.programActivityResult"/>
</resultMap>
<resultMap id="programActivityResult" class="ProgramActivityVO"
groupBy="programActivityID" >
<result property="programActivityID" column="program_activity_id" />
snip...
<result property="activity" resultMap="fitnessCommonData.activityResult" />
</resultMap>
<resultMap id="activityResult" class="ActivityVO" groupBy="activityID" >
<result property="activityID" column="activity_id"/>
<result property="name" column="name"/>
snip...
<result property="activityTags"
resultMap="fitnessCommonData.activityTagResult"/>
<result property="parameters"
resultMap="fitnessCommonData.parametersResult"/>
<result property="muscleGroups"
resultMap="fitnessCommonData.muscleGroupResult" />
</resultMap>
<resultMap id="activityTagResult" class="ActivityTagVO"
groupBy="activityTagID" >
<result property="activityTagID" column="activity_tag_id" />
<result property="name" column="activity_tag_name" />
</resultMap>
<resultMap id="muscleGroupResult" class="MuscleGroupVO"
groupBy="muscleGroupID" >
<result property="muscleGroupID" column="muscle_group_id" />
<result property="name" column="muscle_group_name" />
snip...
</resultMap>
<resultMap id="parametersResult" class="int" groupBy="parameterID" >
<result property="parameterID" column="parameter_id" />
snip...
</resultMap>
Please let me know if there is anything incorrect in there.
Collin
On 7/5/07, Larry Meadors <lm...@apache.org> wrote:
> No, that is a different issue - like if a person has multiple cats and
> dogs and you want person.dogList and person.catList populated.
>
> What you are doing should work. Have you tried simplifying it to just
> one child list?
>
> Larry
>
>
> On 7/5/07, Collin Peters <ca...@gmail.com> wrote:
> > I think this is the same request:
> > https://issues.apache.org/jira/browse/IBATIS-396
> >
> > On 7/5/07, Collin Peters <ca...@gmail.com> wrote:
> > > I can't seem to get this to work. I will try to explain a full example here.
> > >
> > > Imagine the following data structure:
> > > -ObjectA has a List of ObjectB's (1:M)
> > > -ObjectB has a variable ObjectC (1:1)
> > > -Object C has three children lists (Child1, Child2, Child3 - all 1:M)
> > >
> > > The SQL looks something like (postgresql syntax)
> > > SELECT a.object_a_id, b.object_b_id, c.object_c_id
> > > FROM object_a a
> > > JOIN object_b b USING (object_a_id)
> > > JOIN object_c c USING (object_b_id)
> > > JOIN child_1 c1 USING (object_c_id)
> > > JOIN child_2 c2 USING (object_c_id)
> > > JOIN child_3 c3 USING (object_c_id)
> > >
> > > This SQL would obviously return multiple rows with duplicate data.
> > > Assuming there was 2 rows for each of the three children you would get
> > > 8 rows for each ObjectC
> > >
> > > child_1_id | child_2_id | child_3_id
> > > 1 | 1 | 1
> > > 2 | 1 | 1
> > > 1 | 2 | 1
> > > 2 | 2 | 1
> > > 1 | 1 | 2
> > > 2 | 1 | 2
> > > 1 | 2 | 2
> > > 2 | 2 | 2
> > >
> > > Now in iBatis I would like to get a result of one ObjectC that has a
> > > List of 2 objects for each of the children. The result I am getting
> > > is 2 objects for the Child1 List, 4 objects for the Child2 List, and 8
> > > objects for the Child3 List.
> > >
> > > Is this example clear enough? I suppose I can do a code example if required.
> > >
> > > Another strange thing I have found is that iBatis does not complain if
> > > the groupBy uses a meaningless string. Meaning I can have
> > > groupBy="foo" and I do not get any errors ('foo' is not a column in
> > > the table or POJO. Is this behavior to be expected?
> > >
> > > Regards,
> > > Collin
> > >
> >
>
Re: 1:N:M
Posted by Larry Meadors <lm...@apache.org>.
No, that is a different issue - like if a person has multiple cats and
dogs and you want person.dogList and person.catList populated.
What you are doing should work. Have you tried simplifying it to just
one child list?
Larry
On 7/5/07, Collin Peters <ca...@gmail.com> wrote:
> I think this is the same request:
> https://issues.apache.org/jira/browse/IBATIS-396
>
> On 7/5/07, Collin Peters <ca...@gmail.com> wrote:
> > I can't seem to get this to work. I will try to explain a full example here.
> >
> > Imagine the following data structure:
> > -ObjectA has a List of ObjectB's (1:M)
> > -ObjectB has a variable ObjectC (1:1)
> > -Object C has three children lists (Child1, Child2, Child3 - all 1:M)
> >
> > The SQL looks something like (postgresql syntax)
> > SELECT a.object_a_id, b.object_b_id, c.object_c_id
> > FROM object_a a
> > JOIN object_b b USING (object_a_id)
> > JOIN object_c c USING (object_b_id)
> > JOIN child_1 c1 USING (object_c_id)
> > JOIN child_2 c2 USING (object_c_id)
> > JOIN child_3 c3 USING (object_c_id)
> >
> > This SQL would obviously return multiple rows with duplicate data.
> > Assuming there was 2 rows for each of the three children you would get
> > 8 rows for each ObjectC
> >
> > child_1_id | child_2_id | child_3_id
> > 1 | 1 | 1
> > 2 | 1 | 1
> > 1 | 2 | 1
> > 2 | 2 | 1
> > 1 | 1 | 2
> > 2 | 1 | 2
> > 1 | 2 | 2
> > 2 | 2 | 2
> >
> > Now in iBatis I would like to get a result of one ObjectC that has a
> > List of 2 objects for each of the children. The result I am getting
> > is 2 objects for the Child1 List, 4 objects for the Child2 List, and 8
> > objects for the Child3 List.
> >
> > Is this example clear enough? I suppose I can do a code example if required.
> >
> > Another strange thing I have found is that iBatis does not complain if
> > the groupBy uses a meaningless string. Meaning I can have
> > groupBy="foo" and I do not get any errors ('foo' is not a column in
> > the table or POJO. Is this behavior to be expected?
> >
> > Regards,
> > Collin
> >
>
Re: 1:N:M
Posted by Collin Peters <ca...@gmail.com>.
I think this is the same request:
https://issues.apache.org/jira/browse/IBATIS-396
On 7/5/07, Collin Peters <ca...@gmail.com> wrote:
> I can't seem to get this to work. I will try to explain a full example here.
>
> Imagine the following data structure:
> -ObjectA has a List of ObjectB's (1:M)
> -ObjectB has a variable ObjectC (1:1)
> -Object C has three children lists (Child1, Child2, Child3 - all 1:M)
>
> The SQL looks something like (postgresql syntax)
> SELECT a.object_a_id, b.object_b_id, c.object_c_id
> FROM object_a a
> JOIN object_b b USING (object_a_id)
> JOIN object_c c USING (object_b_id)
> JOIN child_1 c1 USING (object_c_id)
> JOIN child_2 c2 USING (object_c_id)
> JOIN child_3 c3 USING (object_c_id)
>
> This SQL would obviously return multiple rows with duplicate data.
> Assuming there was 2 rows for each of the three children you would get
> 8 rows for each ObjectC
>
> child_1_id | child_2_id | child_3_id
> 1 | 1 | 1
> 2 | 1 | 1
> 1 | 2 | 1
> 2 | 2 | 1
> 1 | 1 | 2
> 2 | 1 | 2
> 1 | 2 | 2
> 2 | 2 | 2
>
> Now in iBatis I would like to get a result of one ObjectC that has a
> List of 2 objects for each of the children. The result I am getting
> is 2 objects for the Child1 List, 4 objects for the Child2 List, and 8
> objects for the Child3 List.
>
> Is this example clear enough? I suppose I can do a code example if required.
>
> Another strange thing I have found is that iBatis does not complain if
> the groupBy uses a meaningless string. Meaning I can have
> groupBy="foo" and I do not get any errors ('foo' is not a column in
> the table or POJO. Is this behavior to be expected?
>
> Regards,
> Collin
>
Re: 1:N:M
Posted by Collin Peters <ca...@gmail.com>.
I can't seem to get this to work. I will try to explain a full example here.
Imagine the following data structure:
-ObjectA has a List of ObjectB's (1:M)
-ObjectB has a variable ObjectC (1:1)
-Object C has three children lists (Child1, Child2, Child3 - all 1:M)
The SQL looks something like (postgresql syntax)
SELECT a.object_a_id, b.object_b_id, c.object_c_id
FROM object_a a
JOIN object_b b USING (object_a_id)
JOIN object_c c USING (object_b_id)
JOIN child_1 c1 USING (object_c_id)
JOIN child_2 c2 USING (object_c_id)
JOIN child_3 c3 USING (object_c_id)
This SQL would obviously return multiple rows with duplicate data.
Assuming there was 2 rows for each of the three children you would get
8 rows for each ObjectC
child_1_id | child_2_id | child_3_id
1 | 1 | 1
2 | 1 | 1
1 | 2 | 1
2 | 2 | 1
1 | 1 | 2
2 | 1 | 2
1 | 2 | 2
2 | 2 | 2
Now in iBatis I would like to get a result of one ObjectC that has a
List of 2 objects for each of the children. The result I am getting
is 2 objects for the Child1 List, 4 objects for the Child2 List, and 8
objects for the Child3 List.
Is this example clear enough? I suppose I can do a code example if required.
Another strange thing I have found is that iBatis does not complain if
the groupBy uses a meaningless string. Meaning I can have
groupBy="foo" and I do not get any errors ('foo' is not a column in
the table or POJO. Is this behavior to be expected?
Regards,
Collin
Re: 1:N:M
Posted by Nils Winkler <ni...@nilswinkler.com>.
On Wed, 4 Jul 2007 19:03:21 -0600, "Clinton Begin"
<cl...@gmail.com> said:
> Yes, you should be able to map any depth or breadth.
>
> Simply use two joins, two groups and two result map chains.
>
> Cheers,
> Clinton
But that means that the size of the result set will increase with each
additional depth level, right? I'm sure that iBATIS can handle this, but
bandwidth and processing time might limit the scalability here, if I'm
not mistaken - depending on how rigid your WHERE clause is.
I have done this before, and it works, but I haven't gone deeper than
two levels.
Nils
>
>
> On 7/4/07, Collin Peters <ca...@gmail.com> wrote:
> >
> > Does iBatis have support to map out queries that are more than just
> > 1:N? For example, imagine in the tutorial that a category not only
> > has products as children, but also has some tags as children.
> >
> > So in the developer guide on page 37 the section called "1:N & M:N
> > Solution", if that SQL had another join against a tags table is it
> > still possible to use that syntax somehow to get the right data out
> > (which would be one category with an array of products and an array of
> > tags)?
> >
> > Regards,
> > Collin Peters
> >
--
==================================
nils@nilswinkler.com
Re: 1:N:M
Posted by Clinton Begin <cl...@gmail.com>.
Yes, you should be able to map any depth or breadth.
Simply use two joins, two groups and two result map chains.
Cheers,
Clinton
On 7/4/07, Collin Peters <ca...@gmail.com> wrote:
>
> Does iBatis have support to map out queries that are more than just
> 1:N? For example, imagine in the tutorial that a category not only
> has products as children, but also has some tags as children.
>
> So in the developer guide on page 37 the section called "1:N & M:N
> Solution", if that SQL had another join against a tags table is it
> still possible to use that syntax somehow to get the right data out
> (which would be one category with an array of products and an array of
> tags)?
>
> Regards,
> Collin Peters
>