ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Javier Urbaneja" <urbanej...@gmail.com>
Subject Re: Two simultaneos M:N relations
Date Tue, 05 Sep 2006 08:59:53 GMT
Forget about it. I'm going to change the design to avoid this problem. One
table GROUPS_HAVE_USERS (ID_GROUP(FK),ID_USER(FK), ADMIN) with the admin
field, to populate the admin list in the DAO Impl.
Thank you anyway if you have bothered to read this.

On 9/4/06, Javier Urbaneja <urbanejarl@gmail.com> wrote:
>
> 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