Qin Blog

写一个“特殊”的查询构造器 - (四、条件查询：复杂条件)

2018-05-16

分类 : PHP

复杂的条件

在 SQL 的条件查询中，不只有 where、or where 这些基本的子句，还有 where in、where exists、where between 等复杂一些的子句。而且即使是 where 这种基础的子句，也有多个条件的多种逻辑组合。这篇我们就来讲一下查询构造器如何构造这些复杂的查询语句。

where 系列

where in 子句

我们回想一下使用 where in 子句的 SQL 是什么样的：

-- 从一个数据范围获取
SELECT * FROM test_table WHERE age IN (18, 20, 22, 24);
-- 从一个子查询获取
SELECT * FROM test_table WHERE username IN (SELECT username FROM test_name_table);

从一个子查询获取的模式有些复杂我们稍后再说，先分析下从数据范围获取的方式。

where in 子句判断字段是否属于一个数据集合，有 where in、where not in、or where in、or where not in 四种模式。我们只需构造好这个数据集合，并对集合中的数据进行数据绑定即可。

基类中添加 whereIn() 方法:

// $field where in 要查的字段
// $data  进行判断的数据集合
// $condition in、not in 模式
// $operator AND、OR 分隔符
public function whereIn($field, array $data, $condition = 'IN', $operator = 'AND')
{
    // 判断模式和分隔符是否合法
    if( ! in_array($condition, ['IN', 'NOT IN']) || ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Error whereIn mode");
    }
    // 生成占位符，绑定数据
    foreach ($data as $key => $value) {
        $plh = self::_getPlh();
        $data[$key] = $plh;
        $this->_bind_params[$plh] = $value;
    }
    // 第一次调用该方法，需要 WHERE 关键字
    if($this->_where_str == '') {
        $this->_where_str = ' WHERE '.self::_wrapRow($field).' '.$condition.' ('.implode(',', $data).')';
    } else { // 非初次调用，使用分隔符连接
        $this->_where_str .= ' '.$operator.' '.self::_wrapRow($field).' '.$condition.' ('.implode(',', $data).')';
    }
    // 方便链式调用，返回当前实例
    return $this;
}

关于上述代码，由于 where in、where not in、or where in、or where not in 这写方法的区别只是关键字的区别，对于字符串来说只需替换关键字即可。所以对于这些方法，为了方便，我们把这些模式的关键字作为方法的参数传入，可以提高代码的重用性。

那么，另外三种模式的代码可以这么写：

public function orWhereIn($field, array $data)
{
    return $this->whereIn($field, $data, 'IN', 'OR');
}

public function whereNotIn($field, array $data)
{
    return $this->whereIn($field, $data, 'NOT IN', 'AND');
}

public function orWhereNotIn($field, array $data)
{
    return $this->whereIn($field, $data, 'NOT IN', 'OR');
}

构造测试

$driver->table('test_table')
       ->whereIn('age', [18, 20, 22, 24])
       ->get();

$driver->table('test_table')
       ->Where('age', '!=', 12)
       ->orWhereNotIn('age', [13, 23, 26, 25])
       ->get();

where between 子句

where between 子句的构造和 where in 相差无几，只有语法上的区别，而且只有 where between and、or where between and 两种模式。

whereBetween 系列方法代码：

public function whereBetween($field, $start, $end, $operator = 'AND')
{
    // 检测模式是否合法
    if( ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Logical operator");
    }
    // 生成占位符，绑定数据
    $start_plh = self::_getPlh();
    $end_plh = self::_getPlh();
    $this->_bind_params[$start_plh] = $start;
    $this->_bind_params[$end_plh] = $end;

    // 是否初次访问？
    if($this->_where_str == '') {
        $this->_where_str = ' WHERE '.self::_wrapRow($field).' BETWEEN '.$start_plh.' AND '.$end_plh;
    } else {
        $this->_where_str .= ' '.$operator.' '.self::_wrapRow($field).' BETWEEN '.$start_plh.' AND '.$end_plh;
    }

    return $this;
}

public function orWhereBetween($field, $start, $end)
{
    return $this->whereBetween($field, $start, $end, 'OR');
}

where null 子句

前面的 where 子句中使用单条件模式数据为 NULL 时则进行 IS NULL 的判断。但是我们想要一个更灵活、语义更清晰的接口，所以这里为 NULL 的判断单独编写方法。

where null 系列代码：

public function whereNull($field, $condition = 'NULL', $operator = 'AND')
{
    if( ! in_array($condition, ['NULL', 'NOT NULL']) || ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Logical operator");
    }
    if($this->_where_str == '') {
        $this->_where_str = ' WHERE ';
    } else {
        $this->_where_str .= ' '.$operator.' ';
    }

    $this->_where_str .= self::_wrapRow($field).' IS '.$condition.' ';

    return $this;
}

public function whereNotNull($field)
{
    return $this->whereNull($field, 'NOT NULL', 'AND');
}

public function orWhereNull($field)
{
    return $this->whereNull($field, 'NULL', 'OR');
}

public function orWhereNotNull($field)
{
    return $this->whereNull($field, 'NOT NULL', 'OR');
}

where exists

到 where exists 子句时，构造就有些难度了。我们回忆一下使用 where exists 子句的 SQL：

SELECT * FROM table1 where exists (SELECT * FROM table2);

没错，和之前构造的语句不同，where exists 子句存在子查询。之前的 sql 构造都是通过 _buildQuery() 方法按照一定的顺序构造的，那么如何对子查询进行构造呢？子查询中的 where 子句和外层查询的 where 子句同时存在时，又该怎么区分呢？

首先，观察一下有子查询的 SQL，可以看出：子查询是一个独立的查询语句。

那么，能不能将子查询语句和外层查询语句各自单独构造，然后再组合到一起成为一条完整的 SQL 呢？

当然是可以的。不过，如何去单独构造子查询语句呢？如果子查询中还有子查询语句呢？

我们先看下 laravel 中的 where exists 构造语句是什么样的【1】：

DB::table('users')
            ->whereExists(function ($query) {
                $query->select(DB::raw(1))
                      ->from('orders')
                      ->whereRaw('orders.user_id = users.id');
            })
            ->get();

laravel 查询构造器的 whereExists() 方法接受一个闭包，闭包接收一个查询构造器实例，用于在闭包中构造子句。

使用闭包的好处是：

给接受闭包参数的函数扩展功能 (进行子查询语句构造)
闭包传入函数中，函数可以控制这个闭包的执行方式，在闭包的执行前后可以做相应操作 (现场保护、恢复)

基本结构

所以参考 laravel，我们也使用传入闭包的方式，我们先确定一下 whereExists() 方法的基本结构：

// $callback 闭包参数
// $condition exists、not exists 模式
// $operator and、or 模式
public function whereExists(Closure $callback, $condition = 'EXISTS', $operator = 'AND')
{
    // 判断模式是否合法
    if( ! in_array($condition, ['EXISTS', 'NOT EXISTS']) || ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Error whereExists mode");
    }
    // 初次调用？
    if($this->_where_str == '') {
        $this->_where_str = ' WHERE '.$condition.' ( ';
    } else {
        $this->_where_str .= ' '.$operator.' '.$condition.' ( ';
    }

    // 进行现场保护
    ...
    // 闭包调用，传入当前实例
    ...
    // 现场恢复
    ...

    // 返回当前实例
    return $this;
}

因为使用到了 Closure 限制参数类型，要在基类文件的顶部加上：

use Closure;

现场的保护和恢复

上面一直再说现场的保护和恢复，那么我们保护、恢复的这个现场是什么呢？

我们先理一下构造一个普通的 SQL 的步骤：依次构造各个查询子句、使用 _buildQuery() 方法将这些子句按照固定顺序组合成 SQL。

那么在有子查询的过程中，意味着这样的步骤要经过两次，但是由于要传入当前实例 (另外新建实例的话会创建新连接)，第二次查询构造会覆盖掉第一次构造的结果。所以，我们这里的现场就是这些构造用的子句字符串。

有了现场的保护和恢复，即使在闭包中调用闭包 (即子查询中嵌套子查询) 的情形下也能正确的构造需要的 SQL 语句。(有没有觉得很像递归呢？的确这里是借鉴了栈的使用思路。)

首先我们需要一个保存构造字符串名称的数组 (用来获取构造字符串属性)，在基类添加属性 _buildAttrs：

// 这里保存了需要保护现场的构造字符串名称
protected $_buildAttrs = [
    '_table',
    '_prepare_sql',
    '_cols_str',
    '_where_str',
    '_orderby_str',
    '_groupby_str',
    '_having_str',
    '_join_str',
    '_limit_str',
];

然后，添加保护现场和恢复现场的方法：

// 保护现场
protected function _storeBuildAttr()
{
    $store = [];
    // 将实例的相关属性保存到 $store，并返回
    foreach ($this->_buildAttrs as $buildAttr) {
        $store[$buildAttr] = $this->$buildAttr;
    }

    return $store;
}
//恢复现场
protected function _reStoreBuildAttr(array $data)
{
    // 从 $data 取数据恢复当前实例的属性
    foreach ($this->_buildAttrs as $buildAttr) {
        $this->$buildAttr = $data[$buildAttr];
    }
}

当然，保护了现场后，子查询要使用实例的属性时需要的是一个初始状态的属性，所以我们还需要一个可以重置这些构造字符串的方法：

protected function _resetBuildAttr()
{
    $this->_table = '';
    $this->_prepare_sql = '';
    $this->_cols_str = ' * ';
    $this->_where_str = '';
    $this->_orderby_str = '';
    $this->_groupby_str = '';
    $this->_having_str = '';
    $this->_join_str = '';
    $this->_limit_str = '';
}

完成 whereExists()

有了保护、恢复现场的方法，我们继续完成 whereExists() 方法：

public function whereExists(Closure $callback, $condition = 'EXISTS', $operator = 'AND')
{
    if( ! in_array($condition, ['EXISTS', 'NOT EXISTS']) || ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Error whereExists mode");
    }

    if($this->_where_str == '') {
        $this->_where_str = ' WHERE '.$condition.' ( ';
    } else {
        $this->_where_str .= ' '.$operator.' '.$condition.' ( ';
    }

    // 保护现场，将构造字符串属性都保存起来
    $store = $this->_storeBuildAttr();

    /**************** 开始子查询 SQL 的构造 ****************/
        // 复位构造字符串
        $this->_resetBuildAttr();
        // 调用闭包，将当前实例作为参数传入
        call_user_func($callback, $this);
        // 子查询构造字符串数组    
        $sub_attr = [];
        // 构造子查询 SQL
        $this->_buildQuery();
        // 保存子查询构造字符串，用于外层调用
        foreach ($this->_buildAttrs as $buildAttr) {
            $sub_attr[$buildAttr] = $this->$buildAttr;
        }
    /**************** 结束子查询 SQL 的构造 ****************/

    // 恢复现场
    $this->_reStoreBuildAttr($store);

    // 获取子查询 SQL 字符串，构造外层 SQL
    $this->_where_str .= $sub_attr['_prepare_sql'].' ) ';
    
    return $this;
}

测试

构造语句 SELECT * FROM student WHERE EXISTS ( SELECT * FROM classes WHERE id = 3);：

$results = $driver->table('student')
                  ->whereExists(function($query) {
                      $query->table('classes')
                            ->where('id', 3);
                  })
                  ->get();

大家在测试文件中试试看吧！

whereNotExists()、orWhereExists() 等模式就不单独演示了。完整代码请看 WorkerF - PDODriver.php。

优化

where exists 子句用到了子查询，但并不只有 where exists 使用子查询。最直接的 SELECT * FROM (SELECT * FROM table); 子查询语句，where in 子查询语句也用到子查询，那么重复的逻辑要提出来，Don’t Repeat Yourself！

基类中新建 _subBuilder() 方法，用来进行现场的保护恢复、子查询 SQL 的构造：

protected function _subBuilder(Closure $callback)
{
    // 现场保护
    $store = $this->_storeBuildAttr();

    /**************** begin sub query build ****************/

        $this->_resetBuildAttr();

        call_user_func($callback, $this);
        
        $sub_attr = [];

        $this->_buildQuery();

        foreach ($this->_buildAttrs as $buildAttr) {
            $sub_attr[$buildAttr] = $this->$buildAttr;
        }
    /**************** end sub query build ****************/

    // 现场恢复
    $this->_reStoreBuildAttr($store);

    return $sub_attr;
}

修改 whereExists() 方法：

public function whereExists(Closure $callback, $condition = 'EXISTS', $operator = 'AND')
{
    if( ! in_array($condition, ['EXISTS', 'NOT EXISTS']) || ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Error whereExists mode");
    }

    if($this->_where_str == '') {
        $this->_where_str = ' WHERE '.$condition.' ( ';
    } else {
        $this->_where_str .= ' '.$operator.' '.$condition.' ( ';
    }

    $sub_attr = $this->_subBuilder($callback);

    $this->_where_str .= $sub_attr['_prepare_sql'].' ) ';

    return $this;
}

where in 子查询

有了上面 where exists 的基础，where in 子查询的如出一辙：

public function whereInSub($field, Closure $callback, $condition = 'IN', $operator = 'AND')
{
    if( ! in_array($condition, ['IN', 'NOT IN']) || ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Error whereIn mode");
    }
    
    if($this->_where_str == '') {
        $this->_where_str = ' WHERE '.self::_wrapRow($field).' '.$condition.' ( ';
    } else {
        $this->_where_str .= ' '.$operator.' '.self::_wrapRow($field).' '.$condition.' ( ';
    }

    $sub_attr = $this->_subBuilder($callback);
    $this->_where_str .= $sub_attr['_prepare_sql'].' ) ';

    return $this;
}

构造 SQL SELECT * FROM student WHERE class_id IN (SELECT id FROM class);：

$results = $driver->table('student')
                ->whereInSub('class_id', function($query) {
                    $query->table('class')->select('id');
                })
                ->get();

同样，where not in、or where in 这些模式就不单独展示了。

单纯的子查询

单纯的 SELECT * FROM (子查询) 语句的构造就很简单了：

public function fromSub(Closure $callback)
{
    $sub_attr = $this->_subBuilder($callback);
    $this->_table .= ' ( '.$sub_attr['_prepare_sql'].' ) AS tb_'.uniqid().' ';

    return $this;
}

上述代码需要注意的地方：

FROM 子查询语句需要给子查询一个别名做表名，否则是语法错误，这里我们选择 uniqid() 函数生成一个随机的别名。
这里是用 _table 属性保存了子查询字符串，如果同时调用了 table() 方法会有冲突。

构造 SQL SELECT username, age FROM (SELECT * FROM test_table WHERE class_id = 3)：

$results = $driver->select('username', 'age')
            ->fromSub(function($query) {
                $query->table('test_table')->where('class_id', 3);
            })
            ->get();

复杂的 where 逻辑

在基本的 where 子句中，有时候会出现复杂的逻辑运算，比如多个条件用 OR 和 AND 来组合：

WHERE a = 1 OR a = 2 AND b = 1; 

AND 的优先级是大于 OR 的，如果想要先执行 OR 的条件，需要圆括号进行包裹：

WHERE a = 1 AND (b = 2 OR c = 3); 

AND 和 OR 我们可以用 where() 和 orWhere() 方法连接，但是圆括号的包裹还需要增加方法来实现。

思路

参考含有子查询的 SQL，我们可以把圆括号包裹的内部作为一个“子查询”字符串来看待，区别在于，我们不像是子查询构造中取整个子查询的 SQL，而是只取 where 子句的构造字符串。

Ok，有了思路，那就编码吧：

public function whereBrackets(Closure $callback, $operator = 'AND')
{
    if( ! in_array($operator, ['AND', 'OR'])) {
        throw new \InvalidArgumentException("Logical operator");
    }
    
    if($this->_where_str == '') {
        $this->_where_str = ' WHERE ( '; // 开头的括号包裹
    } else {
        $this->_where_str .= ' '.$operator.' ( '; // 开头的括号包裹
    }
    $sub_attr = $this->_subBuilder($callback);
    // 这里只取子查询构造中的 where 子句
    // 由于子查询的 where 子句会带上 WHERE 关键字，这里要去掉
    $this->_where_str .= preg_replace('/WHERE/', '', $sub_attr['_where_str'], 1).' ) '; // 结尾的括号包裹

    return $this;
}

构造 SQL SELECT * FROM test_table WHERE a = 1 AND (b = 2 OR c IS NOT NULL);：

$results = $driver->table('test_table')
            ->where('a', 1)
            ->whereBrackets(function($query) {
                $query->where('b', 2)
                      ->orWhereNotNull('c');
            })
            ->get();

orWhereBrackets() 就不单独演示了。

参考

【1】Laravel - Query Builder

上一篇 : 写一个“特殊”的查询构造器 - (三、条件查询)
下一篇 : 写一个“特殊”的查询构造器 - (五、聚合函数、分组、排序、分页)