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-unsubscr...@ibatis.apache.org
For additional commands, e-mail: user-java-h...@ibatis.apache.org
