In Laravel 9, reuse Spatie\QueryBuilder\QueryBuilder in asynchronous queue to generate query SQL
1. In Laravel 9, a list of APIs has now been implemented, which is based on Spatie\QueryBuilder\QueryBuilder to easily build Eloquent queries from API requests. However, now you need to implement a function of exporting Excel on the list page, planned to be implemented in asynchronous queues. So, if you want to reuse the code that built the Eloquent query before, it seems that you can only pass the request object into the queue?
2. Prompt error: Serialization ofclosedis not allowed. as shown in Figure 1
Figure 1
{
"status_code": 500,
"code": 0,
"message": "Serialization of 'Closure' is not allowed",
"trace": {
"line": 158,
"file": "E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Queue\\Queue.php",
"class": "Exception",
"trace": [
"#0 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Queue\\Queue.php(158): serialize(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob))",
"#1 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Queue\\Queue.php(127): Illuminate\\Queue\\Queue->createObjectPayload(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob), 'queues:export_q...')",
"#2 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Queue\\RedisQueue.php(212): Illuminate\\Queue\\Queue->createPayloadArray(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob), 'queues:export_q...', '')",
"#3 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Queue\\Queue.php(105): Illuminate\\Queue\\RedisQueue->createPayloadArray(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob), 'queues:export_q...', '')",
"#4 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Queue\\RedisQueue.php(136): Illuminate\\Queue\\Queue->createPayload(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob), 'queues:export_q...', '')",
"#5 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Queue\\Queue.php(57): Illuminate\\Queue\\RedisQueue->push(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob), '', 'export_queue')",
"#6 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Bus\\Dispatcher.php(246): Illuminate\\Queue\\Queue->pushOn('export_queue', Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob))",
"#7 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Bus\\Dispatcher.php(229): Illuminate\\Bus\\Dispatcher->pushCommandToQueue(Object(Illuminate\\Queue\\RedisQueue), Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob))",
"#8 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Bus\\Dispatcher.php(77): Illuminate\\Bus\\Dispatcher->dispatchToQueue(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob))",
"#9 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Bus\\PendingDispatch.php(193): Illuminate\\Bus\\Dispatcher->dispatch(Object(Modules\\Order\\Jobs\\OrderShippingLogExportJob))",
"#10 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Bus\\Dispatchable.php(18): Illuminate\\Foundation\\Bus\\PendingDispatch->__destruct()",
"#11 E:\\wwwroot\\object\\Modules\\Order\\Services\\OrderShippingLogService.php(205): Modules\\Order\\Jobs\\OrderShippingLogExportJob::dispatch(Object(App\\Models\\JobStatus), Array, Object(Illuminate\\Http\\Request))",
"#12 E:\\wwwroot\\object\\Modules\\Order\\Http\\Controllers\\Api\\OrderShippingLogController.php(46): Modules\\Order\\Services\\OrderShippingLogService->export(Array, Object(Illuminate\\Http\\Request))",
"#13 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Controller.php(54): Modules\\Order\\Http\\Controllers\\Api\\OrderShippingLogController->export()",
"#14 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\ControllerDispatcher.php(43): Illuminate\\Routing\\Controller->callAction('export', Array)",
"#15 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Route.php(259): Illuminate\\Routing\\ControllerDispatcher->dispatch(Object(Illuminate\\Routing\\Route), Object(Modules\\Order\\Http\\Controllers\\Api\\OrderShippingLogController), 'export')",
"#16 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Route.php(204): Illuminate\\Routing\\Route->runController()",
"#17 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(725): Illuminate\\Routing\\Route->run()",
"#18 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(141): Illuminate\\Routing\\Router->Illuminate\\Routing\\{closure}(Object(Illuminate\\Http\\Request))",
"#19 E:\\wwwroot\\object\\vendor\\spatie\\laravel-permission\\src\\Middlewares\\RoleOrPermissionMiddleware.php(26): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#20 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Spatie\\Permission\\Middlewares\\RoleOrPermissionMiddleware->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'order_transport...')",
"#21 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Middleware\\SubstituteBindings.php(50): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#22 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Routing\\Middleware\\SubstituteBindings->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#23 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Auth\\Middleware\\Authenticate.php(44): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#24 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Auth\\Middleware\\Authenticate->handle(Object(Illuminate\\Http\\Request), Object(Closure), 'api')",
"#25 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#26 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(726): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))",
"#27 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(703): Illuminate\\Routing\\Router->runRouteWithinStack(Object(Illuminate\\Routing\\Route), Object(Illuminate\\Http\\Request))",
"#28 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(667): Illuminate\\Routing\\Router->runRoute(Object(Illuminate\\Http\\Request), Object(Illuminate\\Routing\\Route))",
"#29 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Routing\\Router.php(656): Illuminate\\Routing\\Router->dispatchToRoute(Object(Illuminate\\Http\\Request))",
"#30 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Kernel.php(190): Illuminate\\Routing\\Router->dispatch(Object(Illuminate\\Http\\Request))",
"#31 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(141): Illuminate\\Foundation\\Http\\Kernel->Illuminate\\Foundation\\Http\\{closure}(Object(Illuminate\\Http\\Request))",
"#32 E:\\wwwroot\\object\\app\\Http\\Middleware\\QueryListenerMiddleware.php(30): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#33 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): App\\Http\\Middleware\\QueryListenerMiddleware->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#34 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest.php(21): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#35 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\TrimStrings.php(40): Illuminate\\Foundation\\Http\\Middleware\\TransformsRequest->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#36 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\TrimStrings->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#37 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize.php(27): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#38 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\ValidatePostSize->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#39 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance.php(86): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#40 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Foundation\\Http\\Middleware\\PreventRequestsDuringMaintenance->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#41 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Http\\Middleware\\HandleCors.php(62): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#42 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Http\\Middleware\\HandleCors->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#43 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Http\\Middleware\\TrustProxies.php(39): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#44 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(180): Illuminate\\Http\\Middleware\\TrustProxies->handle(Object(Illuminate\\Http\\Request), Object(Closure))",
"#45 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Pipeline\\Pipeline.php(116): Illuminate\\Pipeline\\Pipeline->Illuminate\\Pipeline\\{closure}(Object(Illuminate\\Http\\Request))",
"#46 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Kernel.php(165): Illuminate\\Pipeline\\Pipeline->then(Object(Closure))",
"#47 E:\\wwwroot\\object\\vendor\\laravel\\framework\\src\\Illuminate\\Foundation\\Http\\Kernel.php(134): Illuminate\\Foundation\\Http\\Kernel->sendRequestThroughRouter(Object(Illuminate\\Http\\Request))",
"#48 E:\\wwwroot\\object\\public\\index.php(52): Illuminate\\Foundation\\Http\\Kernel->handle(Object(Illuminate\\Http\\Request))",
"#49 {main}"
]
}
}
3. Try to convert the request object into an array first, then in the asynchronous queue, and then convert it to the request object again. Attempt to print all request data of the native request object and the converted request object. Exactly equal, as expected.
$params = $this->request->all();
print_r($params);
$request = new Request($params);
print_r($request);
print_r($request->all());
exit;
Array
(
[filter] => Array
(
[shop_plat_id] => Array
(
[0] => 1
[1] => 2
)
[shop_id] => Array
(
[0] => 1
[1] => 14
)
[logistic_channel_id] => Array
(
[0] => 304872
[1] => 474241
)
[logistic_company_id] => Array
(
[0] => 10336
[1] => 10340
)
[operated_source] => Array
(
[0] => 1
[1] => 2
)
[plat_order_no] => Array
(
[0] => GM20240202055938
[1] => GM20240131081251
)
[logistic_freight_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_freight_transfer_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_freight_inner_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_virtual_number] => Array
(
[0] => GM20240202055938766
[1] => GM20240131081251410
)
[shipping_type] => Array
(
[0] => 1
[1] => 2
)
[shipping_status] => Array
(
[0] => 2
[1] => 3
)
[shipping_at_gmt_start] => 2024-02-02 05:52:31
[shipping_at_gmt_end] => 2024-02-04 05:52:31
[operated_at_gmt_start] => 2024-02-02 05:52:31
[operated_at_gmt_end] => 2024-02-04 05:52:31
)
[per_page] => 20
[page] => 1
)
Illuminate\Http\Request Object
(
[attributes] => Symfony\Component\HttpFoundation\ParameterBag Object
(
[parameters:protected] => Array
(
)
)
[request] => Symfony\Component\HttpFoundation\InputBag Object
(
[parameters:protected] => Array
(
)
)
[query] => Symfony\Component\HttpFoundation\InputBag Object
(
[parameters:protected] => Array
(
[filter] => Array
(
[shop_plat_id] => Array
(
[0] => 1
[1] => 2
)
[shop_id] => Array
(
[0] => 1
[1] => 14
)
[logistic_channel_id] => Array
(
[0] => 304872
[1] => 474241
)
[logistic_company_id] => Array
(
[0] => 10336
[1] => 10340
)
[operated_source] => Array
(
[0] => 1
[1] => 2
)
[plat_order_no] => Array
(
[0] => GM20240202055938
[1] => GM20240131081251
)
[logistic_freight_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_freight_transfer_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_freight_inner_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_virtual_number] => Array
(
[0] => GM20240202055938766
[1] => GM20240131081251410
)
[shipping_type] => Array
(
[0] => 1
[1] => 2
)
[shipping_status] => Array
(
[0] => 2
[1] => 3
)
[shipping_at_gmt_start] => 2024-02-02 05:52:31
[shipping_at_gmt_end] => 2024-02-04 05:52:31
[operated_at_gmt_start] => 2024-02-02 05:52:31
[operated_at_gmt_end] => 2024-02-04 05:52:31
)
[per_page] => 20
[page] => 1
)
)
[server] => Symfony\Component\HttpFoundation\ServerBag Object
(
[parameters:protected] => Array
(
)
)
[files] => Symfony\Component\HttpFoundation\FileBag Object
(
[parameters:protected] => Array
(
)
)
[cookies] => Symfony\Component\HttpFoundation\InputBag Object
(
[parameters:protected] => Array
(
)
)
[headers] => Symfony\Component\HttpFoundation\HeaderBag Object
(
[headers:protected] => Array
(
)
[cacheControl:protected] => Array
(
)
)
[content:protected] =>
[languages:protected] =>
[charsets:protected] =>
[encodings:protected] =>
[acceptableContentTypes:protected] =>
[pathInfo:protected] =>
[requestUri:protected] =>
[baseUrl:protected] =>
[basePath:protected] =>
[method:protected] =>
[format:protected] =>
[session:protected] =>
[locale:protected] =>
[defaultLocale:protected] => en
[preferredFormat:Symfony\Component\HttpFoundation\Request:private] =>
[isHostValid:Symfony\Component\HttpFoundation\Request:private] => 1
[isForwardedValid:Symfony\Component\HttpFoundation\Request:private] => 1
[json:protected] =>
[convertedFiles:protected] =>
[userResolver:protected] =>
[routeResolver:protected] =>
)
Array
(
[filter] => Array
(
[shop_plat_id] => Array
(
[0] => 1
[1] => 2
)
[shop_id] => Array
(
[0] => 1
[1] => 14
)
[logistic_channel_id] => Array
(
[0] => 304872
[1] => 474241
)
[logistic_company_id] => Array
(
[0] => 10336
[1] => 10340
)
[operated_source] => Array
(
[0] => 1
[1] => 2
)
[plat_order_no] => Array
(
[0] => GM20240202055938
[1] => GM20240131081251
)
[logistic_freight_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_freight_transfer_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_freight_inner_no] => Array
(
[0] => PH900016739XX
[1] => PH900890788XX
)
[logistic_virtual_number] => Array
(
[0] => GM20240202055938766
[1] => GM20240131081251410
)
[shipping_type] => Array
(
[0] => 1
[1] => 2
)
[shipping_status] => Array
(
[0] => 2
[1] => 3
)
[shipping_at_gmt_start] => 2024-02-02 05:52:31
[shipping_at_gmt_end] => 2024-02-04 05:52:31
[operated_at_gmt_start] => 2024-02-02 05:52:31
[operated_at_gmt_end] => 2024-02-04 05:52:31
)
[per_page] => 20
[page] => 1
)
4. The final realization is as follows, in line with expectations. as shown in Figure 2
Figure 2
$orderShippingLogQueryBuilder = $this->repository->QueryBuilder($params);
$orderShippingLogQueryBuilder->chunk(100, function ($orderShippingLogs) {
foreach ($orderShippingLogs as $orderShippingLog) {
echo $orderShippingLog->id;
}
});
exit;
/**
* 查询生成器
* @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');
}
