解決 mybatis一對多分頁問題 聯級查詢

顯示總訂單下的子訂單並且條件包含總訂單條件 和子訂單條件

VO

@ApiModelProperty(value = "訂單id")
	private Integer orderId;

	@ApiModelProperty(value = "訂單狀態")
	private Integer status;

	@ApiModelProperty(value = "實付(總計)")
	private Integer realPay;

	@ApiModelProperty(value = "下單時間(訂單詳情專用欄位)")
	@JsonFormat(pattern = "yyyy-MM-dd:HH:mm:ss",timezone = "Asia/Shanghai")
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	private LocalDateTime createdAt;

	@ApiModelProperty(value = "取消時間(訂單詳情專用欄位)")
	@JsonFormat(pattern = "yyyy-MM-dd:HH:mm:ss",timezone = "Asia/Shanghai")
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	private LocalDateTime cancelAt;

	@ApiModelProperty(value = "完成時間(訂單詳情專用欄位)")
	@JsonFormat(pattern = "yyyy-MM-dd:HH:mm:ss",timezone = "Asia/Shanghai")
	@JsonSerialize(using = LocalDateTimeSerializer.class)
	private LocalDateTime receivingAt;

	@ApiModelProperty(value = "子訂單資訊")
	private List<StallSubOrderVO> subOrderList;

返回值 XML
column="{orderId=orderId,stallId=stallId,subOrderStatus=subOrderStatus} 一定要對應上你的條件

<resultMap id="resultOrderByStallMap" type="StallOrderVO">
        <id column="orderId" property="orderId"/>
        <result column="status" property="status"/>
        <result column="realPay" property="realPay"/>
        <collection property="subOrderList" column="{orderId=orderId,stallId=stallId,subOrderStatus=subOrderStatus}"
                    select="getSubList"/>
    </resultMap>

總訂單 xml

<select id="getOrderListByStall" resultMap="resultOrderByStallMap">
        select o.id as orderId,o.`status`, o.real_pay as realPay, ${stallId} as stallId ,'${subOrderStatus}' as subOrderStatus
        from `order` as o
        where
        <if test="orderStatus != null">
            o.`status` in
            <foreach item="orderItem" collection="orderStatus" index="index" open="(" separator="," close=")">
                #{orderItem}
            </foreach>
            and
        </if>
        o.id in(
        select DISTINCT so.order_id
        from sub_order as so
        where so.order_id = o.id and so.stall_owner_id = #{stallId} and so.is_ready = false and so.`status` in (${subOrderStatus})
        )
        order by o.updated_at desc
    </select>

子訂單

<select id="getSubList" resultType="com.deepmedical.freshfood.http.res.stall.StallSubOrderVO">
        select id as suborderId,is_ready as isReady,dishes_name as dishesName, sku_spec as
        skuSpec, sku_price as skuPrice, amount
        from sub_order
        where order_id = #{orderId} and stall_owner_id = #{stallId} and is_ready = false
            <if test="subOrderStatus != null and subOrderStatus.length > 0">
                and `status` in  (#{subOrderStatus})
            </if>

    </select>

service(用的mybatis-plus 直接分頁)

Page page = new Page(orderForm.getPageNum(), orderForm.getPageSize());
        List orderList = baseMapper.getOrderListByStall(page, stallId, orderStatus,sb.toString());
        page.setRecords(orderList);
        return PageUtil.getPage(page.getRecords(), page.getTotal());