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

4
Yii provides a basic database access layer as described in the [Database basics](database-basics.md) section. The database access layer provides a low-level way to interact with the database. While useful in some situations, it can be tedious to rely too much upon direct SQL. An alternative approach that Yii provides is the Query Builder. The Query Builder provides an object-oriented vehicle for generating queries to be executed.
Alexander Makarov committed
5

6
Here's a basic example:
Alexander Makarov committed
7 8 9 10

```php
$query = new Query;

11
// Define the query:
Alexander Makarov committed
12
$query->select('id, name')
13 14
	->from('tbl_user')
	->limit(10);
Alexander Makarov committed
15

16
// Create a command. 
Alexander Makarov committed
17
$command = $query->createCommand();
18 19 20
// You can get the actual SQL using $command->sql

// Execute the command:
Alexander Makarov committed
21 22 23
$rows = $command->queryAll();
```

24 25
Basic selects
-------------
Alexander Makarov committed
26

27
In order to form a basic `SELECT` query, you need to specify what columns to select and from what table:
Alexander Makarov committed
28 29 30 31 32 33

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

34
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
35 36

```php
37 38 39 40 41
$columns = [];
$columns[] = 'id';
$columns[] = 'name';
$query->select($columns)
	->from('tbl_user');
Alexander Makarov committed
42 43
```

44 45
Joins
-----
Alexander Makarov committed
46

47
Joins are generated in the Query Builder by using the applicable join method:
Alexander Makarov committed
48 49 50 51 52

- `innerJoin`
- `leftJoin`
- `rightJoin`

53 54 55 56 57 58 59 60 61 62 63
This left join selects data from two related tables in one query:

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

In the code, the `leftJion` method's first parameter
specifies the table to join to. The second paramter defines the join condition.

If your database application supports other join types, you can use those via the  generic `join` method:
Alexander Makarov committed
64 65 66 67 68

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

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

Specifying SELECT conditions
Alexander Makarov committed
72 73
---------------------

74
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
75

76
The simplest way to apply a condition is to use a string:
Alexander Makarov committed
77 78

```php
Alexander Makarov committed
79
$query->where('status=:status', [':status' => $status]);
Alexander Makarov committed
80 81
```

82
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
83

84 85 86 87 88
```php
$query->where("status=$status"); // Dangerous!
```

Instead of binding the status value immediately, you can do so using `params` or `addParams`:
Alexander Makarov committed
89 90 91

```php
$query->where('status=:status');
Alexander Makarov committed
92
$query->addParams([':status' => $status]);
Alexander Makarov committed
93 94
```

95
Multiple conditions can simultaneously be set in `where` using the *hash format*:
Alexander Makarov committed
96 97

```php
Alexander Makarov committed
98
$query->where([
Alexander Makarov committed
99 100
	'status' => 10,
	'type' => 2,
Alexander Makarov committed
101 102
	'id' => [4, 8, 15, 16, 23, 42],
]);
Alexander Makarov committed
103 104
```

105
That code will generate the following SQL:
Alexander Makarov committed
106 107 108 109 110

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

111
NULL is a special value in databases, and is handled smartly by the Query Builder. This code:
Alexander Makarov committed
112 113

```php
Alexander Makarov committed
114
$query->where(['status' => null]);
Alexander Makarov committed
115 116
```

117
results in this WHERE clause:
Alexander Makarov committed
118 119 120 121 122

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

Alexander Makarov committed
123
Another way to use the method is the operand format which is `[operator, operand1, operand2, ...]`.
Alexander Makarov committed
124 125 126 127

Operator can be one of the following:

- `and`: the operands should be concatenated together using `AND`. For example,
Alexander Makarov committed
128
  `['and', 'id=1', 'id=2']` will generate `id=1 AND id=2`. If an operand is an array,
Alexander Makarov committed
129
  it will be converted into a string using the rules described here. For example,
Alexander Makarov committed
130
  `['and', 'type=1', ['or', 'id=1', 'id=2']]` will generate `type=1 AND (id=1 OR id=2)`.
Alexander Makarov committed
131 132 133 134
  The method will NOT do any quoting or escaping.
- `or`: similar to the `and` operator except that the operands are concatenated using `OR`.
- `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
135
   For example, `['between', 'id', 1, 10]` will generate `id BETWEEN 1 AND 10`.
Alexander Makarov committed
136 137 138 139
- `not between`: similar to `between` except the `BETWEEN` is replaced with `NOT BETWEEN`
  in the generated condition.
- `in`: operand 1 should be a column or DB expression, and operand 2 be an array representing
  the range of the values that the column or DB expression should be in. For example,
Alexander Makarov committed
140
  `['in', 'id', [1, 2, 3]]` will generate `id IN (1, 2, 3)`.
Alexander Makarov committed
141 142 143 144
  The method will properly quote the column name and escape values in the range.
- `not in`: similar to the `in` operator except that `IN` is replaced with `NOT IN` in the generated condition.
- `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.
145
  For example, `['like', 'name', 'tester']` will generate `name LIKE '%tester%'`.
Alexander Makarov committed
146
  When the value range is given as an array, multiple `LIKE` predicates will be generated and concatenated
147
  using `AND`. For example, `['like', 'name', ['test', 'sample']]` will generate
Alexander Makarov committed
148
  `name LIKE '%test%' AND name LIKE '%sample%'`.
149 150 151 152 153 154
  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.
Alexander Makarov committed
155 156 157 158 159 160
- `or like`: similar to the `like` operator except that `OR` is used to concatenate the `LIKE`
  predicates when operand 2 is an array.
- `not like`: similar to the `like` operator except that `LIKE` is replaced with `NOT LIKE`
  in the generated condition.
- `or not like`: similar to the `not like` operator except that `OR` is used to concatenate
  the `NOT LIKE` predicates.
161
- `exists`: requires one operand which must be an instance of [[yii\db\Query]] representing the sub-query.
162 163
  It will build a `EXISTS (sub-query)` expression.
- `not exists`: similar to the `exists` operator and builds a `NOT EXISTS (sub-query)` expression.
Alexander Makarov committed
164 165 166 167 168 169 170

If you are building parts of condition dynamically it's very convenient to use `andWhere` and `orWhere`:

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

Alexander Makarov committed
171
$query->where(['status' => $status]);
Alexander Makarov committed
172
if (!empty($search)) {
Carsten Brandt committed
173
	$query->andWhere(['like', 'title', $search]);
Alexander Makarov committed
174 175 176 177 178 179 180 181 182 183 184 185
}
```

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

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

Order
-----

186
For ordering results `orderBy` and `addOrderBy` could be used:
Alexander Makarov committed
187 188

```php
Alexander Makarov committed
189
$query->orderBy([
190 191
	'id' => SORT_ASC,
	'name' => SORT_DESC,
Alexander Makarov committed
192
]);
Alexander Makarov committed
193 194 195 196
```

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

197 198 199 200 201 202 203 204 205
Distinct
--------

If you want to get IDs of all users with posts you can use `DISTINCT`. With query builder it will look like the following:

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

Alexander Makarov committed
206 207 208 209 210 211 212 213 214 215 216 217
Group and Having
----------------

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
218
$query->addGroupBy(['created_at', 'updated_at']);
Alexander Makarov committed
219 220 221 222 223 224
```

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
225
$query->having(['status' => $status]);
Alexander Makarov committed
226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253
```

Limit and offset
----------------

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

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

To skip 100 fist rows use:

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

Union
-----

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

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

$anotherQuery = new Query;
Alexander Makarov committed
254
$anotherQuery->select('id, 'user' as type, name')->from('tbl_user')->limit(10);
Alexander Makarov committed
255 256 257 258

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