ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Javier Urbaneja" <urbanej...@gmail.com>
Subject Two simultaneos M:N relations
Date Mon, 04 Sep 2006 15:25:01 GMT
Hello

I have 4 tables of interest:
USERS (ID_USER,NICKNAME,AVATAR_IMG_URL,...)
GRUOPS (ID_GROUP,DESCRIPTION,LOGO_IMG_URL,...)
GROUPS_HAVE_USERS (ID_GROUP(FK),ID_USER(FK))
GROUPS_HAVE_ADMINS (ID_GROUP(FK),ID_USER(FK))

My Group domain class has one property for the List of Admins and another
for the List of Users. I'm trying to load a List of all groups, with their
admins and users, avoiding N+1 problem.
The best I have achieve is by this mapping configuration:

  <resultMap class="group" id="groupResult" groupBy="idGroup">
    <result property="idGroup"        column="ID_GROUP"
jdbcType="INTEGER"     javaType="int"/>
    <result property="name"           column="GROUP_NAME"
jdbcType="VARCHAR"     javaType="string"/>
    <result property="adminsList"     resultMap="Group.adminResult"/>
    <result property="usersList"      resultMap="Group.memberResult"/>
  </resultMap>

  <resultMap class="user" id="adminResult" groupBy="idUser">
    <result property="idUser"        column="ADMIN"
jdbcType="INTEGER"     javaType="int"/>
    <result property="nickname"       column="ADMIN_NICK"
jdbcType="VARCHAR"     javaType="string"/>
    <result property="avatarImgUrl"   column="ADMIN_AVATAR"
jdbcType="VARCHAR"     javaType="string"/>
  </resultMap>

  <resultMap class="user" id="memberResult" groupBy="idUser">
    <result property="idUser"         column="MEMBER"
jdbcType="INTEGER"     javaType="int"/>
    <result property="nickname"       column="MEMBER_NICK"
jdbcType="VARCHAR"     javaType="string"/>
    <result property="avatarImgUrl"   column="MEMBER_AVATAR"
jdbcType="VARCHAR"     javaType="string"/>
  </resultMap>

  <sql id="selectGroups">
    select
           g.id_group,
           g.group_name,
           ga.id_user as admin,
           u1.nickname as admin_nick,
           u1.avatar_img_url as admin_avatar,
           gu.id_user as member,
           u2.nickname as member_nick,
           u2.avatar_img_url as member_avatar
        from users u1, users u2, groups g
             left join groups_have_admins ga on ga.id_group=g.id_group
             left join groups_have_users gu on gu.id_group=g.id_group
        where ga.id_user=u1.id_user and gu.id_user=u2.id_user
  </sql>

  <select id="getAllGroups" resultMap="groupResult">
    <include refid="selectGroups"/>
        order by g.group_name
  </select>

The groupBy in memberResult is not giving any success, because of the
resultset (I'm not very good at SQL), so I'm getting a set of users for each
admin in the group (it works well with only one admin).

Group One
users: 2 (true)
users: javier5,javier3 (true)
admins: 1 (true)
admins: javier2 (true)

Group Two
users: 4 (false, they are 2)
admins: 2 (true)
admins: javier2,javier1 (true)
users: javier4,javier4,javier2,javier2 (well, false, you see, they are
replicated)

I would appreciate any help. Thank you for reading.

Mime
View raw message