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

baoxl 發布于2019-07-01 12:29 / 2336人閱讀

摘要：復雜的條件在的條件查詢中，不只有這些基本的子句，還有等復雜一些的子句。這篇我們就來講一下查詢構造器如何構造這些復雜的查詢語句。

復雜的條件

在 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

GPU云服務器云服務器 asp的條件查詢不同條件查詢表格存儲條件查詢條件查詢easyuidatagrid

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/30827.html

寫一個“特殊”的查詢構造器 - (前言)

摘要：而在項目開發中，我們想要的是一個更好用的可維護的工具，此時，對代碼的封裝模塊化就顯得尤為重要，于是出現了兩種方案查詢構造器，對象關系映射。典型環境下按照一般的查詢構造器處理就行。文章目錄寫一個特殊的查詢構造器 - (前言) 寫一個特殊的查詢構造器 - (一、程序結構，基礎封裝) 寫一個特殊的查詢構造器 - (二、第一條語句) 寫一個特殊的查詢構造器 - (三、條件查詢) 寫一個特殊...

GitChat 2019-07-01 10:00 評論0 收藏0
寫一個“特殊”的查詢構造器 - (三、條件查詢)

摘要：構造條件如果單單是執行這樣的語句，使用原生擴展就好了，使用查詢構造器就是殺雞用牛刀。這一篇，我們來講講如何使用查詢構造器進行條件查詢。構造 where 條件如果單單是執行 SELECT * FROM test_table; 這樣的語句，使用原生擴展就好了，使用查詢構造器就是殺雞用牛刀。當然，在實際的業務需求中，大部分的 SQL 都沒這么簡單，有各種條件查詢、分組、排序、連表等操作，...

why_rookie 2019-07-01 12:29 評論0 收藏0
寫一個“特殊”的查詢構造器 - (六、關聯)

摘要：雖然現在這樣的情況已經很少，但是對于查詢構造器而言，還是要提供一個方便的方法來對表前綴進行設置，特別是當你沒有權限修改表名的時候。所以我們將表前綴作為一個配置參數傳入查詢構造器，在查詢構造器的底層進行自動前綴添加。關聯查詢是關系型數據庫典型的查詢語句，根據兩個或多個表中的列之間的關系，從這些表中查詢數據。在 SQL 標準中使用 JOIN 和 ON 關鍵字來實現關聯查詢。 Join 子...

rainyang 2019-07-01 12:29 評論0 收藏0
寫一個“特殊”的查詢構造器 - (五、聚合函數、分組、排序、分頁)

摘要：聚合函數在中，有一些用來統計匯總的函數，被稱作聚合函數，如等。方法其它方法如之類的編寫就不一一展示了，代碼請看聚合函數。如何獲取總數當然是使用上面講到的聚合函數來處理。 where 相關的子句構造完成后，我們繼續構造其它子句。這一篇我們進行聚合函數、分組、排序等子句的構造。聚合函數在 SQL 中，有一些用來統計、匯總的函數，被稱作聚合函數，如 SUM、COUNT、AVG 等。使用...

iamyoung001 2019-07-01 12:29 評論0 收藏0

發表評論

登陸后可評論

0條評論

baoxl

男|高級講師

我要關注我要私信

TA的文章

制作Deepin深度系統鏡像且快速安裝到筆記本電腦中

閱讀 2955·2021-10-20 13:46
歐路云：1核/1G/30G/30Mbps不限流量/洛杉磯三網GIA/帶150G防御/月付16元

閱讀 2512·2021-08-12 13:22
前端每日實戰：85# 視頻演示如何用純 CSS 創作一個小球反彈的動畫

閱讀 2693·2019-08-30 15:54
vue中css樣式覆蓋問題 vue中css樣式作用域 vue.js樣式不起作用

閱讀 2336·2019-08-30 15:53
Vue學習筆記（一）

閱讀 540·2019-08-30 13:47
JavaScript 是如何工作的：JavaScript 的內存模型

閱讀 3574·2019-08-23 16:56
js中的｜與 && 運算符詳解

閱讀 1720·2019-08-23 13:02
初學者可能不知道的vue技巧

閱讀 1790·2019-08-23 12:25

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

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

相關文章

**寫一個“特殊”的查詢構造器 - (前言)**

**寫一個“特殊”的查詢構造器 - (三、條件查詢)**

**寫一個“特殊”的查詢構造器 - (六、關聯)**

**寫一個“特殊”的查詢構造器 - (五、聚合函數、分組、排序、分頁)**

發表評論

0條評論

baoxl

男|高級講師

TA的文章

制作Deepin深度系統鏡像且快速安裝到筆記本電腦中

歐路云：1核/1G/30G/30Mbps不限流量/洛杉磯三網GIA/帶150G防御/月付16元

前端每日實戰：85# 視頻演示如何用純 CSS 創作一個小球反彈的動畫

vue中css樣式覆蓋問題 vue中css樣式作用域 vue.js樣式不起作用

Vue學習筆記（一）

JavaScript 是如何工作的：JavaScript 的內存模型

js中的｜與 && 運算符詳解

初學者可能不知道的vue技巧

最新活動

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

寫一個“特殊”的查詢構造器 - (四、條件查詢：復雜條件)

相關文章

發表評論

0條評論

男|高級講師

TA的文章

最新活動

上云采購季！| 2核2G4M爆款云服務器低至59元/年，更有多臺、長期優惠，快來選購！

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