ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zoran Avtarovski <zo...@sparecreative.com>
Subject Re: Multiple n+1
Date Fri, 03 Feb 2006 22:40:53 GMT
Thanks Nils,

I apreciate the pointer.

I've changed my SQL statement to read:

SELECT * FROM Users
left outer join UserGroup  on Users.user_id = UserGroup.user_id
left outer join UserCat on Users.user_id = UserCat.user_id
left outer join nGroup on nGroup.group_id = UserGroup.group_id
left outer join Category on Category.cat_id = UserCat.cat_id
WHERE Users.user_id = #user_id#

Which gives a result set with all the data I need,

I didn't realise the groupBy attribute was available in the resultMap tag
for the child properties (eg Group, Category). That makes it so much easier.
Do you know if it works? I'll implement it in the morning (too tired now),
but I'm interested to know if anybody else has had any success with this
method. There whole section on n+1 is a little sparse.

Zoran



> I have something like this working. Here's roughly what I'm doing:
> 
> - Create a single select using joins across the tables.
> - Make sure that each of the selected columns has a unique alias, e.g.
> "category_name"
> - Test the query in a SQL tool to make sure you get what you want. You
> should have multiple rows for each user.
> - Create result maps for User, Group and Category.
> - Link the Group and Category result maps into the User result map:
> <result  property="groups" column="user_id" resultMap="userGroups" />
> - Each of the result maps should have the groupBy attribute set to its
> own primary key attribute:
> 
> class User { private int userId;}
> class Group { private int groupId;}
> class Category {private int catId;}
> 
> <resultMap id="user" class="User" groupBy="userId">...</resultMap>
> <resultMap id="group" class="Group" groupBy="groupId">...</resultMap>
> <resultMap id="category" class="Category"
> groupBy="catId">...</resultMap>
> 
> - For testing, start with only one relationship, e.g. Group, leave out
> Category. Once this works, add Category again.
> 
> The example in the Wiki is too simple IMHO, we should try to add a more
> complex one...
> 
> Nils
> 
> On Thu, 02 Feb 2006 21:10:50 +1100, "Zoran Avtarovski"
> <zoran@sparecreative.com> said:
>> I've been trying to get a n+1 example to work without much luck and I
>> think
>> that I may have misunderstood the wiki article. I have intentionally kept
>> the example simple so I can fully understand the concepts and then apply
>> it
>> to the real world app which uses the same principles.
>> 
>> I have three object tables
>> User, which has {user_id, name, type} columns
>> Group, which has {group_id, name, description} columns
>> Category, which has {cat_id, name, description} columns
>> 
>> I also have two relationship tables
>> UserGroup, which has {user_id, group_id} columns
>> UserCat, which has {user_id, group_id} columns
>> 
>> Each User can have multiple Groups and Categories. This is represented
>> through a User POJO which includes two Collection properties {groups,
>> categories} with appropriate setters and getters.
>> 
>> I currently use multiple SQL calls to populate the n+1 properties, I use
>> a
>> <result  property="groups" column="user_id" select="getUserGroups" /> and
>> the same for categories. You get the Idea. Ideally, I'd really like to
>> use a
>> single SQL query to improve system performance.
>> 
>> Can I resolve multiple n+1 relationships in a single call and if so how.
>> I'd
>> appreciate any help. I'm sure that once I get my head around the concept
>> of
>> n+1 selects it will be easy.
>> 
>> Zoran
>> 
>> 
> --
> ==================================
> nils@nilswinkler.com
> 



Mime
View raw message