封装sql链式操作

前言

用习惯了tp这种链式操作框架，因为项目原因不能使用tp这种框架，只能自己手动拼接sql,这种对于不愿用点号一直拼的人很是恼火，于是本人封装了一个简单的链式操作，来获取sql，然后用pdo直接执行sql语句获取结果。

代码说明

里面运用的基本是面向对象的思想，用到了一个PHP不算常用的场景，反射，大大的节省了代码量。自己亲手封装，仅供参考，代码中没有对于注入进行处理，需要的小伙伴可以自己进行二次改进。

class DbSql
{
    //属性声明顺序不可变化,否则影响sql语句中关键字的顺序
    private $table;//表名
    public $child;//子查询
    private $join;//连接
    private $where;//条件
    private $between;//between and
    private $group;//分组
    private $having;//分组后操作
    private $order;//排序
    private $field;//字段,默认为*
    private $limit;//限制
    //使用call
    public function __call($name, $arg)
    {
        if ($name == 'where') {
            $this->$name[] = $arg[0];
        } elseif ($name == 'join') {
            $this->$name[] = $arg;
        } else {
            $this->$name = $arg[0];
        }
        return $this;
    }
    //最终生成sql语句
    public function sql()
    {
        $ref = new ReflectionClass($this);
        $sql = '';
        //方法名称映射
        $map = [
            'order' => ' order by ',
            'group' => ' group by ',
            'table' => ' select ',
            'child' => ' select ',
        ];
        foreach ($ref->getProperties() as $pro) {
            $pro_name = $pro->name;//属性名称
            $this_attr = $this->$pro_name;//对应的属性具体值
            if (!empty($this_attr)) {
                $arr = [isset($map[$pro_name]) ? $map[$pro_name] : $pro_name];//初始化行为
                if ($pro_name == 'table') {
                    $arr = array_merge($arr, [$this->field == '' ? '*' : $this->field, 'from', $this->table]);
                } elseif ($pro_name == 'child') {
                    $child_sql = substr($this_attr, 0, strripos($this_attr, ' '));
                    $alias = substr($this_attr, strripos($this_attr, ' '));
                    $arr = array_merge($arr, [$this->field == '' ? '*' : $this->field], ['from ( ', $child_sql, ' ) ', $alias]);
                } elseif ($pro_name == 'join') {
                    $arr = [$this->buildJoin()];
                } elseif ($pro_name == 'where') {
                    $arr[] = rtrim($this->buildWhere(), ' and');
                } elseif ($pro_name == 'between') {
                    $between = explode(',', $this_attr);
                    $arr = [' and ' . $between[0], 'between', $between[1] . ',', $between[2]];
                } else {
                    $arr[] = $this_attr;
                }
                $this->$pro_name = null;
                $sql .= ' ' . implode(' ', $arr);
            }
        }
        return $sql;
    }

    //处理where条件
    private function buildWhere()
    {
        $sql = "";
        if (is_array($this->where)) {
            foreach ($this->where as $k => $v) {
                //关联数组
                if ($this->array_depth($v) == 1) {
                    foreach ($v as $kk => $vv) {
                        //纯字符串
                        if (is_numeric($kk)) {
                            $sql .= "'{$vv}' and ";
                        } else {
                            $sql .= "$kk = '{$vv}' and ";
                        }
                    }
                } elseif ($this->array_depth($v) == 2) {
                    foreach ($v as $kkk => $vvv) {
                        if (is_array($vvv)) {
                            $sql .= " $kkk {$vvv[0]} '{$vvv[1]}' and ";
                        }
                    }
                }
            }
        }
        return $sql;
    }

    //处理join条件
    private function buildJoin()
    {
        $sql = "";
        if (is_array($this->join)) {
            foreach ($this->join as $k => $v) {
                if (!isset($v[2])) {
                    $joinType = ' inner join ';
                } else {
                    $joinType = $v[2] . ' join ';
                }
                $sql .= $joinType . $v[0] . ' on ' . $v[1] . ' ';
            }
        }
        return $sql;
    }

    //获取数组维度主要用于判断where
    function array_depth($array)
    {
        if (!is_array($array)) return 0;
        $max_depth = 1;
        foreach ($array as $value) {
            if (is_array($value)) {
                $depth = $this->array_depth($value) + 1;
                if ($depth > $max_depth) {
                    $max_depth = $depth;
                }
            }
        }
        return $max_depth;
    }
}

运行实例

$where = [];
$where['name'] = ['like', '%1111%'];
$db = new DbSql();
$sql = $db->table('a')
    ->where(['id' => 1])
    ->where($where)
    ->between('ass,1,2')
    ->order('id desc')
    ->group('a.id')
    ->join('user u', 'a.id=o.id', 'left')
    ->join('user11 u1', 'a1.id=o1.id', 'left')
    ->limit('1,2')
    ->sql();

打印$sql
select * from a left join user u on a.id=o.id left join user11 u1 on a1.id=o1.id where id = '1' and name like '%1111%' and ass between 1, 2 group by a.id order by id desc limit 1,2