In Laravel 9, when querying the fields in the association table based on the spatie/laravel-query-builder, add the implementation of the join statement as needed
1. The implementation at this stage is as follows. The final generated SQL will always need to be associated with several other tables, otherwise an error will be reported.
{
"filter": {
"sku_code": [
"smileying01"
]
},
"per_page": 100,
"page": 1
}
// 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品SKU
// 键为请求参数，值为 SQL 查询字段
$fields = [
'plat_order_no' => 'plat_order_no',
'transaction_no' => 'transaction_no',
'return_order_no' => 'return_order_no',
'return_logistics_freight_no' => 'return_order_logistics.logistics_freight_no',
'logistics_freight_no' => 'logistics_freight_no',
'sku_code' => 'return_order_items.sku_code',
];
$allowedFilters = [
AllowedFilter::exact('signing_status'),
AllowedFilter::partial('remark'),
AllowedFilter::exact('return_type'),
AllowedFilter::exact('processing_method'),
AllowedFilter::scope('created_at_gmt_start'),
AllowedFilter::scope('created_at_gmt_end'),
AllowedFilter::scope('order_payment_at_gmt_start'),
AllowedFilter::scope('order_payment_at_gmt_end'),
AllowedFilter::scope('order_delivery_at_gmt_start'),
AllowedFilter::scope('order_delivery_at_gmt_end'),
];
foreach ($fields as $key => $field) {
if (isset($criteria['filter'][$key])) {
if (count($criteria['filter'][$key]) > 1) {
// 多个精确检索
$allowedFilters[] = AllowedFilter::exact($field);
} else {
// 单个模糊检索
$allowedFilters[] = AllowedFilter::partial($field);
}
if ($key != $field) {
$criteria['filter'][$field] = $criteria['filter'][$key];
unset($criteria['filter'][$key]);
}
}
}
// 退货原因ID，转换 SQL 查询字段
if (isset($criteria['filter']['refund_reason_id'])) {
$criteria['filter']['return_order_refund_reasons.refund_reason_id'] = $criteria['filter']['refund_reason_id'];
unset($criteria['filter']['refund_reason_id']);
$allowedFilters[] = AllowedFilter::exact('return_order_refund_reasons.refund_reason_id');
}
$builder = QueryBuilder::for(ReturnOrder::class, new Request($criteria))
->select('return_orders.*')
->leftJoin('return_order_items', 'return_order_items.return_order_id', 'return_orders.id')
->leftJoin('return_order_refund_reasons', 'return_order_refund_reasons.return_order_id', 'return_orders.id')
->leftJoin('return_order_logistics', 'return_order_logistics.return_order_id', 'return_orders.id')
->allowedFilters($allowedFilters)
->with('returnOrderItems')
->with('returnOrderRefundReasons.refundReason')
->with('returnOrderLogistics')
->with('resendOrder')
->with('order.orderItemLogisticsFeatures')
->defaultSort('-id');
select
count(*) as aggregate
from
`return_orders`
left join `return_order_items` on `return_order_items`.`return_order_id` = `return_orders`.`id`
left join `return_order_refund_reasons` on `return_order_refund_reasons`.`return_order_id` = `return_orders`.`id`
left join `return_order_logistics` on `return_order_logistics`.`return_order_id` = `return_orders`.`id`
where
(
LOWER(`return_order_items`.`sku_code`) LIKE '%smileying01%'
)
2. The adjusted implementation is as follows. After preparing new request($criteria), then judge whether to join, and the generated sql is associated with other tables on demand, which is in line with expectations. as shown in Figure 1
Figure 1
// 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品SKU
// 键为请求参数，值为 SQL 查询字段
$fields = [
'plat_order_no' => 'plat_order_no',
'transaction_no' => 'transaction_no',
'return_order_no' => 'return_order_no',
'return_logistics_freight_no' => 'return_order_logistics.logistics_freight_no',
'logistics_freight_no' => 'logistics_freight_no',
'sku_code' => 'return_order_items.sku_code',
];
$allowedFilters = [
AllowedFilter::exact('signing_status'),
AllowedFilter::partial('remark'),
AllowedFilter::exact('return_type'),
AllowedFilter::exact('processing_method'),
AllowedFilter::scope('created_at_gmt_start'),
AllowedFilter::scope('created_at_gmt_end'),
AllowedFilter::scope('order_payment_at_gmt_start'),
AllowedFilter::scope('order_payment_at_gmt_end'),
AllowedFilter::scope('order_delivery_at_gmt_start'),
AllowedFilter::scope('order_delivery_at_gmt_end'),
];
foreach ($fields as $key => $field) {
if (isset($criteria['filter'][$key])) {
if (count($criteria['filter'][$key]) > 1) {
// 多个精确检索
$allowedFilters[] = AllowedFilter::exact($field);
} else {
// 单个模糊检索
$allowedFilters[] = AllowedFilter::partial($field);
}
if ($key != $field) {
$criteria['filter'][$field] = $criteria['filter'][$key];
unset($criteria['filter'][$key]);
}
}
}
// 退货原因ID，转换 SQL 查询字段
if (isset($criteria['filter']['refund_reason_id'])) {
$criteria['filter']['return_order_refund_reasons.refund_reason_id'] = $criteria['filter']['refund_reason_id'];
unset($criteria['filter']['refund_reason_id']);
$allowedFilters[] = AllowedFilter::exact('return_order_refund_reasons.refund_reason_id');
}
$builder = QueryBuilder::for(ReturnOrder::class, new Request($criteria))->select('return_orders.*');
// 退货快递单号
if (isset($criteria['filter']['return_logistics_freight_no'])) {
$builder->leftJoin('return_order_logistics', 'return_order_logistics.return_order_id', 'return_orders.id');
}
// 商品 SKU 编码
if (isset($criteria['filter']['return_order_items.sku_code'])) {
$builder->leftJoin('return_order_items', 'return_order_items.return_order_id', 'return_orders.id');
}
// 退货原因ID
if (isset($criteria['filter']['refund_reason_id'])) {
$builder->leftJoin('return_order_refund_reasons', 'return_order_refund_reasons.return_order_id', 'return_orders.id');
$allowedFilters[] = AllowedFilter::exact('return_order_refund_reasons.refund_reason_id');
}
$builder->allowedFilters($allowedFilters)
->with('returnOrderItems')
->with('returnOrderRefundReasons.refundReason')
->with('returnOrderLogistics')
->with('resendOrder')
->with('order.orderItemLogisticsFeatures')
->defaultSort('-id');
select
count(*) as aggregate
from
`return_orders`
left join `return_order_items` on `return_order_items`.`return_order_id` = `return_orders`.`id`
where
(
LOWER(`return_order_items`.`sku_code`) LIKE '%smileying01%'
)
