在 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 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 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' ); } // 退货原因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' ); |
1 2 3 4 5 6 7 8 9 | 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%' ) |
近期评论