In Laravel 9, the query SQL is generated based on Spatie\QueryBuilder\QueryBuilder, the same field can be supported: Partial, Exact
1. Reference:In Laravel 9, reuse Spatie\QueryBuilder\QueryBuilder in asynchronous queue to generate query SQL
/**
* 查询生成器
* @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 scheme, for a certain field, can only use one filter, or partial, or Exact. Reference:In Yii 2.0, implement the request parameter form of the filter.
{
"filter": {
"plat_order_no": [
"GM20240202055938",
"GM20240131081251"
]
},
"sort": "-operated_at_gmt",
"per_page": 20,
"page": 1
}
{
"filter": {
"plat_order_no": [
"like": [
"GM20240202055938",
"GM20240131081251"
]
]
},
"sort": "-operated_at_gmt",
"per_page": 20,
"page": 1
}
3. For a single fuzzy search, multiple accurate search fields, decide to verify the type of an array, and then determine the number of array elements, if it is greater than 1, then accurately retrieve, otherwise, fuzzy search. The final implementation is as follows.
$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;
// 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品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');
