laravel 5.8 分表及分页查询处理

# laravel 5.8 分表及分页查询处理

## trait类

<?php


namespace App\Libraries\Split;


use App\Exceptions\ResponseApiException;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

trait SplitTableTrait
{
    //是否分表，默认false，即不分表
    public $isSplitTable = false;

    //原表
    public $originTable;

    //表
    public $endTable;

    /**
     * 后缀参数
     * @var string
     */
    protected $suffix = null;

    /**
     * 年月参数：202104
     * @var string
     */
    public $ym;

    public function init(array $attributes = [], $suffix = null)
    {
        //默认原表
        $this->originTable = $this->table;
        //默认最终表
        $this->endTable = $this->table;

        $this->ym = Carbon::now()->format('Ym');

        //isSplitTable参数为true时进行分表，否则不分表
        if ($this->isSplitTable) {
            //初始化后缀，未传则默认年月分表
            $this->suffix = $suffix ?: $this->ym;
        }

        //初始化分表表名并创建
        $this->setSuffix();
    }

    /**
     * 设置表后缀, 如果设置分表后缀，可在service层调用生成自定义后缀表名，
     * 但每次操作表之前都需要调用该方法以保证数据表的准确性
     * @param $suffix
     */
    public function setSuffix($suffix = null)
    {
        //isSplitTable参数为true时进行分表，否则不分表
        if ($this->isSplitTable) {
            //初始化后缀，未传则默认年月分表
            $this->suffix = $suffix ?: $this->ym;
        }

        if ($this->suffix !== null) {
            //$this->endTable = $this->getTable() . '_' . $suffix;
            $this->endTable = $this->originTable . '_' . $this->suffix;

            //最终表替换模型中声明的表作为分表使用的表
            $this->table = $this->endTable;
        }

        //调用时，创建分表，格式为 table_{$suffix}
        //未传自定义后缀情况下,，默认按年月分表格式为：b_log_202101
        //无论使用时是否自定义分表名，都会创建默认的分表，除非关闭该调用
        $this->createTable();
    }

    /**
     * 提供一个静态方法设置表后缀
     * @param string $suffix
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public static function suffix($suffix = null)
    {
        $instance = new static;
        $instance->setSuffix($suffix);

        return $instance->newQuery();
    }

    /**
     * 创建新的"table_{$suffix}"的模型实例并返回
     * @param array $attributes
     * @param bool $exists
     * @return object $model
     */
    public function newInstance($attributes = [], $exists = false)
    {
        $model = $this::newInstance($attributes, $exists);
        $model->setSuffix($this->suffix);

        return $model;
    }

    /**
     * 创建分表，没有则创建，有则不处理
     */
    protected function createTable()
    {
        //初始化分表,，按年月分表格式为：b_log_202101
        if (!Schema::hasTable($this->endTable)) {
            DB::update("create table {$this->endTable} like {$this->originTable}");
        }
    }


    
    /**
     * 默认日期参数字段
     * @var null
     */
    protected $defaultDateField = null;

    /**
     * 开始时间
     * @var
     */
    protected $startTime;

    /**
     * 结束时间
     * @var
     */
    protected $endTime;

    /**
     * 默认查询天数
     * @var int
     */
    protected $defaultDays = 30;

    /**
     * 处理时间参数处
     * @param array $params
     * @throws ResponseApiException
     */
    protected function dealDateParam(array $params)
    {
        //默认只查当前年月的数据
        $this->startTime = isset($params['start_time']) && $params['start_time']
            ? Carbon::parse($params['start_time'])->toDateTimeString() : false;
        $this->endTime = isset($params['end_time']) && $params['end_time']
            ? Carbon::parse($params['end_time'])->toDateTimeString() : false;
        if ($this->defaultDateField && isset($params[$this->defaultDateField]) && $params[$this->defaultDateField]) {
            $this->startTime = Carbon::parse($params[$this->defaultDateField])->toDateTimeString();
            $this->endTime = Carbon::parse($params[$this->defaultDateField])->addDay()->subSecond()->toDateTimeString();
        }
        if (!$this->startTime && !$this->endTime) {
            $this->startTime = Carbon::today()->firstOfMonth()->toDateTimeString();
            $this->endTime = Carbon::now()->toDateTimeString();
        } elseif(!$this->startTime && $this->endTime) {
            $this->startTime = Carbon::parse($this->endTime)->subDays($this->defaultDays)->toDateTimeString();
        } elseif ($this->startTime && !$this->endTime) {
            $this->endTime = Carbon::parse($this->startTime)->addDays($this->defaultDays)->toDateTimeString();
        }

        //限制只能查30天的数据
        if (Carbon::parse($this->startTime)->addDays($this->defaultDays)->toDateTime() < Carbon::parse($this->endTime)->toDateTime()) {
            throw new ResponseApiException('public_log_model_date_time_rage_over_limit');
        }
    }

    /**
     * 排序字段
     * @var
     */
    protected $orderByField = null;

    /**
     * 排序类型，asc：正序，desc：倒序，默认倒序
     * @var
     */
    protected $orderBy = 'desc';

    /**
     * 执行union all对分表的最终扥分页查询
     * @param $queries
     * @return array
     */
    protected function dealListByUnionAllQuery($queries)
    {
        //弹出一张表作为union的开始
        $unionQuery = $queries->shift();
        //循环剩下的表添加union
        $queries->each(function ($item, $key) use ($unionQuery) {
            $unionQuery->unionAll($item);
        });
        //设置临时表的名称，添加临时表，顺序不能反过来，否则用关联约束会找不到表
        $endQuery =
            //with($this)->setTable('union_'.$this->originTable)
            // 添加临时表
            //->from(DB::raw("({$unionQuery->toSql()}) as union_".$this->originTable))
            DB::table(DB::raw("({$unionQuery->toSql()}) as union_".$this->originTable))
                //合并查询条件
                ->mergeBindings($unionQuery);
        if ($this->orderByField) {
            $endQuery->orderBy($this->orderByField, $this->orderBy);
        }
        $lists = $endQuery
                //分页
                ->paginate(pageSize())
                ->toArray();
        //处理分页数据
        $list = dealPaginate($lists);
        return $list;
    }

    /**
     * 获取分表中列表数据（通过union实现），以日志表为例，使用时在模型内声明同名方法并处理
     * @param array $params
     * @return array
     * @throws ResponseApiException
     */
    public function pubListByDefaultTableData(array $params = [])
    {
        //默认只查当前年月的数据
        $startTime = isset($params['start_time']) && $params['start_time']
            ? Carbon::parse($params['start_time'])->toDateTimeString() : false;
        $endTime = isset($params['end_time']) && $params['end_time']
            ? Carbon::parse($params['end_time'])->toDateTimeString() : false;
        if (isset($params['day_date']) && $params['day_date']) {
            $startTime = Carbon::parse($params['day_date'])->toDateTimeString();
            $endTime = Carbon::parse($params['day_date'])->addDay()->subSecond()->toDateTimeString();
        }
        if (!$startTime && !$endTime) {
            $startTime = Carbon::today()->firstOfMonth()->toDateTimeString();
            $endTime = Carbon::now()->toDateTimeString();
        } elseif(!$startTime && $endTime) {
            $startTime = Carbon::parse($endTime)->subDays(30)->toDateTimeString();
        } elseif ($startTime && !$endTime) {
            $endTime = Carbon::parse($startTime)->addDays(30)->toDateTimeString();
        }

        //限制只能查30天的数据
        if (Carbon::parse($startTime)->addDays(30)->toDateTime() < Carbon::parse($endTime)->toDateTime()) {
            throw new ResponseApiException('public_log_model_date_time_rage_over_limit');
        }

        $projectIds = array_filter(explode(',', $params['project_ids'] ?? '')) ?: false;
        $truckNo = isset($params['truck_no']) && $params['truck_no'] ? $params['truck_no'] : false;
        $equipNo = isset($params['equip_no']) && $params['equip_no'] ? $params['equip_no'] : false;
        $info = isset($params['search_word']) && $params['search_word'] ? $params['search_word'] : false;

        // 查询集合
        $queries = collect();
        // 循环比较年月，添加每一张表的查询
        $start = Carbon::parse($startTime);
        $end = Carbon::parse($endTime);
        $fields = ['log_id', 'project_id', 'truck_no', 'equip_no', 'info', 'date', 'created_at'];
        for ($i = $start->copy(); $i->format('Ym') <= $end->format('Ym'); $i->addMonth()) {
            //根据是否分表，确定查询真正表
            if ($this->isSplitTable) {
                $curTable = "{$this->originTable}_{$i->format('Ym')}";
            } else {
                $curTable = $this->originTable;
            }
            //如果表不存在则跳过本次循环
            if (!Schema::hasTable($curTable)) {
                continue;
            }
            $queries->push(
                DB::table($curTable)
                    // 建议都用select查询字段，SQL尽可能的优化性能
                    ->select($fields)
                    ->when($projectIds, function ($query) use($projectIds) {
                        $query->whereIn('project_id', $projectIds);
                    })
                    ->when($truckNo, function ($query) use($truckNo) {
                        $query->where('truck_no', $truckNo);
                    })
                    ->when($equipNo, function ($query) use($equipNo) {
                        $query->where('equip_no', $equipNo);
                    })
                    ->when($info, function ($query) use($info) {
                        $query->where('info', 'like', "%$info%");
                    })
                    ->where('date', '>=', $startTime)
                    ->where('date', '<=', $endTime)
            );
        }

        //弹出一张表作为union的开始
        $unionQuery = $queries->shift();
        //循环剩下的表添加union
        $queries->each(function ($item, $key) use ($unionQuery) {
            $unionQuery->unionAll($item);
        });
        //设置临时表的名称，添加临时表，顺序不能反过来，否则用关联约束会找不到表
        $lists =
            //with($this)->setTable('union_'.$this->originTable)
            // 添加临时表
            //->from(DB::raw("({$unionQuery->toSql()}) as union_".$this->originTable))
            DB::table(DB::raw("({$unionQuery->toSql()}) as union_".$this->originTable))
            //合并查询条件
            ->mergeBindings($unionQuery)
            //按上报时间倒序
            ->orderBy('date', 'desc')
            //分页
            ->paginate(pageSize())
            ->toArray();
        //处理分页数据
        $list = dealPaginate($lists);
        return $list;
    }

    public function pubListByCustomTableData(array $params = [])
    {
        //默认只查近30天的数据
    }
}

该trait类已在通用模型中use，如果使用时没有通用模型，可以直接在模型中use引入。

## 使用：

<?php

namespace App\Models;

use App\Exceptions\ResponseApiException;
use App\Models\Common\PublicModel;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class Log extends PublicModel
{
    //是否分表，默认false，即不分表
    public $isSplitTable = true;

    //表名
    protected $table = 'b_log';

    //主键
    protected $primaryKey = 'log_id';

    protected $columns;

    const CREATED_AT = 'created';

    const UPDATED_AT = false;

    //创建白名单
    protected $fillable = ['log_id'];

    //默认过滤器
    public $filter = [];

    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);

        //初始化分表处理
        $this->init();

        //初始化表字段
        $this->columns = array_column(objectToArray(DB::select('show columns from '.$this->table)), 'Field');
        $this->fillable = array_values($this->columns);
    }

    /**
     * 获取分表中列表数据（通过union实现）
     * @param array $params
     * @return array
     * @throws ResponseApiException
     */
    public function listMod(array $params = [])
    {
        //处理时间参数
        $this->defaultDateField = 'created';
        $this->dealDateParam($params);

        $userId = $params['token_info']['user_id'] ?? false;
        $logSort = $params['log_sort'] ?? false;
        $method = $params['method'] ?? false;

        // 查询集合
        $queries = collect();
        // 循环比较年月，添加每一张表的查询
        $start = Carbon::parse($this->startTime);
        $end = Carbon::parse($this->endTime);
        $fields = ['log_id', 'user_id', 'user_name', 'path', 'method', 'ip', 'created', 'log_sort', 'content'];
        for ($i = $start->copy(); $i->format('Ym') <= $end->format('Ym'); $i->addMonth()) {
            //根据是否分表，确定查询真正表
            if ($this->isSplitTable) {
                $curTable = "{$this->originTable}_{$i->format('Ym')}";
            } else {
                $curTable = $this->originTable;
            }
            //如果表不存在则跳过本次循环
            if (!Schema::hasTable($curTable)) {
                continue;
            }
            $queries->push(
                DB::table($curTable)
                    // 建议都用select查询字段，SQL尽可能的优化性能
                    ->select($fields)
                    ->when($logSort, function ($query) use($logSort) {
                        $query->where('log_sort', $logSort);
                    })
                    ->when($method, function ($query) use($method) {
                        $query->where('method', $method);
                    })
                    ->where('created', '>=', $this->startTime)
                    ->where('created', '<=', $this->endTime)
            );
        }

        //执行union all查询
        $this->orderByField = 'created';
        $list = $this->dealListByUnionAllQuery($queries);
        return $list;
    }
}

- 模型中定义参数 isSplitTable，为true时分表，为false时不分表

- 如果设置分表，模型构造函数中调用：$this->init(); 进行分表初始化

 

****************************只要思想不滑坡，办法总比困难多****************************