查詢生成器（Query Builder）: 使用簡單抽象層查詢數據庫 · Yii 2.0 權威指南

# 查詢構建器查詢構建器建立在?[Database Access Objects](http://www.yiichina.com/doc/guide/2.0/db-dao)?基礎之上，可讓你創建程序化的、DBMS無關的SQL語句。相比于原生的SQL語句，查詢構建器可以幫你寫出可讀性更強的SQL相關的代碼，并生成安全性更強的SQL語句。使用查詢構建器通常包含以下兩個步驟： 1. 創建一個 yii\db\Query 對象來代表一條 SELECT SQL 語句的不同子句（例如?`SELECT`,?`FROM`）。 2. 執行 yii\db\Query 的一個查詢方法（例如：`all()`）從數據庫當中檢索數據。如下所示代碼是查詢構造器的一個典型用法： ~~~ $rows = (new \yii\db\Query()) ->select(['id', 'email']) ->from('user') ->where(['last_name' => 'Smith']) ->limit(10) ->all(); ~~~ 上面的代碼將會生成并執行如下的SQL語句，其中?`:last_name`?參數綁定了字符串?`'Smith'`。 ~~~ SELECT `id`, `email` FROM `user` WHERE `last_name` = :last_name LIMIT 10 ~~~ > 提示: 你平時更多的時候會使用 yii\db\Query 而不是 [yii\db\QueryBuilder]]。當你調用其中一個查詢方法時，后者將會被前者隱式的調用。yii\db\QueryBuilder主要負責將 DBMS 不相關的 yii\db\Query 對象轉換成 DBMS 相關的 SQL 語句（例如，以不同的方式引用表或字段名稱）。 ## 創建查詢為了創建一個 yii\db\Query 對象，你需要調用不同的查詢構建方法來代表SQL語句的不同子句。這些方法的名稱集成了在SQL語句相應子句中使用的關鍵字。例如，為了指定 SQL 語句當中的?`FROM`?子句，你應該調用?`from()`?方法。所有的查詢構建器方法返回的是查詢對象本身，也就是說，你可以把多個方法的調用串聯起來。接下來，我們會對這些查詢構建器方法進行一一講解： yii\db\Query::select() yii\db\Query::select() 方法用來指定 SQL 語句當中的?`SELECT`?子句。你可以像下面的例子一樣使用一個數組或者字符串來定義需要查詢的字段。當 SQL 語句是由查詢對象生成的時候，被查詢的字段名稱將會自動的被引號括起來。 ~~~ $query->select(['id', 'email']); // 等同于： $query->select('id, email'); ~~~ 就像寫原生 SQL 語句一樣，被選取的字段可以包含表前綴，以及/或者字段別名。例如： ~~~ $query->select(['user.id AS user_id', 'email']); // 等同于： $query->select('user.id AS user_id, email'); ~~~ 如果使用數組格式來指定字段，你可以使用數組的鍵值來表示字段的別名。例如，上面的代碼可以被重寫為如下形式： ~~~ $query->select(['user_id' => 'user.id', 'email']); ~~~ 如果你在組建查詢時沒有調用 yii\db\Query::select() 方法，那么選擇的將是?`'*'`?，也即選取的是所有的字段。除了字段名稱以外，你還可以選擇數據庫的表達式。當你使用到包含逗號的數據庫表達式的時候，你必須使用數組的格式，以避免自動的錯誤的引號添加。例如： ~~~ $query->select(["CONCAT(first_name, ' ', last_name) AS full_name", 'email']); ~~~ 從 2.0.1 的版本開始你就可以使用子查詢了。在定義每一個子查詢的時候，你應該使用 yii\db\Query 對象。例如： ~~~ $subQuery = (new Query())->select('COUNT(*)')->from('user'); // SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post` $query = (new Query())->select(['id', 'count' => $subQuery])->from('post'); ~~~ 你應該調用 yii\db\Query::distinct() 方法來去除重復行，如下所示： ~~~ // SELECT DISTINCT `user_id` ... $query->select('user_id')->distinct(); ~~~ 你可以調用 yii\db\Query::addSelect() 方法來選取附加字段，例如： ~~~ $query->select(['id', 'username']) ->addSelect(['email']); ~~~ yii\db\Query::from() yii\db\Query::from() 方法指定了 SQL 語句當中的?`FROM`?子句。例如： ~~~ // SELECT * FROM `user` $query->from('user'); ~~~ 你可以通過字符串或者數組的形式來定義被查詢的表名稱。就像你寫原生的 SQL 語句一樣，表名稱里面可包含數據庫前綴，以及/或者表別名。例如： ~~~ $query->from(['public.user u', 'public.post p']); // 等同于： $query->from('public.user u, public.post p'); ~~~ 如果你使用的是數組的格式，那么你同樣可以用數組的鍵值來定義表別名，如下所示： ~~~ $query->from(['u' => 'public.user', 'p' => 'public.post']); ~~~ 除了表名以外，你還可以從子查詢中再次查詢，這里的子查詢是由 yii\db\Query 創建的對象。例如： ~~~ $subQuery = (new Query())->select('id')->from('user')->where('status=1'); // SELECT * FROM (SELECT `id` FROM `user` WHERE status=1) u $query->from(['u' => $subQuery]); ~~~ yii\db\Query::where() yii\db\Query::where() 方法定義了 SQL 語句當中的?`WHERE`?子句。你可以使用如下三種格式來定義?`WHERE`?條件： * 字符串格式，例如：`'status=1'` * 哈希格式，例如：?`['status' => 1, 'type' => 2]` * 操作符格式，例如：`['like', 'name', 'test']` #### 字符串格式在定義非常簡單的查詢條件的時候，字符串格式是最合適的。它看起來和原生 SQL 語句差不多。例如： ~~~ $query->where('status=1'); // 或者使用參數綁定來綁定動態參數值 $query->where('status=:status', [':status' => $status]); ~~~ 千萬不要像如下的例子一樣直接在條件語句當中嵌入變量，特別是當這些變量來源于終端用戶輸入的時候，因為這樣我們的軟件將很容易受到 SQL 注入的攻擊。 ~~~ // 危險！千萬別這樣干，除非你非常的確定 $status 是一個整型數值。 $query->where("status=$status"); ~~~ 當使用參數綁定的時候，你可以調用 yii\db\Query::params() 或者 yii\db\Query::addParams() 方法來分別綁定不同的參數。 ~~~ $query->where('status=:status') ->addParams([':status' => $status]); ~~~ #### 哈希格式哈希格式最適合用來指定多個?`AND`?串聯起來的簡單的"等于斷言"子條件。它是以數組的形式來書寫的，數組的鍵表示字段的名稱，而數組的值則表示這個字段需要匹配的值。例如： ~~~ // ...WHERE (`status` = 10) AND (`type` IS NULL) AND (`id` IN (4, 8, 15)) $query->where([ 'status' => 10, 'type' => null, 'id' => [4, 8, 15], ]); ~~~ 就像你所看到的一樣，查詢構建器非常的智能，能恰當地處理數值當中的空值和數組。你也可以像下面那樣在子查詢當中使用哈希格式： ~~~ $userQuery = (new Query())->select('id')->from('user'); // ...WHERE `id` IN (SELECT `id` FROM `user`) $query->where(['id' => $userQuery]); ~~~ #### 操作符格式操作符格式允許你指定類程序風格的任意條件語句，如下所示： ~~~ [操作符, 操作數1, 操作數2, ...] ~~~ 其中每個操作數可以是字符串格式、哈希格式或者嵌套的操作符格式，而操作符可以是如下列表中的一個： * `and`: 操作數會被?`AND`?關鍵字串聯起來。例如，`['and', 'id=1', 'id=2']`?將會生成?`id=1 AND id=2`。如果操作數是一個數組，它也會按上述規則轉換成字符串。例如，`['and', 'type=1', ['or', 'id=1', 'id=2']]`?將會生成?`type=1 AND (id=1 OR id=2)`。這個方法不會自動加引號或者轉義。 * `or`: 用法和?`and`?操作符類似，這里就不再贅述。 * `between`: 第一個操作數為字段名稱，第二個和第三個操作數代表的是這個字段的取值范圍。例如，`['between', 'id', 1, 10]`將會生成?`id BETWEEN 1 AND 10`。 * `not between`: 用法和?`BETWEEN`?操作符類似，這里就不再贅述。 * `in`: 第一個操作數應為字段名稱或者 DB 表達式。第二個操作符既可以是一個數組，也可以是一個?`Query`?對象。它會轉換成`IN`條件語句。如果第二個操作數是一個數組，那么它代表的是字段或 DB 表達式的取值范圍。如果第二個操作數是?`Query`?對象，那么這個子查詢的結果集將會作為第一個操作符的字段或者 DB 表達式的取值范圍。例如，?`['in', 'id', [1, 2, 3]]`?將生成?`id IN (1, 2, 3)`。該方法將正確地為字段名加引號以及為取值范圍轉義。`in`?操作符還支持組合字段，此時，操作數1應該是一個字段名數組，而操作數2應該是一個數組或者?`Query`?對象，代表這些字段的取值范圍。 * `not in`: 用法和?`in`?操作符類似，這里就不再贅述。 * `like`: 第一個操作數應為一個字段名稱或 DB 表達式，第二個操作數可以使字符串或數組，代表第一個操作數需要模糊查詢的值。比如，`['like', 'name', 'tester']`?會生成?`name LIKE '%tester%'`。如果范圍值是一個數組，那么將會生成用?`AND`?串聯起來的多個?`like`?語句。例如，`['like', 'name', ['test', 'sample']]`?將會生成?`name LIKE '%test%' AND name LIKE '%sample%'`。你也可以提供第三個可選的操作數來指定應該如何轉義數值當中的特殊字符。該操作數是一個從需要被轉義的特殊字符到轉義副本的數組映射。如果沒有提供這個操作數，將會使用默認的轉義映射。如果需要禁用轉義的功能，只需要將參數設置為?`false`?或者傳入一個空數組即可。需要注意的是，當使用轉義映射（又或者沒有提供第三個操作數的時候），第二個操作數的值的前后將會被加上百分號。 > 注意：當使用 PostgreSQL 的時候你還可以使用?[`ilike`](http://www.postgresql.org/docs/8.3/static/functions-matching.html#FUNCTIONS-LIKE)， > 該方法對大小寫不敏感。 * `or like`: 用法和?`like`?操作符類似，區別在于當第二個操作數為數組時，會使用?`OR`?來串聯多個?`LIKE`?條件語句。 * `not like`: 用法和?`like`?操作符類似，區別在于會使用?`NOT LIKE`?來生成條件語句。 * `or not like`: 用法和?`not like`?操作符類似，區別在于會使用?`OR`?來串聯多個?`NOT LIKE`?條件語句。 * `exists`: 需要一個操作數，該操作數必須是代表子查詢 yii\db\Query 的一個實例，它將會構建一個?`EXISTS (sub-query)`?表達式。 * `not exists`: 用法和?`exists`?操作符類似，它將創建一個?`NOT EXISTS (sub-query)`?表達式。 * `>`,?`<=`, 或者其他包含兩個操作數的合法 DB 操作符: 第一個操作數必須為字段的名稱，而第二個操作數則應為一個值。例如，`['>', 'age', 10]`?將會生成?`age>10`。 #### 附加條件你可以使用 yii\db\Query::andWhere() 或者 yii\db\Query::orWhere() 在原有條件的基礎上附加額外的條件。你可以多次調用這些方法來分別追加不同的條件。例如， ~~~ $status = 10; $search = 'yii'; $query->where(['status' => $status]); if (!empty($search)) { $query->andWhere(['like', 'title', $search]); } ~~~ 如果?`$search`?不為空，那么將會生成如下 SQL 語句： ~~~ ... WHERE (`status` = 10) AND (`title` LIKE '%yii%') ~~~ #### 過濾條件當?`WHERE`?條件來自于用戶的輸入時，你通常需要忽略用戶輸入的空值。例如，在一個可以通過用戶名或者郵箱搜索的表單當中，用戶名或者郵箱輸入框沒有輸入任何東西，這種情況下你想要忽略掉對應的搜索條件，那么你就可以使用 yii\db\Query::filterWhere() 方法來實現這個目的： ~~~ // $username 和 $email 來自于用戶的輸入 $query->filterWhere([ 'username' => $username, 'email' => $email, ]); ~~~ yii\db\Query::filterWhere() 和 yii\db\Query::where() 唯一的不同就在于，前者將忽略在條件當中的[hash format](http://www.yiichina.com/doc/guide/2.0/db-query-builder#hash-format)的空值。所以如果`$email`?為空而?`$username`?不為空，那么上面的代碼最終將生產如下 SQL?`...WHERE username=:username`。 > 提示：當一個值為 null、空數組、空字符串或者一個只包含空白字符時，那么它將被判定為空值。類似于 [yii\db\Query::andWhere()|andWhere()]] 和 yii\db\Query::orWhere(), 你可以使用 yii\db\Query::andFilterWhere() 和 yii\db\Query::orFilterWhere() 方法來追加額外的過濾條件。 yii\db\Query::orderBy() yii\db\Query::orderBy() 方法是用來指定 SQL 語句當中的?`ORDER BY`?子句的。例如， ~~~ // ... ORDER BY `id` ASC, `name` DESC $query->orderBy([ 'id' => SORT_ASC, 'name' => SORT_DESC, ]); ~~~ 如上所示，數組當中的鍵指代的是字段名稱，而數組當中的值則表示的是排序的方式。 PHP 的常量?`SORT_ASC`?指的是升序排列，`SORT_DESC`?指的則是降序排列。如果?`ORDER BY`?僅僅包含簡單的字段名稱，你可以使用字符串來聲明它，就像寫原生的 SQL 語句一樣。例如， ~~~ $query->orderBy('id ASC, name DESC'); ~~~ > 注意：當?`ORDER BY`?語句包含一些 DB 表達式的時候，你應該使用數組的格式。你可以調用 [yii\db\Query::addOrderBy()|addOrderBy()]] 來為?`ORDER BY`?片斷添加額外的子句。例如， ~~~ $query->orderBy('id ASC') ->addOrderBy('name DESC'); ~~~ yii\db\Query::groupBy() yii\db\Query::groupBy() 方法是用來指定 SQL 語句當中的?`GROUP BY`?片斷的。例如， ~~~ // ... GROUP BY `id`, `status` $query->groupBy(['id', 'status']); ~~~ 如果?`GROUP BY`?僅僅包含簡單的字段名稱，你可以使用字符串來聲明它，就像寫原生的 SQL 語句一樣。例如， ~~~ $query->groupBy('id, status'); ~~~ > 注意：當?`GROUP BY`?語句包含一些 DB 表達式的時候，你應該使用數組的格式。你可以調用 [yii\db\Query::addOrderBy()|addOrderBy()]] 來為?`GROUP BY`?子句添加額外的字段。例如， ~~~ $query->groupBy(['id', 'status']) ->addGroupBy('age'); ~~~ yii\db\Query::having() yii\db\Query::having() 方法是用來指定 SQL 語句當中的?`HAVING`?子句。它帶有一個條件，和?[where()](http://www.yiichina.com/doc/guide/2.0/db-query-builder#where)?中指定條件的方法一樣。例如， ~~~ // ... HAVING `status` = 1 $query->having(['status' => 1]); ~~~ 請查閱?[where()](http://www.yiichina.com/doc/guide/2.0/db-query-builder#where)?的文檔來獲取更多有關于如何指定一個條件的細節。你可以調用 yii\db\Query::andHaving() 或者 yii\db\Query::orHaving() 方法來為?`HAVING`?子句追加額外的條件，例如， ~~~ // ... HAVING (`status` = 1) AND (`age` > 30) $query->having(['status' => 1]) ->andHaving(['>', 'age', 30]); ~~~ yii\db\Query::limit() 和 yii\db\Query::offset()` yii\db\Query::limit() 和 yii\db\Query::offset() 是用來指定 SQL 語句當中的?`LIMIT`?和?`OFFSET`?子句的。例如， ~~~ // ... LIMIT 10 OFFSET 20 $query->limit(10)->offset(20); ~~~ 如果你指定了一個無效的 limit 或者 offset（例如，一個負數），那么它將會被忽略掉。 > 提示：在不支持?`LIMIT`?和?`OFFSET`?的 DBMS 中（例如，MSSQL），查詢構建器將生成一條模擬?`LIMIT`/`OFFSET`?行為的 SQL 語句。 ~~~ ### yii\db\Query::join() [yii\db\Query::join()|join()]] 是用來指定 SQL 語句當中的?`JOIN`?子句的。例如， php // LEFT JOIN?`post`?ON?`post`.`user_id`?=?`user`.`id` $query->join('LEFT JOIN', 'post', 'post.user_id = user.id'); ~~~ yii\db\Query::join() 帶有四個參數： * `$type`: 連接類型，例如：`'INNER JOIN'`,?`'LEFT JOIN'`。 * `$table`: 將要連接的表名稱。 * `$on`: 可選參數，連接條件，即?`ON`?子句。請查閱?[where()](http://www.yiichina.com/doc/guide/2.0/db-query-builder#where)?獲取更多有關于條件定義的細節。 * `$params`: 可選參數，與連接條件綁定的參數。你可以分別調用如下的快捷方法來指定?`INNER JOIN`,?`LEFT JOIN`?和?`RIGHT JOIN`。 * yii\db\Query::innerJoin() * yii\db\Query::leftJoin() * yii\db\Query::rightJoin() 例如， ~~~ $query->leftJoin('post', 'post.user_id = user.id'); ~~~ 可以通過多次調用如上所述的連接方法來連接多張表，每連接一張表調用一次。除了連接表以外，你還可以連接子查詢。方法如下，將需要被連接的子查詢指定為一個 yii\db\Query 對象，例如， ~~~ $subQuery = (new \yii\db\Query())->from('post'); $query->leftJoin(['u' => $subQuery], 'u.id = author_id'); ~~~ 在這個例子當中，你應該將子查詢放到一個數組當中，而數組當中的鍵，則為這個子查詢的別名。 yii\db\Query::union() yii\db\Query::union() 方法是用來指定 SQL 語句當中的?`UNION`?子句的。例如， ~~~ ~~~ $query1 = (new \yii\db\Query()) ->select("id, category_id AS type, name") ->from('post') ->limit(10); $query2 = (new \yii\db\Query()) ->select('id, type, name') ->from('user') ->limit(10); $query1->union($query2 ~~~); ~~~ 你可以通過多次調用 yii\db\Query::union() 方法來追加更多的?`UNION`?子句。 ## 查詢方法 yii\db\Query 提供了一整套的用于不同查詢目的的方法。 * yii\db\Query::all(): 將返回一個由行組成的數組，每一行是一個由名稱和值構成的關聯數組（譯者注：省略鍵的數組稱為索引數組）。 * yii\db\Query::one(): 返回結果集的第一行。 * yii\db\Query::column(): 返回結果集的第一列。 * yii\db\Query::scalar(): 返回結果集的第一行第一列的標量值。 * yii\db\Query::exists(): 返回一個表示該查詢是否包結果集的值。 * yii\db\Query::count(): 返回?`COUNT`?查詢的結果。 * 其它集合查詢方法: 包括 yii\db\Query::sum(), yii\db\Query::average(), yii\db\Query::max(), yii\db\Query::min() 等.?`$q`?是一個必選參數，既可以是一個字段名稱，又可以是一個 DB 表達式。例如， ~~~ // SELECT `id`, `email` FROM `user` $rows = (new \yii\db\Query()) ->select(['id', 'email']) ->from('user') ->all(); // SELECT * FROM `user` WHERE `username` LIKE `%test%` $row = (new \yii\db\Query()) ->from('user') ->where(['like', 'username', 'test']) ->one(); ~~~ > 注意：yii\db\Query::one() 方法只返回查詢結果當中的第一條數據，條件語句中不會加上?`LIMIT 1`?條件。如果你清楚的知道查詢將會只返回一行或幾行數據（例如，如果你是通過某些主鍵來查詢的），這很好也提倡這樣做。但是，如果查詢結果有機會返回大量的數據時，那么你應該顯示調用?`limit(1)`?方法，以改善性能。例如，?`(new \yii\db\Query())->from('user')->limit(1)->one()`。所有的這些查詢方法都有一個可選的參數?`$db`, 該參數指代的是 yii\db\Connection，執行一個 DB 查詢時會用到。如果你省略了這個參數，那么?`db`?[application component](http://www.yiichina.com/doc/guide/2.0/structure-application-components)?將會被用作默認的 DB 連接。如下是另外一個使用?`count()`?查詢的例子： ~~~ // 執行 SQL: SELECT COUNT(*) FROM `user` WHERE `last_name`=:last_name $count = (new \yii\db\Query()) ->from('user') ->where(['last_name' => 'Smith']) ->count(); ~~~ 當你調用 yii\db\Query 當中的一個查詢方法的時候，實際上內在的運作機制如下： * 在當前 yii\db\Query 的構造基礎之上，調用 yii\db\QueryBuilder 來生成一條 SQL 語句； * 利用生成的 SQL 語句創建一個 yii\db\Command 對象； * 調用 yii\db\Command 的查詢方法（例如，`queryAll()`）來執行這條 SQL 語句，并檢索數據。有時候，你也許想要測試或者使用一個由 yii\db\Query 對象創建的 SQL 語句。你可以使用以下的代碼來達到目的： ~~~ $command = (new \yii\db\Query()) ->select(['id', 'email']) ->from('user') ->where(['last_name' => 'Smith']) ->limit(10) ->createCommand(); // 打印 SQL 語句 echo $command->sql; // 打印被綁定的參數 print_r($command->params); // 返回查詢結果的所有行 $rows = $command->queryAll(); ~~~ ### 索引查詢結果當你在調用 yii\db\Query::all() 方法時，它將返回一個以連續的整型數值為索引的數組。而有時候你可能希望使用一個特定的字段或者表達式的值來作為索引結果集數組。那么你可以在調用 yii\db\Query::all() 之前使用 yii\db\Query::indexBy() 方法來達到這個目的。例如， ~~~ // 返回 [100 => ['id' => 100, 'username' => '...', ...], 101 => [...], 103 => [...], ...] $query = (new \yii\db\Query()) ->from('user') ->limit(10) ->indexBy('id') ->all(); ~~~ 如需使用表達式的值做為索引，那么只需要傳遞一個匿名函數給 yii\db\Query::indexBy() 方法即可： ~~~ $query = (new \yii\db\Query()) ->from('user') ->indexBy(function ($row) { return $row['id'] . $row['username']; })->all(); ~~~ 該匿名函數將帶有一個包含了當前行的數據的?`$row`?參數，并且返回用作當前行索引的標量值（譯者注：就是簡單的數值或者字符串，而不是其他復雜結構，例如數組）。 ### 批處理查詢當需要處理大數據的時候，像 yii\db\Query::all() 這樣的方法就不太合適了，因為它們會把所有數據都讀取到內存上。為了保持較低的內存需求， Yii 提供了一個所謂的批處理查詢的支持。批處理查詢會利用數據游標將數據以批為單位取出來。批處理查詢的用法如下： ~~~ use yii\db\Query; $query = (new Query()) ->from('user') ->orderBy('id'); foreach ($query->batch() as $users) { // $users 是一個包含100條或小于100條用戶表數據的數組 } // or if you want to iterate the row one by one foreach ($query->each() as $user) { // $user 指代的是用戶表當中的其中一行數據 } ~~~ ~~~ yii\db\Query::batch() 和 yii\db\Query::each() 方法將會返回一個實現了`Iterator`?接口 yii\db\BatchQueryResult 的對象，可以用在`foreach`?結構當中使用。在第一次迭代取數據的時候，數據庫會執行一次 SQL 查詢，然后在剩下的迭代中，將直接從結果集中批量獲取數據。默認情況下，一批的大小為 100，也就意味著一批獲取的數據是 100 行。你可以通過給?`batch()`?或者?`each()`?方法的第一個參數傳值來改變每批行數的大小。相對于 yii\db\Query::all() 方法，批處理查詢每次只讀取 100 行的數據到內存。如果你在處理完這些數據后及時丟棄這些數據，那么批處理查詢可以很好的幫助降低內存的占用率。如果你通過 yii\db\Query::indexBy() 方法為查詢結果指定了索引字段，那么批處理查詢將仍然保持相對應的索引方案，例如， ~~~ `$query = (new \yii\db\Query())` ->from('user') ->indexBy('username'); foreach ($query->batch() as $users) { // $users 的 “username” 字段將會成為索引 } foreach ($query->each() as $username => $user) { }