【唐诗】《宮詞五首 二》 顧況：玉樓天半起笙歌，風送宮嬪笑語和。月殿影開聞夜漏，水精簾卷近銀河。

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

How to Add Join Statements on Demand When Querying Related Table Fields With spatie/laravel-query-builder in Laravel 9

The adjusted implementation is as follows: after preparing new Request($criteria), it then determines whether to join. The generated SQL joins other tables on demand, as expected

作者：

永夜

在

, , , ,
Views: 67

1. The current implementation is as follows: the generated SQL always requires joining several other tables; otherwise, an error occurs.

JSON

{
    "filter": {
        "sku_code": [
            "smileying01"
        ]
    },
    "per_page": 100,
    "page": 1
}
PHP

        // 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品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');
SQL

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), it then determines whether to join. The generated SQL joins other tables on demand, as expected. See Figure 1.

The adjusted implementation is as follows: after preparing new Request($criteria), it then determines whether to join. The generated SQL joins other tables on demand, as expected
Figure 1
PHP

        // 单个模糊检索，多个精确检索：订单号、交易号、退货单号、退货快递单号、货运单号、商品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');
SQL

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%'
  )

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我  ·  GitHub  ·  邮件联系

更多文章