记录一个一对多分页查询引起的问题, 导致查询的数据不全,
需求为: 查询项目列表, 并查询出项目对应的所有老师(项目与老师为一对多的关系), 一对多查询, 这个时候, 如果将项目表设为查询主表, 老师表为从表, 并且分页查询, 且使用group by项目的id , 查询结果中,一个项目只能查出来一个老师, 此时, 就需要用到子查询, 将分页条件写在子查询中,
<select id="getPageList" resultMap="projectDetailMap">
select
zp.id as project_id,zp.project_name,zp.company_id,zp.company_name,
zp.project_start_date,zp.project_end_date,zp.status_code,zp.status_name,zp.is_push,zp.type_id,
zt.id as template_id,zt.template_name,
ztt.type_name as type_name,
ztt.id as template_type_id,ztt.type_name as template_type_name,
su.id as teacher_id, su.zh_name,su.head_photo as teacher_head_photo
from zhmd_project zp
left join zhzd_template zt on zt.id = zp.template_id
left join zhzd_type ztt on ztt.id = zp.type_id
left join zhmd_project_teacher zpt on zpt.project_id = zp.id
left join sys_user su on su.id = zpt.teacher_id
where
zp.id in
( select temp.id from (SELECT distinct zp.id from zhmd_project zp
left join zhmd_project_teacher zpt on zpt.project_id = zp.id
where zp.is_delete = 0 <include refid="projectArgs"/>
order by zp.create_date desc
<if test="start!=null and pageSize!=null">
limit #{start},#{pageSize}
</if>
) as temp)
order by zp.create_date desc
</select>
子查询如图所示, 将limit写在子查询中, 并且使用distinct , 就可以解决数据不全的问题,最后, 使用mybatis的手动映射
<resultMap id="projectDetailMap" type="com.etouch.admincenter.bean.ZhmdProjectBean">
<id column="project_id" property="id"/>
<result column="project_name" property="projectName"/>
<result column="type_id" property="typeId"/>
<result column="type_name" property="typeName"/>
<result column="template_id" property="templateId"/>
<result column="template_name" property="templateName"/>
<result column="template_type_id" property="templateTypeId"/>
<result column="template_type_name" property="templateTypeName"/>
<result column="company_id" property="companyId"/>
<result column="company_name" property="companyName"/>
<result column="project_start_date" property="projectStartDate"/>
<result column="project_end_date" property="projectEndDate"/>
<result column="status_code" property="statusCode"/>
<result column="status_name" property="statusName"/>
<result column="is_push" property="isPush"/>
<association property="monitorUser" javaType="com.etouch.admincenter.dto.UserDTO">
<id column="responsible_id" property="id"/>
<result column="responsible_login_name" property="loginName"/>
<result column="responsible_zh_name" property="zhName"/>
<result column="monitor_head_photo" property="headPhoto"/>
</association>
<association property="responsibleUser" javaType="com.etouch.admincenter.dto.UserDTO">
<id column="monitor_id" property="id"/>
<result column="monitor_login_name" property="loginName"/>
<result column="monitor_zh_name" property="zhName"/>
<result column="response_head_photo" property="headPhoto"/>
</association>
<collection property="teachers" ofType="com.etouch.admincenter.dto.UserDTO">
<id column="teacher_id" property="id"/>
<result column="teacher_login_name" property="loginName"/>
<result column="zh_name" property="zhName"/>
<result column="teacher_head_photo" property="headPhoto"/>
</collection>
</resultMap>
以下为封装的条件查询语句(其中通过isAdmin字段, 来判断当前登录用户是否是超级管理员,超管可查看所有的项目)
<sql id="projectArgs">
<if test="projectId != null">
and zp.id = #{projectId}
</if>
<if test="projectName != null">
and zp.project_name = #{projectName}
</if>
<if test="companyId != null">
and zp.company_id = #{companyId}
</if>
<if test="companyName != null">
and zp.company_name = #{companyName}
</if>
<choose>
<when test="isAdmin!=null">
and 1=1
</when>
<otherwise>
<if test="userId != null">
and (zpt.teacher_id = #{userId} or zp.project_manager_user_id =#{userId} )
</if>
</otherwise>
</choose>
</sql>