laravel版本DB,EloquentORM不固定条件查询封装

支持DB与ORM的来构建查询。
DB构建sql语句已在多个项目中使用，[ORM方式不兼容，需要对 $this->builder赋值操作。需要修改方法里$this->builder赋值操作，如 self::where($this->builder, $where); 需改成 $this->builder = self::where($this->builder, $where); 如有需要自行修改与测试]

where条件构建说明

参数： [“字段名”,“操作符”,“查询值”,“与前一个条件的关系[默认and]”] 与db的where方法一致

1.如果是等于，可以省略"操作符" :
[“username”, “chen”] 或 [“username”,"=" , “chen”]

2.大于:
[“createtime”, “>”, “2019-1-1”]

3.如果为or，那就得一写全：
[“username”, “=”, “chen”, “or”]

4.其它的where兼容db的where方法

测试

1.and与or混合条件

// where与orderby是一个二维数组
$wehre = [['yearid', 2010], [['price', '>', 0], ['issticky', '=', 1, 'or']]];
$column = ['id', 'name'];
$bll->list($where,$column,[['sort', 'desc'], ['id', 'asc']]);
// select id,name form test where yearid=2010 and (price>0 or issticky=1) order by sort desc,id asc

2.ORM方法测试

$builder = new Builder(User::class);
$datas = $builder->get([['id', 1]]);
// 加载关系表数据
$datas->load(['userCards.userCardPhotos', 'userCards.userCardTags']);

!!! 别的测试方法就不写了，与Laravel的db api一致，只是在构建了一个where方法

主要代码

<?php
namespace Extend\Query;

use Illuminate\Database\Query\Builder as dbBuilder;
use \Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class Builder
{
    /**
     * MySql的构造器
     * @var dbBuilder|Model
     */
    protected $builder;

    public function __construct($table)
    {
        // orm方式不兼容，需要修改方法里$this->builder赋值操作，如  self::where($this->builder, $where); 需改成  $this->builder = self::where($this->builder, $where);
        // $this->builder = is_string($table) && strpos($table, '\\') === false ? DB::table($table) : ($table instanceof Model ? $table : new $table);
        $this->builder = DB::table($table) ;
    }

    /**
     * 根据表id获取数据
     * @param  $id
     * @param array $column
     * @return mixed
     * @author: qic
     */
    public function getById($id, $column = null)
    {
        $where = ['id' => $id];
        if (!empty($column)) {
            $column = self::getColumn($column);
            $this->builder->select($column);
        }
        $model = $this->builder->where($where)->first();
        return $model;
    }

    /**
     * 根据表id获取数据
     * @param array $ids
     * @param null $column
     * @return array
     * @author: qic
     */
    public function getByIds(array $ids, $column = null)
    {
        if (!empty($column)) {
            $column = self::getColumn($column);
            $this->builder->select($column);
        }
        $list = $this->builder->whereIn('id', $ids)->get();
        return $list;
    }

    /**
     * 获取表信息内容
     * @param array $where 条件
     * @param array $column 获取的列名
     * @param array $orderby 排序
     * @param int $page 当前页
     * @param int $pagerows 每页记录数
     * @param null $total 返回记录总数 null不计算返回
     * @return Model|dbBuilder|\Illuminate\Support\Collection|string
     */
    public function get(array $where, $column = null, $orderby = null, int $page = 1, int $pagerows = 10, &$total = null)
    {
        if (!empty($column)) {
            $column = self::getColumn($column);
            $this->builder->select($column);
        }
        self::where($this->builder, $where);
        if ($total !== null) {
            $total = $this->builder->count();
            if ($total == 0) {
                return $this->builder;
            }
        }
        if ($page > 0 && $pagerows > 0) {
            $this->builder->skip(($page - 1) * $pagerows)->take($pagerows);
        }
        if (!empty($orderby)) {
            foreach ($orderby as $item) {
                if (is_array($item)) {
                    $this->builder->orderBy(...$item);
                } elseif (is_string($item)) {
                    $this->builder->orderByRaw($item);
                }
            }
        }

        return $this->builder->get();
    }

    /**
     * 获取总数
     * @param array $where
     * @return int
     */
    public function count(array $where)
    {
        self::where($this->builder, $where);
        return $this->builder->count();
    }

    /**
     * 获取一条数据
     * @param array $where
     * @param null $column
     * @return array
     * @author: qic
     */
    public function getFirst(array $where, $column = null, $orderby = null)
    {
        if (!empty($column)) {
            $column = self::getColumn($column);
            $this->builder->select($column);
        }
        self::where($this->builder, $where);
        if (!empty($orderby)) {
            foreach ($orderby as $item) {
                $this->builder->orderBy(...$item);
            }
        }

        return $this->builder->first();
    }

    /**
     * @param $builder
     * @param $where
     * @return dbBuilder
     * @author: qic
     */
    public static function where($builder, $where)
    {
        if (count($where)) {
            foreach ($where as $item) {
                //一维数组
                if (!is_array($item[0])) {
                    $op = '=';
                    if (count($item) > 2)
                        $op = str_replace(' ', '', strtolower($item[1]));
                    switch ($op) {
                        case 'in':
                            unset($item[1]);
                            $builder->whereIn(...$item);
                            break;
                        case 'notin':
                            unset($item[1]);
                            $builder->whereNotIn(...$item);
                            break;
                        default:
                            $builder->where(...$item);
                            break;
                    }
                } else {
                    //多维数组
                    $builder->where(function ($query) use ($item) {
                        self::where($query, $item);
                    });
                }
            }
        }
        return $builder;
    }

    /**
     * 获取查询的列
     * @param $column
     * @return array|null
     * @author: qic
     */
    protected static function getColumn($column)
    {
        $cols = [];
        if (is_array($column)) {
            $cols = $column;
        } else if (is_string($column)) {
            $cols = explode(',', $column);
        }
        return $cols;
    }
}