復雜的條件

在 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

總結

以上是生活随笔為你收集整理的写一个“特殊”的查询构造器 - (四、条件查询：复杂条件)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。