1. Reference: In Laravel 9, reusing Spatie\QueryBuilder\QueryBuilder in an asynchronous queue to generate query SQL
PHP
/**
* 查询生成器
* @param array $criteria
* @return QueryBuilder
*/
public function QueryBuilder(array $criteria): QueryBuilder
{
return QueryBuilder::for(OrderShippingLog::class, new Request($criteria))
->allowedFilters([
AllowedFilter::exact('shop_plat_id'),
AllowedFilter::exact('shop_id'),
AllowedFilter::exact('logistic_channel_id'),
AllowedFilter::exact('logistic_company_id'),
AllowedFilter::exact('operated_source'),
AllowedFilter::exact('plat_order_no'),
AllowedFilter::exact('logistic_freight_no'),
AllowedFilter::exact('logistic_freight_transfer_no'),
AllowedFilter::exact('logistic_freight_inner_no'),
AllowedFilter::exact('logistic_virtual_number'),
AllowedFilter::exact('shipping_type'),
AllowedFilter::exact('shipping_status'),
AllowedFilter::scope('shipping_at_gmt_start'),
AllowedFilter::scope('shipping_at_gmt_end'),
AllowedFilter::scope('operated_at_gmt_start'),
AllowedFilter::scope('operated_at_gmt_end'),
])
->with('shopPlat:id,title')
->with('shop:id,title')
->with('order.orderItemLogisticsFeatures')
->with('order.urgentType:id,title,color')
->with('logisticChannel:id,title')
->with('operatorUser:id,name,number')
->defaultSort('-id');
}
2. The current implementation only allows a single filter type per field—either partial or exact. Reference: Implementing request parameter formats for filters in Yii 2.0.
JSON
{
"filter": {
"plat_order_no": [
"GM20240202055938",
"GM20240131081251"
]
},
"sort": "-operated_at_gmt",
"per_page": 20,
"page": 1
}
JSON
{
"filter": {
"plat_order_no": [
"like": [
"GM20240202055938",
"GM20240131081251"
]
]
},
"sort": "-operated_at_gmt",
"per_page": 20,
"page": 1
}
3. For fields that require a single fuzzy search and multiple exact searches, I decided to validate the input as an array and then check the number of elements. If the count is greater than 1, it performs an exact search; otherwise, it performs a fuzzy search. The final implementation is as follows.
PHP
$validator = Validator::make(
$params,
[
'filter.plat_order_no' => 'array',
'filter.plat_order_no.*' => 'max:100',
],
[
'filter.plat_order_no.array' => '原订单的平台订单号必须是数组',
'filter.plat_order_no.*.max' => '原订单的平台订单号最大长度是100',
]
);
if ($validator->stopOnFirstFailure()->fails()) {
throw new BusinessException(BusinessException::MODULE_ORDER, $validator->getMessageBag()->first());
}
return $validator;
PHP
// 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品SKU
// 键为请求参数，值为 SQL 查询字段
$fields = [
'plat_order_no' => 'plat_order_no',
];
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]);
}
}
}
$builder = QueryBuilder::for(ReturnOrder::class, new Request($criteria))
->select('return_orders.*')
->allowedFilters($allowedFilters)
->defaultSort('-id');