mybatis-plus中collection一对多关联查询分页出错问题总结

最近项目里要返回一个下面这种json的数据，用的mybatis-plus框架

sql查数据出来是这种的

要组装成上面的json

首先想到的是用一对多关联查询，一条sql搞定，然后用collection自己组装就可以

 <resultMap id="CheckItemResponse" type="com.xxx.response.subject.CheckItemDtoResponse">
        <id column="id" property="id" />
        <result column="value" property="deviceTypeCodeName" />
        <result column="check_item" property="checkItem" />
        <result column="subject_type" property="subjectType" />
        <result column="subject" property="subject" />
        <collection property="checkItemInfoDtoResponses" ofType=com.xxx.CheckItemInfoDtoResponse">
            <result property="no" column="no"/>
            <result property="option" column="option"/>
        </collection>
    </resultMap>

用上面的collection后数据一切都OK了，后面前端调接口的人找到我说分页是不是有问题？？然后我自己测试了一下，分页真的有问题！！！！

{
    "msg": "success",
    "code": 200,
    "data": {
        "total": 4,
        "size": 10,
        "pages": 1,
        "current": 1,
        "records": [
            {
                "id": 4,
                "deviceTypeCode": null,
                "deviceTypeCodeName": null,
                "checkItem": null,
                "subjectType": null,
                "subject": "标题名称",
                "checkItemInfoDtoResponses": [
                    {
                        "id": null,
                        "itemId": null,
                        "no": "a",
                        "option": "正常"
                    }
                ]
            },
            {
                "id": 5,
                "deviceTypeCode": null,
                "deviceTypeCodeName": null,
                "checkItem": null,
                "subjectType": null,
                "subject": "检查外观",
                "checkItemInfoDtoResponses": [
                    {
                        "id": null,
                        "itemId": null,
                        "no": "a",
                        "option": "正常"
                    },
                    {
                        "id": null,
                        "itemId": null,
                        "no": "b",
                        "option": "异常"
                    },
                    {
                        "id": null,
                        "itemId": null,
                        "no": "c",
                        "option": "瞎吃"
                    }
                ]
            }
        ]
    }
}

total应该是2条，结果查出来是4条，后面网上搜索了一下，确实用上面collection这么查会导致分页不正确

所以只好改一下collection就可以了，分成两次查就可以了

<collection property="checkItemInfoDtoResponses" column="id" select="getCheckItemInfo">
            <result property="no" column="no"/>
            <result property="option" column="option"/>
        </collection>

column是关联的id，意思就是根据哪个字段来查询关联表的字段

<select id="getCheckItemInfo" resultType="com.xxx.CheckItemInfoDtoResponse">
        SELECT
        b.no,
        b.option
        FROM
        check_item_info b
        WHERE b.item_id=#{id}
        order by b.sort asc
    </select>

使用上面的collection 分页就可以正常了

会出现N+1问题，所有还是谨慎使用