laravel 的联合查询重新整合包括排序(该方法支持php7.3以下不包括php7.3)

laravel 的联合查询重新整合包括排序(该方法支持php7.3以下不包括php7.3)

我算是做个笔记吧，笔记让我看源码确实难。
原因就是应为laravel的with()联合查询不支持我的列表查询(搜索条件是子表的那可能不行ps:是where条件是活得不是死的。死的我知道当然可以)
为啥支持php7.3以下呢应为 compact()这个函数不知道的自己百度去

第一步在model类里面加入代码(***/vendor/laravel/framework/src/llluminate/Datebase/Eloquent/Model.php)

/**
 * Get a new query builder that doesn't have any global scopes or eager loading.
 *
 * @return \Illuminate\Database\Eloquent\Builder|static
 */
public function newModelQuery()
{
    return $this->newEloquentBuilder(
        $this->newBaseQueryBuilder()
    )->setModel($this);
}

第二步创建俩个Trait文件

//FiltersTrait
<?php
/**
 * Created by PhpStorm.
 * User: 可怜的小白兔
 * Date: 2021/04/13
 * Time: 11:18
 */

namespace App\Repositories\Traits;


use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder;

trait FiltersTrait
{

    /**
     * @param Builder|Model $query
     * @param array         $filters [
     *                               ['columnName', 'value'], // 默认 and 查询 =
     *                               ['columnName', 'operate', 'value'], // 默认and 查询
     *                               ['and|or', 'columnName', 'operate', 'value'],
     *                               ['and|or', ['and|or', 'columnName', 'operate', 'value']],
     *                               ['and|or', ['and|or', ['and|or', 'columnName', 'operate', 'value']]],
     *                               ]
     *                               operate支持： = | <> | != | > | >= | < | <= | like | lLike | rLike | in | notIn | between | notBetween
     * @param  string       $boolean and|or
     *
     * @return \Illuminate\Database\Eloquent\Builder|Model
     */
    public function setFilters($query, $filters = [], $boolean = 'and')
    {

        if (!count($filters)) {
            return $query;
        }

        if ($query instanceof Model) {
            $query = $query->newModelQuery();
        }
        $query->where(function ($item_query) use ($filters) {

            if (count($filters) == count($filters, 1)) {
                $filters = [$filters];
            }

            foreach ($filters as $filter) {
                if (count($filter) === 2) {
                    if (!is_array($filter[1])) {
                        $temp_filters = explode(',', $filter[1]);
                        $item_query   = $this->setFilters($item_query, $temp_filters, $filter[0]);
                    } else {
                        $item_query = $this->setFilters($item_query, $filter[1], $filter[0]);
                    }
                } elseif (count($filter) === 2 || count($filter) === 3 || count($filter) === 4) {

                    switch (count($filter)) {
                        case 3:
                            // and | or
                            $filterType = 'and';

                            // field
                            $searchColumn         = $filter[0];
                            $searchColumnRelation = null;
                            if (stripos($filter[1], '.')) {
                                $explode              = explode('.', $filter[1]);
                                $searchColumn         = array_pop($explode);
                                $searchColumnRelation = implode('.', $explode);
                            }

                            // = | like | lLike | rLike | in | notIn | between
                            $searchType = $filter[1];

                            // 查询数据条件
                            $searchValue = $filter[2];
                            break;
                        case 4:

                            // and | or
                            $filterType = $filter[0] ? strtolower($filter[0]) : 'and';

                            // field
                            $searchColumn         = $filter[1];
                            $searchColumnRelation = null;
                            if (stripos($filter[1], '.')) {
                                $explode              = explode('.', $filter[1]);
                                $searchColumn         = array_pop($explode);
                                $searchColumnRelation = implode('.', $explode);
                            }

                            // = | like | lLike | rLike | in | notIn | between
                            $searchType = $filter[2];

                            // 查询数据条件
                            $searchValue = $filter[3];
                            break;
                        default:
                            // and | or
                            $filterType = 'and';

                            // field
                            $searchColumn         = $filter[0];
                            $searchColumnRelation = null;
                            if (stripos($filter[1], '.')) {
                                $explode              = explode('.', $filter[1]);
                                $searchColumn         = array_pop($explode);
                                $searchColumnRelation = implode('.', $explode);
                            }

                            // = | like | lLike | rLike | in | notIn | between
                            $searchType = '=';

                            // 查询数据条件
                            $searchValue = $filter[1];
                            break;
                    }

                    if (!is_null($searchValue)) {
                        if (!$searchColumnRelation) {
                            $this->modifyFilter($item_query, $searchType, $searchColumn, $searchValue, $filterType);
                        } else {
                            if($filterType == 'and') {
                                $item_query->whereHas($searchColumnRelation,
                                    function ($temp_query) use ($searchType, $searchColumn, $searchValue) {
                                        $this->modifyFilter($temp_query, $searchType, $searchColumn, $searchValue,
                                            'and');
                                    });
                            }else{
                                $item_query->orWhereHas($searchColumnRelation,
                                    function ($temp_query) use ($searchType, $searchColumn, $searchValue) {
                                        $this->modifyFilter($temp_query, $searchType, $searchColumn, $searchValue,
                                            'and');
                                    });
                            }

                        }
                    }


                } else {
                    continue;
                }
            }
        }, null, null, $boolean);

        return $query;
    }

    /**
     * @param Builder|Model $query
     * @param               $searchType
     * @param               $searchColumn
     * @param               $searchValue
     * @param               $filterType
     */
    private function modifyFilter(&$query, $searchType, $searchColumn, $searchValue, $filterType)
    {
        switch ($searchType) {
            case '=':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, '=', $searchValue, $filterType);
                break;
            case '<>':
            case '!=':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, '<>', $searchValue, $filterType);
                break;
            case '>':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, '>', $searchValue, $filterType);
                break;
            case '>=':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, '>=', $searchValue, $filterType);
                break;
            case '<':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, '<', $searchValue, $filterType);
                break;
            case '<=':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, '<=', $searchValue, $filterType);
                break;
            case 'like':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, 'like', '%' . $searchValue . '%', $filterType);
                break;
            case 'lLike':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, 'like', '%' . $searchValue, $filterType);
                break;
            case 'rLike':
                $searchValue = is_null($searchValue) ? '' : (string)$searchValue;
                $query->where($searchColumn, 'like', $searchValue . '%', $filterType);
                break;
            case 'in':
                $searchValue = is_array($searchValue) ? $searchValue : explode(',', $searchValue);
                $query->whereIn($searchColumn, $searchValue, $filterType);
                break;
            case 'notIn':
                $searchValue = is_array($searchValue) ? $searchValue : explode(',', $searchValue);
                $query->whereNotIn($searchColumn, $searchValue, $filterType);
                break;
            case 'between':
                $searchValue = is_array($searchValue) ? $searchValue : explode(',', $searchValue);
                $query->whereBetween($searchColumn, $searchValue, $filterType);
                break;
            case 'notBetween':
                $searchValue = is_array($searchValue) ? $searchValue : explode(',', $searchValue);
                $query->whereNotBetween($searchColumn, $searchValue, $filterType);
                break;
        }
    }
}

//SortsTrait

<?php
/**
 * Created by PhpStorm.
 * User: 可怜的小白兔
 * Date: 2021/04/13
 * Time: 12:03
 */

namespace App\Repositories\Traits;


use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder;

trait SortsTrait
{

    /**
     * @param Builder|Model $query
     * @param array         $orders
     *
     * @return \Illuminate\Database\Eloquent\Builder|Model
     */
    public function setOrderBy($query, $orders = [])
    {

        if(!count($orders)){
            return $query;
        }

        if (count($orders) == count($orders, 1)) {
            $orders = [$orders];
        }

        foreach ($orders as $order) {
            if (count($order) > 1) {
                if (!in_array(strtolower($order[1]), ['asc', 'desc'])) {
                    $order[1] = 'asc';
                }
            } else {
                $order[0] = $order[0] ?? 'id';
                $order[1] = 'asc';
            }

            list($orderBy, $sortBy) = $order;

            $query->orderBy($orderBy, $sortBy);

        }

        return $query;
    }

}

第三步如何使用举个栗子

//首先在model文件里面直接创建一个关联关系

   /** 
     * @return \Illuminate\Database\Eloquent\Relations\BelongsTo
     */
    public function user()
    {
        return $this->belongsTo(AppletUser::class, 'applet_user_id', 'id');
    }

//好了在控制器里面直接接受参数
 $filters[] = [
                'and',
                [
                    ['or', 'user.nickname', 'like', $search_name],
                    ['or', 'user.student.real_name', 'like', $search_name]
                ]
            ];
         //排序
        $orders = ['created_at', 'desc'];

//接下来就是去service层
use FiltersTrait;
use SortsTrait;
/**
     * @param array $filters
     * @param array $orders
     * @param int   $pageSize
     * @param array $columns
     *
     * @return array|mixed
     */
    public function getPaginate($filters = [], $orders = [], $pageSize = 15, $columns = ['*'])
    {
        $query = AppletUserAccount::with([
            'user',
            'user.student'   => function ($query) {
                $query->select('id', 'real_name');
            }
        ]);

        if (count($filters)) {
            $query = $this->setFilters($query, $filters);
        }

        if (count($orders)) {
            $query = $this->setOrderBy($query, $orders);
        }

        $lists = $query->paginate($pageSize, $columns);


        return $this->baseSucceed($lists);
    }

好了完毕。我知道模型关联确实查询速度慢。用join还凑合但是我就是想要美观你说气不气。