db-query-builder.md 21.1 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
クエリビルダとクエリ
====================

> Note|注意: この節はまだ執筆中です。

[データベースの基礎](db-dao.md) の節で説明したように、Yii は基本的なデータベースアクセスレイヤを提供します。
このデータベースアクセスレイヤは、データベースと相互作用するための低レベルな方法を提供するものです。
それが有用な状況もありますが、生の SQL を書くことは面倒くさく、間違いを生じやすいものでもあります。
これに取って代る方法の一つがクエリビルダを使用することです。
クエリビルダは、実行すべきクエリを生成するためのオブジェクト指向の手法です。

クエリビルダの典型的な使用例は以下のようなものです。

```php
$rows = (new \yii\db\Query())
    ->select('id, name')
    ->from('user')
    ->limit(10)
    ->all();

// これは下記のコードと等価

$query = (new \yii\db\Query())
    ->select('id, name')
    ->from('user')
    ->limit(10);

// コマンドを作成。$command->sql で実際の SQL を取得できる
$command = $query->createCommand();

// コマンドを実行
$rows = $command->queryAll();
```

クエリメソッド
--------------

ご覧のように、[[yii\db\Query]] が、あなたが扱わねばならない主役のオブジェクトです。
舞台裏では、`Query` は、実際には、さまざまなクエリ情報を表現する役目を負っているに過ぎません。
実際のクエリ構築のロジックは、`createCommand()` コマンドを呼んだときに、[[yii\db\QueryBuilder]] によって実行され、クエリの実行は [[yii\db\Command]] によって実行されます。

便宜上の理由から、[[yii\db\Query]] が、よく使われる一連のクエリメソッド (クエリを構築し、実行して、結果を返すメソッド) を提供しています。
例えば、

- [[yii\db\Query::all()|all()]]: クエリを構築し、実行して、全ての結果を配列として返します。
- [[yii\db\Query::one()|one()]]: 結果の最初の行を返します。
- [[yii\db\Query::column()|column()]]: 結果の最初のカラムを返します。
- [[yii\db\Query::scalar()|scalar()]]: 結果の最初の行の最初のカラムを返します。
- [[yii\db\Query::exists()|exists()]]: 何らかのクエリ結果が有るかどうかを返します。
- [[yii\db\Query::count()|count()]]: `COUNT` クエリの結果を返します。
  他の似たようなメソッドに、`sum($q)``average($q)``max($q)``min($q)` があり、いわゆる統計データクエリをサポートしています。
  これらのメソッドでは `$q` パラメータは必須であり、カラム名または式を取ります。


クエリを構築する
----------------

58 59
以下に、SQL 文の中のさまざまな句を組み立てる方法を説明します。
話を単純にするために、`$query` という変数を使って [[yii\db\Query]] オブジェクトを表すものとします。
60 61 62 63


### `SELECT`

64
基本的な `SELECT` クエリを組み立てるためには、どのテーブルからどのカラムをセレクトするかを指定する必要があります。
65 66 67 68 69 70

```php
$query->select('id, name')
    ->from('user');
```

71
セレクトのオプションは、上記のように、カンマで区切られた文字列で指定することも出来ますが、配列によって指定することも出来ます。
72
配列を使う構文は、セレクトを動的に組み立てる場合に、特に有用です。
73 74 75 76 77 78

```php
$query->select(['id', 'name'])
    ->from('user');
```

79 80 81 82
> Info|情報: `SELECT` 句が SQL 式を含む場合は、常に配列形式を使うべきです。
> これは、`CONCAT(first_name, last_name) AS full_name` のように、SQL 式がカンマを含みうるからです。
> そういう式を他のカラムと一緒に文字列の中に含めると、式がカンマによっていくつかの部分に分離されるおそれがあります。
> それはあなたの意図するところではないでしょう。
83

84 85
カラムを指定するときは、例えば `user.id``user.id AS user_id` などのように、テーブル接頭辞やカラムエイリアスを含めることが出来ます。
カラムを指定するのに配列を使っている場合は、例えば `['user_id' => 'user.id', 'user_name' => 'user.name']` のように、配列のキーを使ってカラムエイリアスを指定することも出来ます。
86

87
バージョン 2.0.1 以降では、サブクエリをカラムとしてセレクトすることも出来ます。例えば、
88 89 90 91
 
```php
$subQuery = (new Query)->select('COUNT(*)')->from('user');
$query = (new Query)->select(['id', 'count' => $subQuery])->from('post');
92
// $query は次の SQL を表現する
93 94 95
// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
```

96
重複行を除外して取得したい場合は、次のように、`distinct()` を呼ぶことが出来ます。
97 98 99 100 101 102 103

```php
$query->select('user_id')->distinct()->from('post');
```

### `FROM`

104
どのテーブルからデータを取得するかを指定するために `from()` を呼びます。
105 106 107 108 109

```php
$query->select('*')->from('user');
```

110 111 112 113
カンマ区切りの文字列または配列を使って、複数のテーブルを指定することが出来ます。
テーブル名は、スキーマ接頭辞 (例えば `'public.user'`)、 および/または、テーブルエイリアス (例えば、`'user u'`) を含んでも構いません。
テーブル名が何らかの括弧を含んでいる場合 (すなわち、テーブルがサブクエリまたは DB 式で与えられていることを意味します) を除いて、メソッドが自動的にテーブル名を引用符で囲みます。
例えば、
114 115 116 117 118

```php
$query->select('u.*, p.*')->from(['user u', 'post p']);
```

119 120 121
テーブルが配列として指定されている場合は、配列のキーをテーブルエイリアスとして使うことも出来ます。
(テーブルにエイリアスが必要でない場合は、文字列のキーを使わないでください。)
例えば、
122 123 124 125 126

```php
$query->select('u.*, p.*')->from(['u' => 'user', 'p' => 'post']);
```

127 128
`Query` オブジェクトを使ってサブクエリを指定することが出来ます。
この場合、対応する配列のキーがサブクエリのエイリアスとして使われます。
129 130 131 132 133 134 135 136 137

```php
$subQuery = (new Query())->select('id')->from('user')->where('status=1');
$query->select('*')->from(['u' => $subQuery]);
```


### `WHERE`

138 139 140
通常、データは何らかの基準に基づいて選択されます。
クエリビルダはその基準を指定するための有用なメソッドをいくつか持っていますが、その中で最も強力なものが `where` です。
これは多様な方法で使うことが出来ます。
141

142
条件を適用するもっとも簡単な方法は文字列を使うことです。
143 144 145 146 147

```php
$query->where('status=:status', [':status' => $status]);
```

148 149
文字列を使うときは、文字列の結合によってクエリを作るのではなく、必ずクエリパラメータをバインドするようにしてください。
上記の手法は使っても安全ですが、下記の手法は安全ではありません。
150 151

```php
152
$query->where("status=$status"); // 危険!
153 154
```

155
`status` の値をただちにバインドするのでなく、`params` または `addParams` を使ってそうすることも出来ます。
156 157 158 159 160 161

```php
$query->where('status=:status');
$query->addParams([':status' => $status]);
```

162
*ハッシュ形式* を使って、複数の条件を同時に `where` にセットすることが出来ます。
163 164 165 166 167 168 169 170 171

```php
$query->where([
    'status' => 10,
    'type' => 2,
    'id' => [4, 8, 15, 16, 23, 42],
]);
```

172
上記のコードは次の SQL を生成します。
173 174 175 176 177

```sql
WHERE (`status` = 10) AND (`type` = 2) AND (`id` IN (4, 8, 15, 16, 23, 42))
```

178
NULL はデータベースでは特別な値です。クエリビルダはこれを賢く処理します。例えば、
179 180 181 182 183

```php
$query->where(['status' => null]);
```

184
これは次の WHERE 句になります。
185 186 187 188 189

```sql
WHERE (`status` IS NULL)
```

190
次のように `Query` オブジェクトを使ってサブクエリを作ることも出来ます。
191 192 193 194 195 196

```php
$userQuery = (new Query)->select('id')->from('user');
$query->where(['id' => $userQuery]);
```

197
これは次の SQL を生成します。
198 199 200 201 202

```sql
WHERE `id` IN (SELECT `id` FROM `user`)
```

203
このメソッドを使うもう一つの方法は、`[演算子, オペランド1, オペランド2, ...]` という形式の引数を使う方法です。
204

205
演算子には、次のどれか一つを使うことが出来ます ([[yii\db\QueryInterface::where()]] も参照してください)。
206

207 208 209 210
- `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)` を生成します。
  このメソッドは、文字列を引用符で囲ったりエスケープしたりしません。
211

212
- `or`: 二つのオペランドが `OR` を使って結合されること以外は `and` 演算子と同じです。
213

214 215
- `between`: オペランド 1 はカラム名、オペランド 2 と 3 はカラムの値が属すべき範囲の開始値と終了値としなければなりません。
  例えば、`['between', 'id', 1, 10]``id BETWEEN 1 AND 10` を生成します。
216

217
- `not between`: 生成される条件において `BETWEEN``NOT BETWEEN` に置き換えられる以外は、`between` と同じです。
218

219 220 221 222 223 224 225 226
- `in`: オペランド 1 はカラム名または DB 式でなければなりません。
  オペランド 2 は、配列または `Query` オブジェクトのどちらかを取ることが出来ます。
  オペランド 2 が配列である場合は、その配列は、カラムまたは DB 式が該当すべき値域を表すものとされます。
  オペランド 2 が `Query` オブジェクトである場合は、サブクエリが生成されて、カラムまたは DB 式の値域として使われます。
  例えば、`['in', 'id', [1, 2, 3]]``id IN (1, 2, 3)` を生成します。
  このメソッドは、カラム名を適切に引用符で囲み、値域の値をエスケープします。
  `in` 演算子はまた複合カラムをもサポートしています。
  その場合、オペランド 1 はカラム名の配列とし、オペランド 2 は配列の配列、または、複合カラムの値域を表す `Query` オブジェクトでなければなりません。
227

228
- `not in`: 生成される条件において `IN``NOT IN` に置き換えられる以外は、`in` と同じです。
229

230 231 232 233 234 235 236 237 238
- `like`: オペランド 1 はカラム名または DB 式、オペランド 2 はカラムまたは DB 式がマッチすべき値を示す文字列または配列でなければなりません。
  例えば、`['like', 'name', 'tester']``name LIKE '%tester%'` を生成します。
  値域が配列として与えられた場合は、複数の `LIKE` 述語が生成されて 'AND' によって結合されます。
  例えば、`['like', 'name', ['test', 'sample']]``name LIKE '%test%' AND name LIKE '%sample%'` を生成します。
  さらに、オプションである三番目のオペランドによって、値の中の特殊文字をエスケープする方法を指定することも出来ます。
  このオペランド 3 は、特殊文字とそのエスケープ結果のマッピングを示す配列でなければなりません。
  このオペランドが提供されない場合は、デフォルトのエスケープマッピングが使用されます。
  `false` または空の配列を使って、値が既にエスケープ済みであり、それ以上エスケープを適用すべきでないことを示すことが出来ます。
  エスケープマッピングを使用する場合 (または第三のオペランドが与えられない場合) は、値が自動的に一組のパーセント記号によって囲まれることに注意してください。
239

240
  > Note|注意: PostgreSQL を使っている場合は、`like` の代りに、大文字と小文字を区別しない比較のための [`ilike`](http://www.postgresql.org/docs/8.3/static/functions-matching.html#FUNCTIONS-LIKE) を使うことも出来ます。
241

242
- `or like`: オペランド 2 が配列である場合に `LIKE` 述語が `OR` によって結合される以外は、`like` 演算子と同じです。
243

244
- `not like`: 生成される条件において `LIKE``NOT LIKE` に置き換えられる以外は、`like` 演算子と同じです。
245

246
- `or not like`: `NOT LIKE` 述語が `OR` によって結合される以外は、`not like` 演算子と同じです。
247

248 249
- `exists`: 要求される一つだけのオペランドは、サブクエリを表す [[yii\db\Query]] のインスタンスでなければなりません。
  これは `EXISTS (sub-query)` という式を構築します。
250

251
- `not exists`: `exists` 演算子と同じで、`NOT EXISTS (sub-query)` という式を構築します。
252

253
これらに加えて、どのようなものでも演算子として指定することが出来ます。
254 255

```php
256 257 258
$query->select('id')
    ->from('user')
    ->where(['>=', 'id', 10]);
259 260
```

261
これは次の結果になります。
262 263 264 265 266

```sql
SELECT id FROM user WHERE id >= 10;
```

267
条件の一部を動的に構築しようとする場合は、`andWhere()``orWhere()` を使うのが非常に便利です。
268 269 270 271 272 273 274 275 276 277 278

```php
$status = 10;
$search = 'yii';

$query->where(['status' => $status]);
if (!empty($search)) {
    $query->andWhere(['like', 'title', $search]);
}
```

279
`$search` が空でない場合は次の SQL が生成されます。
280 281 282 283 284

```sql
WHERE (`status` = 10) AND (`title` LIKE '%yii%')
```

285
#### フィルタの条件を構築する
286

287 288 289 290
ユーザの入力に基づいてフィルタの条件を構築する場合、普通は、「空の入力値」は特別扱いして、フィルタではそれを無視したいものです。
例えば、ユーザ名とメールアドレスの入力欄を持つ HTML フォームがあるとします。
ユーザがユーザ名の入力欄のみに何かを入力した場合は、入力されたユーザ名だけを検索条件とするクエリを作成したいでしょう。
この目的を達するために `filterWhere()` メソッドを使うことが出来ます。
291 292

```php
293
// $username と $email はユーザの入力による
294 295 296 297 298 299
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);
```

300 301 302 303
`filterWhere()` メソッドは `where()` と非常によく似ています。
主な相違点は、`filterWhere()` は与えられた条件から空の値を削除する、ということです。
従って、`$email` が「空」である場合は、結果として生成されるクエリは `...WHERE username=:username` となります。
そして、`$username``$email` が両方とも「空」である場合は、クエリは `WHERE` の部分を持ちません。
304

305
値が *空* であるのは、null、空文字列、空白文字だけの文字列、または、空配列である場合です。
306

307
フィルタの条件を追加するために、`andFilterWhere()``orFilterWhere()` を使うことも出来ます。
308 309 310 311


### `ORDER BY`

312
結果を並び替えるために `orderBy``addOrderBy` を使うことが出来ます。
313 314 315 316 317 318 319 320

```php
$query->orderBy([
    'id' => SORT_ASC,
    'name' => SORT_DESC,
]);
```

321
ここでは `id` の昇順、`name` の降順で並べ替えています。
322

323
### `GROUP BY` と `HAVING`
324

325
生成される SQL に `GROUP BY` を追加するためには、次のようにすることが出来ます。
326 327 328 329 330

```php
$query->groupBy('id, status');
```

331
`groupBy` を使った後に別のフィールドを追加したい場合は、
332 333 334 335 336

```php
$query->addGroupBy(['created_at', 'updated_at']);
```

337 338
`HAVING` 条件を追加したい場合は、それに対応する `having` メソッドおよび `andHaving``orHaving` を使うことが出来ます。
これらのメソッドのパラメータは、`where` メソッドグループのそれと同様です。
339 340 341 342 343

```php
$query->having(['status' => $status]);
```

344
### `LIMIT` と `OFFSET`
345

346
結果を 10 行に限定したいときは、`limit` を使うことが出来ます。
347 348 349 350 351

```php
$query->limit(10);
```

352
最初の 100 行をスキップしたい時は、こうします。
353 354 355 356 357 358 359

```php
$query->offset(100);
```

### `JOIN`

360
適切な結合メソッドを使って、クエリビルダで `JOIN` 句を生成することが出来ます。
361 362 363 364 365

- `innerJoin()`
- `leftJoin()`
- `rightJoin()`

366
次の左外部結合では、二つの関連テーブルから一つのクエリでデータを取得しています。
367 368 369 370 371 372 373

```php
$query->select(['user.name AS author', 'post.title as title'])
    ->from('user')
    ->leftJoin('post', 'post.user_id = user.id');
```

374 375
このコードにおいて、`leftJoin()` メソッドの最初のパラメータは、結合するテーブルを指定するものです。
第二のパラメータは、結合の条件を定義しています。
376

377
データベース製品がその他の結合タイプをサポートしている場合は、汎用の `join` メソッドによってそれを使うことが出来ます。
378 379 380 381 382

```php
$query->join('FULL OUTER JOIN', 'post', 'post.user_id = user.id');
```

383
最初のパラメータが実行する結合タイプです。第二は結合するテーブル、第三は結合の条件です。
384

385 386 387 388
`FROM` と同様に、サブクエリを結合することも出来ます。
そのためには、一つの要素を持つ配列としてサブクエリを指定します。
配列の値はサブクエリを表す `Query` オブジェクトとし、配列のキーはサブクエリのエイリアスとしなければなりません。
例えば、
389 390 391 392 393 394 395 396

```php
$query->leftJoin(['u' => $subQuery], 'u.id=author_id');
```


### `UNION`

397 398 399
SQL における `UNION` は、一つのクエリの結果を別のクエリの結果に追加するものです。
両方のクエリによって返されるカラムが一致していなければなりません。
Yii においてこれを構築するためには、最初に二つのクエリオブジェクトを作成し、次に `union` メソッドを使って連結します。
400 401 402 403 404 405 406 407 408 409 410 411

```php
$query = new Query();
$query->select("id, category_id as type, name")->from('post')->limit(10);

$anotherQuery = new Query();
$anotherQuery->select('id, type, name')->from('user')->limit(10);

$query->union($anotherQuery);
```


412 413
バッチクエリ
------------
414

415 416 417 418
大量のデータを扱う場合は、[[yii\db\Query::all()]] のようなメソッドは適していません。
なぜなら、それらのメソッドは、全てのデータをメモリ上に読み込むことを必要とするためです。
必要なメモリ量を低く抑えるために、Yii はいわゆるバッチクエリのサポートを提供しています。
バッチクエリはデータカーソルを利用して、バッチモードでデータを取得します。
419

420
バッチクエリは次のようにして使うことが出来ます。
421 422 423 424 425 426 427 428 429

```php
use yii\db\Query;

$query = (new Query())
    ->from('user')
    ->orderBy('id');

foreach ($query->batch() as $users) {
430
    // $users は user テーブルから取得した 100 以下の行の配列
431 432
}

433
// または、一行ずつ反復したい場合は
434
foreach ($query->each() as $user) {
435
    // $user は user テーブルから取得した一つの行を表す
436 437 438
}
```

439 440 441 442 443
[[yii\db\Query::batch()]] メソッドと [[yii\db\Query::each()]] メソッドは [[yii\db\BatchQueryResult]] オブジェクトを返します。
このオブジェクトは `Iterator` インタフェイスを実装しており、従って、`foreach` 構文の中で使うことが出来ます。
初回の反復の際に、データベースに対する SQL クエリが作成されます。データは、その後、反復のたびにバッチモードで取得されます。
既定では、バッチサイズは 100 であり、各バッチにおいて 100 行のデータが取得されます。
`batch()` または `each()` メソッドに最初のパラメータを渡すことによって、バッチサイズを変更することが出来ます。
444

445 446
[[yii\db\Query::all()]] とは対照的に、バッチクエリは一度に 100 行のデータしかメモリに読み込みません。
データを処理した後、すぐにデータを破棄するようにすれば、バッチクエリの助けを借りてメモリ消費量を限度以下に抑えることが出来ます。
447

448 449
[[yii\db\Query::indexBy()]] によってクエリ結果をあるカラムでインデックスするように指定している場合でも、バッチクエリは正しいインデックスを保持します。
例えば、
450 451 452 453 454 455 456 457 458

```php
use yii\db\Query;

$query = (new Query())
    ->from('user')
    ->indexBy('username');

foreach ($query->batch() as $users) {
459
    // $users は "username" カラムでインデックスされている
460 461 462 463 464
}

foreach ($query->each() as $username => $user) {
}
```