<?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.gxwebsoft.common.system.mapper.UserRoleMapper">

  <insert id="insertBatch">
    INSERT INTO sys_user_role(user_id, role_id) VALUES
    <foreach collection="roleIds" item="roleId" separator=",">
      (#{userId}, #{roleId})
    </foreach>
  </insert>

  <select id="selectByUserId" resultType="com.gxwebsoft.common.system.entity.Role">
    SELECT *
    FROM sys_role
    WHERE role_id IN (SELECT role_id
                      FROM sys_user_role
                      WHERE user_id = #{userId})
      AND deleted = 0
    ORDER BY role_id desc
  </select>

  <select id="selectByUserIds" resultType="com.gxwebsoft.common.system.entity.Role">
    SELECT a.user_id, b.*
    FROM sys_user_role a
    LEFT JOIN sys_role b ON a.role_id = b.role_id
    WHERE a.user_id IN
    <foreach collection="userIds" open="(" close=")" separator="," item="userId">
      #{userId}
    </foreach>
    AND b.deleted = 0
  </select>

  <!-- 关联查询sql -->
  <sql id="selectSql">
    SELECT a.*, b.role_name, b.role_code
    FROM sys_user_role a
    LEFT JOIN sys_role b ON a.role_id = b.role_id
    <where>
      <if test="param.id != null">
        AND a.id = #{param.id}
      </if>
      <if test="param.userId != null">
        AND a.user_id = #{param.userId}
      </if>
      <if test="param.roleId != null">
        AND a.role_id = #{param.roleId}
      </if>
      <if test="param.createTimeStart != null">
        AND a.create_time &gt;= #{param.createTimeStart}
      </if>
      <if test="param.createTimeEnd != null">
        AND a.create_time &lt;= #{param.createTimeEnd}
      </if>
    </where>
  </sql>

  <!-- 分页查询 -->
  <select id="selectPageRel" resultType="com.gxwebsoft.common.system.entity.UserRole">
    <include refid="selectSql"></include>
  </select>

  <!-- 查询全部 -->
  <select id="selectListRel" resultType="com.gxwebsoft.common.system.entity.UserRole">
    <include refid="selectSql"></include>
  </select>

</mapper>
