一对多分页查询引起的数据问题

记录一个一对多分页查询引起的问题, 导致查询的数据不全,
需求为: 查询项目列表, 并查询出项目对应的所有老师(项目与老师为一对多的关系), 一对多查询, 这个时候, 如果将项目表设为查询主表, 老师表为从表, 并且分页查询, 且使用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>