db-query-builder.md 16.2 KB
Newer Older
Alexander Makarov committed
1 2 3
Query Builder and Query
=======================

4
> Note: This section is under development.
Qiang Xue committed
5

6
Yii provides a basic database access layer as described in the [Database basics](db-dao.md) section.
7 8 9
The database access layer provides a low-level way to interact with the database. While useful in some situations,
it can be tedious and error-prone to write raw SQLs. An alternative approach is to use the Query Builder.
The Query Builder provides an object-oriented vehicle for generating queries to be executed.
Alexander Makarov committed
10

11
A typical usage of the query builder looks like the following:
Alexander Makarov committed
12 13

```php
14
$rows = (new \yii\db\Query())
15
    ->select('id, name')
16
    ->from('user')
17 18
    ->limit(10)
    ->all();
19 20

// which is equivalent to the following code:
Alexander Makarov committed
21

22
$query = (new \yii\db\Query())
23
    ->select('id, name')
24
    ->from('user')
25
    ->limit(10);
Alexander Makarov committed
26

27
// Create a command. You can get the actual SQL using $command->sql
Alexander Makarov committed
28
$command = $query->createCommand();
29 30

// Execute the command:
Alexander Makarov committed
31 32 33
$rows = $command->queryAll();
```

34 35 36
Query Methods
-------------

37
As you can see, [[yii\db\Query]] is the main player that you need to deal with. Behind the scenes,
38 39 40 41 42 43 44 45 46 47 48 49 50
`Query` is actually only responsible for representing various query information. The actual query
building logic is done by [[yii\db\QueryBuilder]] when you call the `createCommand()` method,
and the query execution is done by [[yii\db\Command]].

For convenience, [[yii\db\Query]] provides a set of commonly used query methods that will build
the query, execute it, and return the result. For example,

- [[yii\db\Query::all()|all()]]: builds the query, executes it and returns all results as an array.
- [[yii\db\Query::one()|one()]]: returns the first row of the result.
- [[yii\db\Query::column()|column()]]: returns the first column of the result.
- [[yii\db\Query::scalar()|scalar()]]: returns the first column in the first row of the result.
- [[yii\db\Query::exists()|exists()]]: returns a value indicating whether the query results in anything.
- [[yii\db\Query::count()|count()]]: returns the result of a `COUNT` query. Other similar methods
51
  include `sum($q)`, `average($q)`, `max($q)` and `min($q)`, which support the so-called aggregational data query. The `$q`
52
  parameter is mandatory for these methods and can be either the column name or expression.
53 54 55 56 57


Building Query
--------------

58 59 60 61
In the following, we will explain how to build various clauses in a SQL statement. For simplicity,
we use `$query` to represent a [[yii\db\Query]] object.


62
### `SELECT`
Alexander Makarov committed
63

64
In order to form a basic `SELECT` query, you need to specify what columns to select and from what table:
Alexander Makarov committed
65 66 67

```php
$query->select('id, name')
68
    ->from('user');
Alexander Makarov committed
69 70
```

71 72
Select options can be specified as a comma-separated string, as in the above, or as an array.
The array syntax is especially useful when forming the selection dynamically:
Alexander Makarov committed
73 74

```php
75
$query->select(['id', 'name'])
76
    ->from('user');
Alexander Makarov committed
77 78
```

79 80 81 82 83
> Info: You should always use the array format if your `SELECT` clause contains SQL expressions.
> This is because a SQL expression like `CONCAT(first_name, last_name) AS full_name` may contain commas.
> If you list it together with other columns in a string, the expression may be split into several parts
> by commas, which is not what you want to see.

84
When specifying columns, you may include the table prefixes or column aliases, e.g., `user.id`, `user.id AS user_id`.
85
If you are using an array to specify the columns, you may also use the array keys to specify the column aliases,
86
e.g., `['user_id' => 'user.id', 'user_name' => 'user.name']`.
87

88 89 90 91 92 93 94 95 96
Starting from version 2.0.1, you may also select sub-queries as columns. For example,
 
```php
$subQuery = (new Query)->select('COUNT(*)')->from('user');
$query = (new Query)->select(['id', 'count' => $subQuery])->from('post');
// $query represents the following SQL:
// SELECT `id`, (SELECT COUNT(*) FROM `user`) AS `count` FROM `post`
```

97 98 99
To select distinct rows, you may call `distinct()`, like the following:

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

103
### `FROM`
104 105 106 107

To specify which table(s) to select data from, call `from()`:

```php
108
$query->select('*')->from('user');
109 110 111
```

You may specify multiple tables using a comma-separated string or an array.
112
Table names can contain schema prefixes (e.g. `'public.user'`) and/or table aliases (e.g. `'user u'`).
113 114 115 116
The method will automatically quote the table names unless it contains some parenthesis
(which means the table is given as a sub-query or DB expression). For example,

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

When the tables are specified as an array, you may also use the array keys as the table aliases
121
(if a table does not need an alias, do not use a string key). For example,
122 123

```php
maks feltrin committed
124
$query->select('u.*, p.*')->from(['u' => 'user', 'p' => 'post']);
125 126 127 128
```

You may specify a sub-query using a `Query` object. In this case, the corresponding array key will be used
as the alias for the sub-query.
Qiang Xue committed
129

130
```php
131
$subQuery = (new Query())->select('id')->from('user')->where('status=1');
132 133
$query->select('*')->from(['u' => $subQuery]);
```
Qiang Xue committed
134

135

136
### `WHERE`
Alexander Makarov committed
137

138
Usually data is selected based upon certain criteria. Query Builder has some useful methods to specify these, the most powerful of which being `where`. It can be used in multiple ways.
Alexander Makarov committed
139

140
The simplest way to apply a condition is to use a string:
Alexander Makarov committed
141 142

```php
Alexander Makarov committed
143
$query->where('status=:status', [':status' => $status]);
Alexander Makarov committed
144 145
```

146
When using strings, make sure you're binding the query parameters, not creating a query by string concatenation. The above approach is safe to use, the following is not:
Alexander Makarov committed
147

148 149 150 151 152
```php
$query->where("status=$status"); // Dangerous!
```

Instead of binding the status value immediately, you can do so using `params` or `addParams`:
Alexander Makarov committed
153 154 155

```php
$query->where('status=:status');
Alexander Makarov committed
156
$query->addParams([':status' => $status]);
Alexander Makarov committed
157 158
```

159
Multiple conditions can simultaneously be set in `where` using the *hash format*:
Alexander Makarov committed
160 161

```php
Alexander Makarov committed
162
$query->where([
163 164 165
    'status' => 10,
    'type' => 2,
    'id' => [4, 8, 15, 16, 23, 42],
Alexander Makarov committed
166
]);
Alexander Makarov committed
167 168
```

169
That code will generate the following SQL:
Alexander Makarov committed
170 171 172 173 174

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

175
NULL is a special value in databases, and is handled smartly by the Query Builder. This code:
Alexander Makarov committed
176 177

```php
Alexander Makarov committed
178
$query->where(['status' => null]);
Alexander Makarov committed
179 180
```

181
results in this WHERE clause:
Alexander Makarov committed
182 183 184 185 186

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

187 188 189 190 191 192
If you need `IS NOT NULL` you can use the following:

```php
$query->where(['not', ['col' => null]]);
```

193 194 195 196 197 198 199 200 201 202 203 204 205 206
You can also create sub-queries with `Query` objects like the following,

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

which will generate the following SQL:

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


Alexander Makarov committed
207
Another way to use the method is the operand format which is `[operator, operand1, operand2, ...]`.
Alexander Makarov committed
208

209
Operator can be one of the following (see also [[yii\db\QueryInterface::where()]]):
Alexander Makarov committed
210 211

- `and`: the operands should be concatenated together using `AND`. For example,
Alexander Makarov committed
212
  `['and', 'id=1', 'id=2']` will generate `id=1 AND id=2`. If an operand is an array,
Alexander Makarov committed
213
  it will be converted into a string using the rules described here. For example,
Alexander Makarov committed
214
  `['and', 'type=1', ['or', 'id=1', 'id=2']]` will generate `type=1 AND (id=1 OR id=2)`.
Alexander Makarov committed
215
  The method will NOT do any quoting or escaping.
216

Alexander Makarov committed
217
- `or`: similar to the `and` operator except that the operands are concatenated using `OR`.
218

Alexander Makarov committed
219 220
- `between`: operand 1 should be the column name, and operand 2 and 3 should be the
   starting and ending values of the range that the column is in.
Alexander Makarov committed
221
   For example, `['between', 'id', 1, 10]` will generate `id BETWEEN 1 AND 10`.
222

Alexander Makarov committed
223 224
- `not between`: similar to `between` except the `BETWEEN` is replaced with `NOT BETWEEN`
  in the generated condition.
225

226 227 228 229
- `in`: operand 1 should be a column or DB expression. Operand 2 can be either an array or a `Query` object.
  It will generate an `IN` condition. If Operand 2 is an array, it will represent the range of the values
  that the column or DB expression should be; If Operand 2 is a `Query` object, a sub-query will be generated
  and used as the range of the column or DB expression. For example,
Alexander Makarov committed
230
  `['in', 'id', [1, 2, 3]]` will generate `id IN (1, 2, 3)`.
Alexander Makarov committed
231
  The method will properly quote the column name and escape values in the range.
232 233
  The `in` operator also supports composite columns. In this case, operand 1 should be an array of the columns,
  while operand 2 should be an array of arrays or a `Query` object representing the range of the columns.
234

Alexander Makarov committed
235
- `not in`: similar to the `in` operator except that `IN` is replaced with `NOT IN` in the generated condition.
236

Alexander Makarov committed
237 238
- `like`: operand 1 should be a column or DB expression, and operand 2 be a string or an array representing
  the values that the column or DB expression should be like.
239
  For example, `['like', 'name', 'tester']` will generate `name LIKE '%tester%'`.
Alexander Makarov committed
240
  When the value range is given as an array, multiple `LIKE` predicates will be generated and concatenated
241
  using `AND`. For example, `['like', 'name', ['test', 'sample']]` will generate
Alexander Makarov committed
242
  `name LIKE '%test%' AND name LIKE '%sample%'`.
243 244 245 246 247 248
  You may also provide an optional third operand to specify how to escape special characters in the values.
  The operand should be an array of mappings from the special characters to their
  escaped counterparts. If this operand is not provided, a default escape mapping will be used.
  You may use `false` or an empty array to indicate the values are already escaped and no escape
  should be applied. Note that when using an escape mapping (or the third operand is not provided),
  the values will be automatically enclosed within a pair of percentage characters.
249 250 251 252

  > Note: When using PostgreSQL you may also use [`ilike`](http://www.postgresql.org/docs/8.3/static/functions-matching.html#FUNCTIONS-LIKE)
  > instead of `like` for case-insensitive matching.

Alexander Makarov committed
253 254
- `or like`: similar to the `like` operator except that `OR` is used to concatenate the `LIKE`
  predicates when operand 2 is an array.
255

Alexander Makarov committed
256 257
- `not like`: similar to the `like` operator except that `LIKE` is replaced with `NOT LIKE`
  in the generated condition.
258

Alexander Makarov committed
259 260
- `or not like`: similar to the `not like` operator except that `OR` is used to concatenate
  the `NOT LIKE` predicates.
261

262
- `exists`: requires one operand which must be an instance of [[yii\db\Query]] representing the sub-query.
263
  It will build a `EXISTS (sub-query)` expression.
264

265
- `not exists`: similar to the `exists` operator and builds a `NOT EXISTS (sub-query)` expression.
Alexander Makarov committed
266

267 268 269
Additionally you can specify anything as operator:

```php
270 271 272
$query->select('id')
    ->from('user')
    ->where(['>=', 'id', 10]);
273 274 275 276 277
```

It will result in:

```sql
278
SELECT id FROM user WHERE id >= 10;
279 280
```

Qiang Xue committed
281
If you are building parts of condition dynamically it's very convenient to use `andWhere()` and `orWhere()`:
Alexander Makarov committed
282 283 284 285 286

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

Alexander Makarov committed
287
$query->where(['status' => $status]);
Alexander Makarov committed
288
if (!empty($search)) {
289
    $query->andWhere(['like', 'title', $search]);
Alexander Makarov committed
290 291 292 293 294 295 296 297 298
}
```

In case `$search` isn't empty the following SQL will be generated:

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

Qiang Xue committed
299 300 301 302 303
#### Building Filter Conditions

When building filter conditions based on user inputs, you usually want to specially handle "empty inputs"
by ignoring them in the filters. For example, you have an HTML form that takes username and email inputs.
If the user only enters something in the username input, you may want to build a query that only tries to
304
match the entered username. You may use the `filterWhere()` method to achieve this goal:
Qiang Xue committed
305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323

```php
// $username and $email are from user inputs
$query->filterWhere([
    'username' => $username,
    'email' => $email,
]);
```

The `filterWhere()` method is very similar to `where()`. The main difference is that `filterWhere()`
will remove empty values from the provided condition. So if `$email` is "empty", the resulting query
will be `...WHERE username=:username`; and if both `$username` and `$email` are "empty", the query
will have no `WHERE` part.

A value is *empty* if it is null, an empty string, a string consisting of whitespaces, or an empty array.

You may also use `andFilterWhere()` and `orFilterWhere()` to append more filter conditions.


324
### `ORDER BY`
Alexander Makarov committed
325

326
For ordering results `orderBy` and `addOrderBy` could be used:
Alexander Makarov committed
327 328

```php
Alexander Makarov committed
329
$query->orderBy([
330 331
    'id' => SORT_ASC,
    'name' => SORT_DESC,
Alexander Makarov committed
332
]);
Alexander Makarov committed
333 334 335 336
```

Here we are ordering by `id` ascending and then by `name` descending.

337
### `GROUP BY` and `HAVING`
Alexander Makarov committed
338 339 340 341 342 343 344 345 346 347

In order to add `GROUP BY` to generated SQL you can use the following:

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

If you want to add another field after using `groupBy`:

```php
Alexander Makarov committed
348
$query->addGroupBy(['created_at', 'updated_at']);
Alexander Makarov committed
349 350 351 352 353 354
```

To add a `HAVING` condition the corresponding `having` method and its `andHaving` and `orHaving` can be used. Parameters
for these are similar to the ones for `where` methods group:

```php
Alexander Makarov committed
355
$query->having(['status' => $status]);
Alexander Makarov committed
356 357
```

358
### `LIMIT` and `OFFSET`
Alexander Makarov committed
359 360 361 362 363 364 365 366 367 368 369 370 371

To limit result to 10 rows `limit` can be used:

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

To skip 100 fist rows use:

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

372 373 374 375 376 377 378 379 380 381 382
### `JOIN`

The `JOIN` clauses are generated in the Query Builder by using the applicable join method:

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

This left join selects data from two related tables in one query:

```php
383 384 385
$query->select(['user.name AS author', 'post.title as title'])
    ->from('user')
    ->leftJoin('post', 'post.user_id = user.id');
386 387 388 389 390 391 392 393
```

In the code, the `leftJoin()` method's first parameter
specifies the table to join to. The second parameter defines the join condition.

If your database application supports other join types, you can use those via the  generic `join` method:

```php
394
$query->join('FULL OUTER JOIN', 'post', 'post.user_id = user.id');
395 396 397 398 399 400 401 402 403 404 405 406 407 408
```

The first argument is the join type to perform. The second is the table to join to, and the third is the condition.

Like `FROM`, you may also join with sub-queries. To do so, specify the sub-query as an array
which must contain one element. The array value must be a `Query` object representing the sub-query,
while the array key is the alias for the sub-query. For example,

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


### `UNION`
Alexander Makarov committed
409 410

`UNION` in SQL adds results of one query to results of another query. Columns returned by both queries should match.
411
In Yii in order to build it you can first form two query objects and then use the `union` method:
Alexander Makarov committed
412 413

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

417
$anotherQuery = new Query();
418
$anotherQuery->select('id, type, name')->from('user')->limit(10);
Alexander Makarov committed
419 420 421 422

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

423 424 425 426

Batch Query
-----------

427
When working with large amounts of data, methods such as [[yii\db\Query::all()]] are not suitable
428
because they require loading all data into the memory. To keep the memory requirement low, Yii
429
provides the so-called batch query support. A batch query makes uses of the data cursor and fetches
430 431 432 433 434 435 436
data in batches.

Batch query can be used like the following:

```php
use yii\db\Query;

437
$query = (new Query())
438
    ->from('user')
439
    ->orderBy('id');
440

Qiang Xue committed
441
foreach ($query->batch() as $users) {
442
    // $users is an array of 100 or fewer rows from the user table
443 444
}

Qiang Xue committed
445
// or if you want to iterate the row one by one
446
foreach ($query->each() as $user) {
447
    // $user represents one row of data from the user table
448 449
}
```
450

Qiang Xue committed
451 452
The method [[yii\db\Query::batch()]] and [[yii\db\Query::each()]] return an [[yii\db\BatchQueryResult]] object
which implements the `Iterator` interface and thus can be used in the `foreach` construct.
453 454
During the first iteration, a SQL query is made to the database. Data are then fetched in batches
in the remaining iterations. By default, the batch size is 100, meaning 100 rows of data are being fetched in each batch.
Qiang Xue committed
455 456 457
You can change the batch size by passing the first parameter to the `batch()` or `each()` method.

Compared to the [[yii\db\Query::all()]], the batch query only loads 100 rows of data at a time into the memory.
458
If you process the data and then discard it right away, the batch query can help reduce memory usage.
Qiang Xue committed
459

460 461 462 463 464 465
If you specify the query result to be indexed by some column via [[yii\db\Query::indexBy()]], the batch query
will still keep the proper index. For example,

```php
use yii\db\Query;

466
$query = (new Query())
467
    ->from('user')
468
    ->indexBy('username');
469

Qiang Xue committed
470
foreach ($query->batch() as $users) {
471
    // $users is indexed by the "username" column
472 473 474 475 476
}

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