laravel 多条件结合scope查询作用域优化

背景

在很多情况下，列表数据都需要筛选，比如订单数据列表，可能根据前台传的订单状态，用户邮箱等条件进行筛选，通常我们的代码如下:

		$orders_handler = Order::query();
		//筛选条件
        if ($request->get('status','')) { 
             $orders_handler->where('status', 'escrow');
             
        }

		if ($params['shipment_status']) { #发货方式
            switch ($params['shipment_status']) {
                case 1 ://订货代购单交期延误
                    $orders_handler->where('type', '<>', Order::TYPE_IN_STOCK)
                        ->whereIn('payment.status', [Order::PAYMENT_PAID, Order::PAYMENT_PARTIALLY_REFUNDED])
                        ->where('status', '<=', Order::STATUS_VERIFIED)
                        ->where('estimated_shipment_date', '<', time());
                    break;
                case 2 ://现货独立未发货
                    $orders_handler->where('type', Order::TYPE_IN_STOCK)
                        ->whereIn('payment.status', [Order::PAYMENT_PAID, Order::PAYMENT_PARTIALLY_REFUNDED])
                        ->where('status', '<=', Order::STATUS_DISPATCH)
                        ->where('shipment_type', '<>', Order::SHIPMENT_ACCOUNT_JLC)
                        ->exceptOldOrder();
                    break;
                case 3 ://绑PCB未发货
                    $orders_handler->whereIn('payment.status', [Order::PAYMENT_PAID, Order::PAYMENT_PARTIALLY_REFUNDED])
                        ->where('status', '<=', Order::STATUS_DISPATCH)
                        ->where('shipment_type', Order::SHIPMENT_ACCOUNT_JLC);
                    break;
                case 4 ://订货代购单未发货
                    $orders_handler->where('type', '<>', Order::TYPE_IN_STOCK)
                        ->whereIn('payment.status', [Order::PAYMENT_PAID, Order::PAYMENT_PARTIALLY_REFUNDED])
                        ->where('status', '<=', Order::STATUS_DISPATCH)
                        ->exceptOldOrder();
                    break;
                case 5 ://已发货
                    $orders_handler->where('status', '>=', Order::STATUS_DISPATCH);
                    break;
            }
        }
        
        .....
      
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

最重要的是这种筛选条件可能根据需求不断的添加．．．

那么我们是否可以优化这种情况呢…

实现

首先我们先建一个说有过滤条件条件的基类：
基类的位置同学们可以随便放，我是新建目录Ｆilter
BaseQueryFilter:

namespace App\Filter;


use Illuminate\Http\Request;
use Jenssegers\Mongodb\Eloquent\Builder;

abstract class BaseQueryFilter
{

    protected $request;
    protected $builder;

    public function __construct(Request $request)
    {
        $this->request = $request;
    }

    public function apply(Builder $builder)
    {
        $this->builder = $builder;

        foreach ($this->filters() as $name => $value) {
            if ($value) {
                if (strstr($name,'_')){
                    $name_array = explode('_',$name);
                    $method = '';
                    foreach ($name_array as $key => $new_name) {
                        if ($key != 0) $new_name = ucfirst($new_name);
                        $method .= $new_name;
                    }
                    $name = $method;
                }
                if (method_exists($this, $name)) {
                    call_user_func_array([$this, $name], array_filter([$value]));
                }
            }

        }

        return $this->builder;
    }

    public function filters()
    {
        return $this->request->all();
    }
}
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

apply 过滤了前台传的空值，同时实现如pay_method -> payMethod 的转化，这段同学们可以酌情封装下哦

这次我们主要以过滤订单列表数据作为例子，创建OrderFilter继承BaseQueryFilter

OrderFilter:

/**
 * Created by PhpStorm.
 * User: SZJLS
 * Date: 2019/7/2
 * Time: 19:00
 */

namespace App\Filter;


use App\Models\Order;

class OrderFilter extends BaseQueryFilter
{

    public function search($search)
    {
        return $this->builder->where('user.email', 'like', "%{$search}%");
    }

    public function payMethod($pay_method)
    {
        $this->builder->where('status', '>=', Order::STATUS_SUBMITTED);
        switch ($pay_method) {
            case 1 ://escrow
                $this->builder->where('payment.method', 'escrow');
                break;
            case 3 ://transfer
                $this->builder->where('payment.method', 'transfer');
                break;
            case 5 ://paypal
                $this->builder->where('payment.method', 'paypal');
                break;
            case 7 ://stripe
                $this->builder->where('payment.method', 'stripe');
                break;
            case 9 ://paypal express
                $this->builder->where('payment.method', 'PayPal Express');
                break;
            case 10:
                $this->builder->where('payment.method', 'Advance Payment Balance');
                break;
        }
        return $this->builder;
    }

    ...
}
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

接下来就是应用了，在orderModel 里创建查询作用域，注入BaseQueryFilter　进行查询过滤

orderModel:

public function scopeFilter($query,BaseQueryFilter $filter) {
        return $filter->apply($query);
    }
1
2
3

OrderController:

    public function __construct(OrderRepository $repository)
    {
        $this->repo = $repository;
    }
    public function index(OrderFilter $filter)
    {
        $orders = $this->repo->getOrderList($filter);
        ...
       
        return view('new_admin.order.index', [
            'orders' => $orders,
            'order_types' => $order_types,
         ...
        ]);
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

OrderRepository:

 public function getOrderList(OrderFilter $filter)
    {
        $handle = Order::query();
        //默认显示已支付
        if (empty($filter->filters())) {
            $handle->where('payment.status', '>', Order::PAYMENT_UNPAID);
        } else {
            $handle->filter($filter);
        };

        return $handle->orderBy('created_at', 'desc')->paginate();


    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

完成

如上我们就应用scope 作用于对多条件查询进行了优化，使代码看起来更优雅

以上实现都是基于mongodb的，所以有些数据看起来不怎么合适