在 Laravel 9 中，基于 spatie/laravel-query-builder 查询关联表中的字段时，按需添加 join 语句的实现
1、现阶段的实现如下，最终生成的 SQL 永远都需要 join 关联上其他几张表，否则会报错
1 2 3 4 5 6 7 8 9 | { "filter": { "sku_code": [ "smileying01" ] }, "per_page": 100, "page": 1} |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 | // 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品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'); |
1 2 3 4 5 6 7 8 9 10 11 | select count(*) as aggregatefrom `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、调整后的实现如下，在准备好 new Request($criteria) 之后，再判断是否 join ，生成的 SQL 按需关联其他表，符合预期。如图1
图1
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 | // 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品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');}// 退货原因IDif (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'); |
1 2 3 4 5 6 7 8 9 | select count(*) as aggregatefrom `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%' ) |
近期评论