ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From quinya <y.qui...@ilocal.nl>
Subject Outer Join - Mapping More Than One
Date Tue, 22 Sep 2009 14:54:48 GMT

Hi,

I am having problems mapping more than one outer join to a result set. I saw
another posting for a similar issue, but I still can't get it to work.

I basically have three objects - User, Role and UserGroup.

The respective tables have many-to-many relationships implemented with join
tables.

The actual objects have the relationships mapped as:
User has a roleList and userGroupList.
UserGroup has a roleList.

When I get the user, I want to bring the whole lot back in one big query.

I have a test user that should have 2 items in the User.roleList, 1 item in
the user.userGroupList and 2 items in the user.userGroupList[0].roleList.

But instead I end up with 4 items in the User.roleList (2 distinct, 2
duplicates) and 4 items in the user.userGroupList[0].roleList (2 distinct, 2
duplicates).

I suspect it has something to do with the way I am trying to join to the
same table (role), but two different instances... if you know what I mean...

Anyway, my SQL mapping is below. I'm using MySQL and iBatis 2.3.4.726.

Any help is greatly appreciated.


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL MAP 2.0//EN" 	
	"http://www.ibatis.com/dtd/sql-map-2.dtd">
	
<sqlMap namespace="user-join"> 
   	
   	
   	<resultMap id="userSummaryResultMap2" class="userSummary">
		<result property="id" column="ilocal_user.id"/>
		<result property="created_time" column="ilocal_user.created_time"/>
		<result property="updated_time" column="ilocal_user.updated_time"/>
		<result property="old_updated_time" column="ilocal_user.updated_time"/>
		<result property="username" column="ilocal_user.username"/>
		<result property="password" column="ilocal_user.password"/>
		<result property="firstName" column="ilocal_user.first_name"/>
		<result property="lastName" column="ilocal_user.last_name"/>
		<result property="dateOfBirth" column="ilocal_user.date_of_birth"/>
		<result property="phoneNumber" column="ilocal_user.phone_number"/>
	</resultMap> 
	
	<resultMap id="userResultMap2" class="user" extends="userSummaryResultMap2"
groupBy="id">
		<result property="roleList" column="ilocal_user.id"
resultMap="user-join.roleResultMap2" notNullColumn="role.id"/>
		<result property="userGroupList" column="ilocal_user.id"
resultMap="user-join.userGroupResultMap2" notNullColumn="user_group.id"/>
	</resultMap> 
	
	<resultMap id="roleResultMap2" class="role">
		<result property="id" column="role.id"/>
		<result property="created_time" column="role.created_time"/>
		<result property="updated_time" column="role.updated_time"/>
		<result property="old_updated_time" column="role.updated_time"/>
		<result property="authority" column="role.authority"/>
		<result property="levelNumber" column="role.level_number"/>
	</resultMap>
	
	<resultMap id="roleResultMap3" class="role">
		<result property="id" column="role2.id"/>
		<result property="created_time" column="role2.created_time"/>
		<result property="updated_time" column="role2.updated_time"/>
		<result property="old_updated_time" column="role2.updated_time"/>
		<result property="authority" column="role2.authority"/>
		<result property="levelNumber" column="role2.level_number"/>
	</resultMap>
	
	<resultMap id="userGroupSummaryResultMap2" class="userGroupSummary">
		<result property="id" column="user_group.id"/>
		<result property="created_time" column="user_group.created_time"/>
		<result property="updated_time" column="user_group.updated_time"/>
		<result property="old_updated_time" column="user_group.updated_time"/>
		<result property="description" column="description"/>
	</resultMap>    
	
	<resultMap id="userGroupResultMap2" class="userGroup"
extends="userGroupSummaryResultMap2" groupBy="id">
		<result property="roleList" column="user_group.id"
resultMap="user-join.roleResultMap3" notNullColumn="role2.id"/> 
	</resultMap>   
	
	<select id="getUserById2" parameterClass="long"
resultMap="user-join.userResultMap2" remapResults="true">
		select
		  ilocal_user.*,
		  role.*,
		  user_group.*,
		  role2.*
		from
		  ilocal_user
		LEFT OUTER JOIN
		  user_role
		ON
		  ilocal_user.id = user_role.ilocal_user_id
		LEFT OUTER JOIN
		  role role
		ON
		  user_role.role_id = role.id
		LEFT OUTER JOIN
		  user_group_user 
		ON
		  ilocal_user.id = user_group_user.ilocal_user_id
		LEFT OUTER JOIN
		  user_group 
		ON
		  user_group_user.user_group_id = user_group.id
		LEFT OUTER JOIN
		  user_group_role 
		ON
		  user_group.id = user_group_role.user_group_id
		LEFT OUTER JOIN
		  role role2
		ON
		  user_group_role.role_id = role2.id
		WHERE 
			ilocal_user.id = #id#
	</select> 
					
</sqlMap>









-- 
View this message in context: http://www.nabble.com/Outer-Join---Mapping-More-Than-One-tp25530776p25530776.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: user-java-unsubscribe@ibatis.apache.org
For additional commands, e-mail: user-java-help@ibatis.apache.org


Mime
View raw message