寫一個“特殊”的查詢構造器 – (四、條件查詢：複雜條件)

MrQin發表於2019-02-16

複雜的條件

在 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

複合條件查詢的重構
2017-11-21
寫一個“特殊”的查詢構造器 – (二、第一條語句)
2019-02-16
MongoDB查詢條件
2018-04-27
MongoDB
MongoDB條件查詢
2015-01-13
MongoDB
mysql條件查詢
2024-04-06
MySql
Laravel 多條件查詢
2020-04-22
Laravel
SQL多條件查詢
2010-11-30
SQL
條件查詢JSP
2009-06-23
JS
JN專案-查詢條件過濾特殊字元
2017-11-24
字元
Go語言ORM包：使用worm構造查詢條件
2022-07-18
GoWorm
Linq查詢之多個排序條件
2018-06-21
排序
Linq兩個from查詢條件
2018-06-21
mongodb條件查詢不等於
2021-09-11
MongoDB
golang beego orm 查詢條件 or and
2020-11-11
GolangORM
【mybatis-plus】條件查詢
2020-12-29
MyBatis
多條件查詢---ssh版本
2010-11-21
sql 查詢條件問題
2004-09-24
SQL
查詢作為條件的SQL
2011-01-04
SQL
一個NOT EXISTS含有OR條件子查詢的優化
2014-10-10
優化
mybatis-plus QueryWrapper條件查詢器
2020-12-15
MyBatisAPP
mysql拆分字串做條件查詢
2022-07-05
MySql字串
AntDesignBlazor示例——列表查詢條件
2023-12-03
Blazor
菜品條件分頁查詢
2024-04-20
設計一個介面支援各種查詢條件
2003-08-19
oracle date資料的條件查詢
2014-12-03
Oracle
SQL SERVER 條件語句的查詢
2016-05-21
SQLServer
寫一個“特殊”的查詢構造器 – (七、DML 語句、事務)
2019-02-16
【索引】反向索引--條件範圍查詢
2010-09-07
索引
mysql多條件過濾查詢之mysq高階查詢
2021-12-30
MySql
Laravel 多條件查詢時粗心導致的一個 BUG
2019-05-30
Laravel
RANK函式基於條件的查詢
2009-10-24
函式
查詢條件和條數，先查詢兩條免費的，後面為vip
2020-10-12
記錄一篇關於條件查詢語句的用法:1.通過QueryWrapper進行條件構造2.通過Example進行條件構造
2020-11-09
APP
將主查詢條件寫到子查詢裡執行效果會怎樣？
2012-06-04
等於NULL的查詢條件導致查詢結果不正確
2010-04-23
Null
Laravel Eloquent ORM 多條件查詢，你會怎麼寫？
2021-01-27
LaravelORM
exp匯出一個表中符合查詢條件的資料
2010-07-23
深入理解mongodb查詢條件語句
2018-11-18
MongoDB