解决 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());
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页