寫一個(gè)“特殊”的查詢構(gòu)造器 - (四、條件查詢：復(fù)雜條件)

baoxl 發(fā)布于2019-07-01 12:29 / 2361人閱讀

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

復(fù)雜的條件

在 SQL 的條件查詢中，不只有 where、or where 這些基本的子句，還有 where in、where exists、where between 等復(fù)雜一些的子句。而且即使是 where 這種基礎(chǔ)的子句，也有多個(gè)條件的多種邏輯組合。這篇我們就來講一下查詢構(gòu)造器如何構(gòu)造這些復(fù)雜的查詢語句。

where 系列 where in 子句

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

-- 從一個(gè)數(shù)據(jù)范圍獲取
SELECT * FROM test_table WHERE age IN (18, 20, 22, 24);
-- 從一個(gè)子查詢獲取
SELECT * FROM test_table WHERE username IN (SELECT username FROM test_name_table);

從一個(gè)子查詢獲取的模式有些復(fù)雜我們稍后再說，先分析下從數(shù)據(jù)范圍獲取的方式。

where in 子句判斷字段是否屬于一個(gè)數(shù)據(jù)集合，有 where in、where not in、or where in、or where not in 四種模式。我們只需構(gòu)造好這個(gè)數(shù)據(jù)集合，并對(duì)集合中的數(shù)據(jù)進(jìn)行數(shù)據(jù)綁定即可。

基類中添加 whereIn() 方法:

// $field where in 要查的字段
// $data  進(jìn)行判斷的數(shù)據(jù)集合
// $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");
    }
    // 生成占位符，綁定數(shù)據(jù)
    foreach ($data as $key => $value) {
        $plh = self::_getPlh();
        $data[$key] = $plh;
        $this->_bind_params[$plh] = $value;
    }
    // 第一次調(diào)用該方法，需要 WHERE 關(guān)鍵字
    if($this->_where_str == "") {
        $this->_where_str = " WHERE ".self::_wrapRow($field)." ".$condition." (".implode(",", $data).")";
    } else { // 非初次調(diào)用，使用分隔符連接
        $this->_where_str .= " ".$operator." ".self::_wrapRow($field)." ".$condition." (".implode(",", $data).")";
    }
    // 方便鏈?zhǔn)秸{(diào)用，返回當(dāng)前實(shí)例
    return $this;
}

關(guān)于上述代碼，由于 where in、where not in、or where in、or where not in 這寫方法的區(qū)別只是關(guān)鍵字的區(qū)別，對(duì)于字符串來說只需替換關(guān)鍵字即可。所以對(duì)于這些方法，為了方便，我們把這些模式的關(guān)鍵字作為方法的參數(shù)傳入，可以提高代碼的重用性。

那么，另外三種模式的代碼可以這么寫：

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");
}

構(gòu)造測試

$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 子句的構(gòu)造和 where in 相差無幾，只有語法上的區(qū)別，而且只有 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");
    }
    // 生成占位符，綁定數(shù)據(jù)
    $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 子句中使用單條件模式數(shù)據(jù)為 NULL 時(shí)則進(jìn)行 IS NULL 的判斷。但是我們想要一個(gè)更靈活、語義更清晰的接口，所以這里為 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 子句時(shí)，構(gòu)造就有些難度了。我們回憶一下使用 where exists 子句的 SQL：

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

沒錯(cuò)，和之前構(gòu)造的語句不同，where exists 子句存在子查詢。之前的 sql 構(gòu)造都是通過 _buildQuery() 方法按照一定的順序構(gòu)造的，那么如何對(duì)子查詢進(jìn)行構(gòu)造呢？子查詢中的 where 子句和外層查詢的 where 子句同時(shí)存在時(shí)，又該怎么區(qū)分呢？

首先，觀察一下有子查詢的 SQL，可以看出：子查詢是一個(gè)獨(dú)立的查詢語句。

那么，能不能將子查詢語句和外層查詢語句各自多帶帶構(gòu)造，然后再組合到一起成為一條完整的 SQL 呢？

當(dāng)然是可以的。不過，如何去多帶帶構(gòu)造子查詢語句呢？如果子查詢中還有子查詢語句呢？

我們先看下 laravel 中的 where exists 構(gòu)造語句是什么樣的【1】：

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

laravel 查詢構(gòu)造器的 whereExists() 方法接受一個(gè)閉包，閉包接收一個(gè)查詢構(gòu)造器實(shí)例，用于在閉包中構(gòu)造子句。

使用閉包的好處是：

給接受閉包參數(shù)的函數(shù)擴(kuò)展功能 (進(jìn)行子查詢語句構(gòu)造)

閉包傳入函數(shù)中，函數(shù)可以控制這個(gè)閉包的執(zhí)行方式，在閉包的執(zhí)行前后可以做相應(yīng)操作 (現(xiàn)場保護(hù)、恢復(fù))

基本結(jié)構(gòu)

所以參考 laravel，我們也使用傳入閉包的方式，我們先確定一下 whereExists() 方法的基本結(jié)構(gòu)：

// $callback 閉包參數(shù)
// $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");
    }
    // 初次調(diào)用？
    if($this->_where_str == "") {
        $this->_where_str = " WHERE ".$condition." ( ";
    } else {
        $this->_where_str .= " ".$operator." ".$condition." ( ";
    }

    // 進(jìn)行現(xiàn)場保護(hù)
    ...
    // 閉包調(diào)用，傳入當(dāng)前實(shí)例
    ...
    // 現(xiàn)場恢復(fù)
    ...

    // 返回當(dāng)前實(shí)例
    return $this;
}

因?yàn)槭褂玫搅?Closure 限制參數(shù)類型，要在基類文件的頂部加上：

use Closure;

現(xiàn)場的保護(hù)和恢復(fù)

上面一直再說現(xiàn)場的保護(hù)和恢復(fù)，那么我們保護(hù)、恢復(fù)的這個(gè)現(xiàn)場是什么呢？

我們先理一下構(gòu)造一個(gè)普通的 SQL 的步驟：依次構(gòu)造各個(gè)查詢子句、使用 _buildQuery() 方法將這些子句按照固定順序組合成 SQL。

那么在有子查詢的過程中，意味著這樣的步驟要經(jīng)過兩次，但是由于要傳入當(dāng)前實(shí)例 (另外新建實(shí)例的話會(huì)創(chuàng)建新連接)，第二次查詢構(gòu)造會(huì)覆蓋掉第一次構(gòu)造的結(jié)果。所以，我們這里的現(xiàn)場就是這些構(gòu)造用的子句字符串。

有了現(xiàn)場的保護(hù)和恢復(fù)，即使在閉包中調(diào)用閉包 (即子查詢中嵌套子查詢) 的情形下也能正確的構(gòu)造需要的 SQL 語句。(有沒有覺得很像遞歸呢？的確這里是借鑒了棧的使用思路。)

首先我們需要一個(gè)保存構(gòu)造字符串名稱的數(shù)組 (用來獲取構(gòu)造字符串屬性)，在基類添加屬性 _buildAttrs：

// 這里保存了需要保護(hù)現(xiàn)場的構(gòu)造字符串名稱
protected $_buildAttrs = [
    "_table",
    "_prepare_sql",
    "_cols_str",
    "_where_str",
    "_orderby_str",
    "_groupby_str",
    "_having_str",
    "_join_str",
    "_limit_str",
];

然后，添加保護(hù)現(xiàn)場和恢復(fù)現(xiàn)場的方法：

// 保護(hù)現(xiàn)場
protected function _storeBuildAttr()
{
    $store = [];
    // 將實(shí)例的相關(guān)屬性保存到 $store，并返回
    foreach ($this->_buildAttrs as $buildAttr) {
        $store[$buildAttr] = $this->$buildAttr;
    }

    return $store;
}
//恢復(fù)現(xiàn)場
protected function _reStoreBuildAttr(array $data)
{
    // 從 $data 取數(shù)據(jù)恢復(fù)當(dāng)前實(shí)例的屬性
    foreach ($this->_buildAttrs as $buildAttr) {
        $this->$buildAttr = $data[$buildAttr];
    }
}

當(dāng)然，保護(hù)了現(xiàn)場后，子查詢要使用實(shí)例的屬性時(shí)需要的是一個(gè)初始狀態(tài)的屬性，所以我們還需要一個(gè)可以重置這些構(gòu)造字符串的方法：

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()

有了保護(hù)、恢復(fù)現(xiàn)場的方法，我們繼續(xù)完成 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." ( ";
    }

    // 保護(hù)現(xiàn)場，將構(gòu)造字符串屬性都保存起來
    $store = $this->_storeBuildAttr();

    /**************** 開始子查詢 SQL 的構(gòu)造 ****************/
        // 復(fù)位構(gòu)造字符串
        $this->_resetBuildAttr();
        // 調(diào)用閉包，將當(dāng)前實(shí)例作為參數(shù)傳入
        call_user_func($callback, $this);
        // 子查詢構(gòu)造字符串?dāng)?shù)組    
        $sub_attr = [];
        // 構(gòu)造子查詢 SQL
        $this->_buildQuery();
        // 保存子查詢構(gòu)造字符串，用于外層調(diào)用
        foreach ($this->_buildAttrs as $buildAttr) {
            $sub_attr[$buildAttr] = $this->$buildAttr;
        }
    /**************** 結(jié)束子查詢 SQL 的構(gòu)造 ****************/

    // 恢復(fù)現(xiàn)場
    $this->_reStoreBuildAttr($store);

    // 獲取子查詢 SQL 字符串，構(gòu)造外層 SQL
    $this->_where_str .= $sub_attr["_prepare_sql"]." ) ";
    
    return $this;
}

測試

構(gòu)造語句 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() 等模式就不多帶帶演示了。完整代碼請(qǐng)看 WorkerF - PDODriver.php。

優(yōu)化

where exists 子句用到了子查詢，但并不只有 where exists 使用子查詢。最直接的 SELECT * FROM (SELECT * FROM table); 子查詢語句，where in 子查詢語句也用到子查詢，那么重復(fù)的邏輯要提出來，Don"t Repeat Yourself！

基類中新建 _subBuilder() 方法，用來進(jìn)行現(xiàn)場的保護(hù)恢復(fù)、子查詢 SQL 的構(gòu)造：

protected function _subBuilder(Closure $callback)
{
    // 現(xiàn)場保護(hù)
    $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 ****************/

    // 現(xiàn)場恢復(fù)
    $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 的基礎(chǔ)，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;
}

構(gòu)造 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 (子查詢) 語句的構(gòu)造就很簡單了：

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

    return $this;
}

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

FROM 子查詢語句需要給子查詢一個(gè)別名做表名，否則是語法錯(cuò)誤，這里我們選擇 uniqid() 函數(shù)生成一個(gè)隨機(jī)的別名。

這里是用 _table 屬性保存了子查詢字符串，如果同時(shí)調(diào)用了 table() 方法會(huì)有沖突。

構(gòu)造 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();

復(fù)雜的 where 邏輯

在基本的 where 子句中，有時(shí)候會(huì)出現(xiàn)復(fù)雜的邏輯運(yùn)算，比如多個(gè)條件用 OR 和 AND 來組合：

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

AND 的優(yōu)先級(jí)是大于 OR 的，如果想要先執(zhí)行 OR 的條件，需要圓括號(hào)進(jìn)行包裹：

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

AND 和 OR 我們可以用 where() 和 orWhere() 方法連接，但是圓括號(hào)的包裹還需要增加方法來實(shí)現(xiàn)。

思路

參考含有子查詢的 SQL，我們可以把圓括號(hào)包裹的內(nèi)部作為一個(gè)“子查詢”字符串來看待，區(qū)別在于，我們不像是子查詢構(gòu)造中取整個(gè)子查詢的 SQL，而是只取 where 子句的構(gòu)造字符串。

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 ( "; // 開頭的括號(hào)包裹
    } else {
        $this->_where_str .= " ".$operator." ( "; // 開頭的括號(hào)包裹
    }
    $sub_attr = $this->_subBuilder($callback);
    // 這里只取子查詢構(gòu)造中的 where 子句
    // 由于子查詢的 where 子句會(huì)帶上 WHERE 關(guān)鍵字，這里要去掉
    $this->_where_str .= preg_replace("/WHERE/", "", $sub_attr["_where_str"], 1)." ) "; // 結(jié)尾的括號(hào)包裹

    return $this;
}

構(gòu)造 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云服務(wù)器云服務(wù)器 asp的條件查詢不同條件查詢表格存儲(chǔ)條件查詢條件查詢easyuidatagrid

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://systransis.cn/yun/30827.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

baoxl

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

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

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

閱讀 2544·2021-08-12 13:22
前端每日實(shí)戰(zhàn)：85# 視頻演示如何用純 CSS 創(chuàng)作一個(gè)小球反彈的動(dòng)畫

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

閱讀 2363·2019-08-30 15:53
Vue學(xué)習(xí)筆記（一）

閱讀 570·2019-08-30 13:47
JavaScript 是如何工作的：JavaScript 的內(nèi)存模型

閱讀 3605·2019-08-23 16:56
js中的｜與 && 運(yùn)算符詳解

閱讀 1747·2019-08-23 13:02
初學(xué)者可能不知道的vue技巧

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

成人国产在线小视频_日韩寡妇人妻调教在线播放_色成人www永久在线观看_2018国产精品久久_亚洲欧美高清在线30p_亚洲少妇综合一区_黄色在线播放国产_亚洲另类技巧小说校园_国产主播xx日韩_a级毛片在线免费

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長期優(yōu)惠，快來選購！

寫一個(gè)“特殊”的查詢構(gòu)造器 - (四、條件查詢：復(fù)雜條件)

相關(guān)文章

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

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

**寫一個(gè)“特殊”的查詢構(gòu)造器 - (六、關(guān)聯(lián))**

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

發(fā)表評(píng)論

0條評(píng)論

baoxl

男|高級(jí)講師

TA的文章

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

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

前端每日實(shí)戰(zhàn)：85# 視頻演示如何用純 CSS 創(chuàng)作一個(gè)小球反彈的動(dòng)畫

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

Vue學(xué)習(xí)筆記（一）

JavaScript 是如何工作的：JavaScript 的內(nèi)存模型

js中的｜與 && 運(yùn)算符詳解

初學(xué)者可能不知道的vue技巧

最新活動(dòng)

資訊專欄INFORMATION COLUMN

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長期優(yōu)惠，快來選購！

寫一個(gè)“特殊”的查詢構(gòu)造器 - (四、條件查詢：復(fù)雜條件)

相關(guān)文章

發(fā)表評(píng)論

0條評(píng)論

男|高級(jí)講師

TA的文章

最新活動(dòng)

上云采購季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長期優(yōu)惠，快來選購！

寫一個(gè)“特殊”的查詢構(gòu)造器 - (四、條件查詢：復(fù)雜條件)