mybatisplus 一对多（collection）分页解决方案

mybatisplus 一对多（collection）分页解决方案

简单以订单、商品来举例说明，即一个订单包含多个商品

背景1：查询订单表的内容，商品表不参与条件查询，用懒加载形式

<resultMap id="OrderInfoListResultMap" type="*******OrderInfoVo">
        <id column="id" property="id"/>
        <result column="order_name" property="orderName"/>
        <result column="order_no" property="orderNo"/>      
        ...........省略若干
        <collection property="goodsList" javaType="ArrayList" ofType="****OrderGoodsVo"
             select="getOrderGoodsVoByOrderId" column="{orderId = id}"/>
</resultMap>

 <select id="findBuyerOrderListByPage" resultMap="OrderInfoListResultMap">
 	SELECT *FROM `order_info`
</select>

<select id="getOrderGoodsVoByOrderId" resultType="*******OrderGoodsVo">
        SELECT * FROM `order_goods` WHERE `order_id` = #{orderId}
</select>

背景2：查询订单表的内容，商品表要作为查询条件参与查询，例如要根据商品名称查询出订单信息

<resultMap id="OrderInfoListResultMap" type="*******OrderInfoVo">
        <id column="id" property="id"/>
        <result column="order_name" property="orderName"/>
        <result column="order_no" property="orderNo"/>      
        ...........省略若干
        <collection property="goodsList" javaType="ArrayList" ofType="****OrderGoodsVo"
             select="getOrderGoodsVoByOrderId" column="{orderId = id,goodsName = goodsName}"/>
</resultMap>

 <select id="findBuyerOrderListByPage" resultMap="OrderInfoListResultMap">
 	  select temp.* from (
            SELECT distinct (og.order_id) as maskkey,
            oi.*,#{orderInfo.goodsName} as goodsName
            FROM
            `order_info` oi LEFT JOIN `order_goods` og ON oi.`id` = og.`order_id`
             <where>
	            <if test="orderInfo.goodsName != null and orderInfo.goodsName != ''">and og.goods_name like concat('%',#{orderInfo.goodsName},'%')
	            </if>
            </where>
            ) temp
</select>

<select id="getOrderGoodsVoByOrderId" resultType="*******OrderGoodsVo">
        SELECT * FROM `order_goods` WHERE `order_id` = #{orderId}
         <if test="goodsName != null and goodsName != ''">
            and goods_name like concat('%',#{goodsName},'%')
        </if>
</select>

重点：

1. 将goodsName传递到子查询中
2. 借用一对多的特性，利用左查询联合筛选订单信息，保证分页条数的准确性