MyBatis Plus对关联查询一对多 分页不准确问题解决

今天在和前端联调的时候，突然被告知接口分页有问题，不准确，随后debug发现的确如此：

xml代码：

<resultMap id="getRoleInfoMap" type="com.lylb.casecenter.entity.vo.RoleVO">
        <id column="roleId" property="id"/>
        <result column="roleName" property="name"/>
        <collection property="permissionList" ofType="com.lylb.casecenter.entity.vo.PermissionVO">
            <id column="permissionId" property="permissionId"/>
            <result column="name" property="name"/>
        </collection>
    </resultMap>

 <select id="getRoleList" resultMap="getRoleInfoMap">
        SELECT
            r.id roleId,
            r.NAME roleName,
            p.id permissionId,
            p.name
        FROM
            role_permission rp
            LEFT JOIN role r ON rp.role_id = r.id
            LEFT JOIN permission p ON rp.permission_id = p.id
        WHERE
            r.`status` = '0'
            AND r.deleted = '0'
    </select>

随后查看MyBatis Plus 官方文档发现：

而我这里恰恰如此，left join的表 并没有参与where条件过滤。

解决方案：

将关联查询分开查询，将结果合并展示即可

更改之后代码

    <resultMap id="getRoleInfoMap" type="com.lylb.casecenter.entity.vo.RoleVO">
        <id column="roleId" property="id"/>
        <result column="roleName" property="name"/>
        <collection property="permissionList" ofType="com.lylb.casecenter.entity.vo.PermissionVO"
                    javaType="java.util.List" select="getPermission" column="roleId">

        </collection>
    </resultMap>

    <resultMap id="getPermissionMap" type="com.lylb.casecenter.entity.vo.PermissionVO">
        <id column="permissionId" property="permissionId"/>
        <result column="name" property="name"/>
    </resultMap>

    <select id="getRoleList" resultMap="getRoleInfoMap">
        SELECT
            r.id roleId,
            r.NAME roleName
        FROM role r
        WHERE r.`status` = '0' AND r.deleted = '0'
    </select>

    <select id="getPermission" resultType="com.lylb.casecenter.entity.vo.PermissionVO">
        select
            rp.role_id roleId,
            p.id permissionId,
            p.name
        from role_permission rp
        LEFT JOIN permission p ON rp.permission_id = p.id
        where rp.role_id = #{roleId}
    </select>

结果正确