支持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;
}
}
go语言gorm版本不定条件查询数据封装
https://www.jianshu.com/p/e59cc4f8c4ea
https://github.com/qicmsg/go_vcard
