在 Laravel 9 中，基于 spatie/laravel-query-builder 查询关联表中的字段时，按需添加 join 语句的实现

1、现阶段的实现如下，最终生成的 SQL 永远都需要 join 关联上其他几张表，否则会报错

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

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

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、调整后的实现如下，在准备好 new Request($criteria) 之后，再判断是否 join ，生成的 SQL 按需关联其他表，符合预期。如图1

图1

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

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