<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.sf.mapper.SysRoleMapper">

    <resultMap id="roleAndUsersMap" type="sysRole">
        <id property="roleId" column="role_id"/>
        <result property="roleName" column="role_name"/>
        <!-- 一对多关联用户（collection标签） -->
        <collection property="userList" ofType="sysUser">
            <id property="userId" column="user_id"/>
            <result property="username" column="username"/>
            <result property="status" column="status"/>
            <!-- 嵌套一对一关联用户详情 -->
            <association property="userInfo" javaType="sysUserInfo">
                <result property="realName" column="real_name"/>
                <result property="phone" column="phone"/>
            </association>
        </collection>
    </resultMap>

    <sql id="roleAndUserList">
        SELECT r.*,u.*
        FROM sys_role r
        LEFT JOIN sys_user u
        ON r.role_id=u.role_id
    </sql>
    <select id="findAll" resultMap="roleAndUsersMap">
        <include refid="roleAndUserList"/>
    </select>

    <select id="selectRoleWithUsers" resultMap="roleWithUsersMap">
        SELECT
            r.*,
            u.user_id, u.username, u.status,
            i.real_name, i.phone
        FROM sys_role r
                 LEFT JOIN sys_user u ON r.role_id = u.role_id
                 LEFT JOIN sys_user_info i ON u.user_id = i.user_id
        WHERE r.role_id = #{roleId}
    </select>

    <select id="findById" resultMap="roleAndUsersMap" parameterType="integer">
        <include refid="roleAndUserList"/>
        <where>
            r.role_id=#{id}
        </where>
    </select>

    <select id="findRoleWithMenus" resultMap="roleWithMenusMap">
        SELECT r.*,m.*
        FROM sys_role r
                 LEFT JOIN sys_role_menu rm ON r.role_id=rm.role_id
                 LEFT JOIN sys_menu m ON m.menu_id=rm.menu_id
        WHERE r.role_id=1
    </select>
    <resultMap id="roleWithMenusMap" type="sysRole">
        <id column="role_id" property="roleId"/>
        <result column="role_name" property="roleName"/>
        <result column="role_code" property="roleCode"/>
        <result column="description" property="description"/>
        <result column="create_time" property="createTime"/>
        <collection property="menuList" ofType="sysMenu">
            <id column="menu_id" property="menuId"/>
            <result column="menu_name" property="menuName"/>
            <result column="parent_id" property="parentId"/>
            <result column="url" property="url"/>
            <result column="menu_type" property="menuType"/>
            <result column="sort" property="sort"/>
            <result column="create_time" property="createTime"/>
        </collection>
    </resultMap>
</mapper>