active-record.md 33 KB
Newer Older
Alexander Makarov committed
1 2 3
Active Record
=============

Larry Ullman committed
4
Active Record implements the [Active Record design pattern](http://en.wikipedia.org/wiki/Active_record).
5
The premise behind Active Record is that an individual [[yii\db\ActiveRecord|ActiveRecord]] object is associated with a specific row in a database table. The object's attributes are mapped to the columns of the corresponding table. Referencing an Active Record attribute is equivalent to accessing
6
the corresponding table column for that record.
Larry Ullman committed
7

Larry Ullman committed
8
As an example, say that the `Customer` ActiveRecord class is associated with the
Larry Ullman committed
9
`tbl_customer` table. This would mean that the class's `name` attribute is automatically mapped to the `name` column in `tbl_customer`.
Larry Ullman committed
10
Thanks to Active Record, assuming the variable `$customer` is an object of type `Customer`, to get the value of the `name` column for the table row, you can use the expression `$customer->name`. In this example, Active Record is providing an object-oriented interface for accessing data stored in the database. But Active Record provides much more functionality than this.
Larry Ullman committed
11

12
With Active Record, instead of writing raw SQL statements to perform database queries, you can call intuitive methods to achieve the same goals. For example, calling [[yii\db\ActiveRecord::save()|save()]] would perform an INSERT or UPDATE query, creating or updating a row in the associated table of the ActiveRecord class:
Alexander Makarov committed
13 14 15 16

```php
$customer = new Customer();
$customer->name = 'Qiang';
Qiang Xue committed
17
$customer->save();  // a new row is inserted into tbl_customer
Alexander Makarov committed
18 19 20 21 22 23
```


Declaring ActiveRecord Classes
------------------------------

24
To declare an ActiveRecord class you need to extend [[yii\db\ActiveRecord]] and
Larry Ullman committed
25
implement the `tableName` method:
Alexander Makarov committed
26 27

```php
Qiang Xue committed
28 29 30
use yii\db\ActiveRecord;

class Customer extends ActiveRecord
Alexander Makarov committed
31 32 33 34 35 36 37 38 39 40 41
{
	/**
	 * @return string the name of the table associated with this ActiveRecord class.
	 */
	public static function tableName()
	{
		return 'tbl_customer';
	}
}
```

Larry Ullman committed
42 43 44 45 46 47 48
The `tableName` method only has to return the name of the database table associated with the class.

Class instances are obtained in one of two ways:

* Using the `new` operator to create a new, empty object
* Using a method to fetch an existing record (or records) from the database

Larry Ullman committed
49
Connecting to the Database
Alexander Makarov committed
50 51
----------------------

Carsten Brandt committed
52
ActiveRecord relies on a [[yii\db\Connection|DB connection]] to perform the underlying DB operations.
Larry Ullman committed
53
By default, ActiveRecord assumes that there is an application component named `db` which provides the needed
Carsten Brandt committed
54
[[yii\db\Connection]] instance. Usually this component is configured in application configuration file:
Alexander Makarov committed
55 56

```php
Alexander Makarov committed
57 58 59
return [
	'components' => [
		'db' => [
Alexander Makarov committed
60 61 62 63
			'class' => 'yii\db\Connection',
			'dsn' => 'mysql:host=localhost;dbname=testdb',
			'username' => 'demo',
			'password' => 'demo',
Alexander Makarov committed
64 65 66
		],
	],
];
Alexander Makarov committed
67 68
```

Larry Ullman committed
69
Please read the [Database basics](database-basics.md) section to learn more on how to configure and use database connections.
Alexander Makarov committed
70

Larry Ullman committed
71
Querying Data from the Database
Qiang Xue committed
72
---------------------------
Alexander Makarov committed
73

Qiang Xue committed
74
There are two ActiveRecord methods for querying data from database:
Alexander Makarov committed
75

76 77
 - [[yii\db\ActiveRecord::find()]]
 - [[yii\db\ActiveRecord::findBySql()]]
Alexander Makarov committed
78

Carsten Brandt committed
79
Both methods return an [[yii\db\ActiveQuery]] instance, which extends [[yii\db\Query]], and thus supports the same set of flexible and powerful DB query methods. The following examples demonstrate some of the possibilities.
Alexander Makarov committed
80 81 82 83

```php
// to retrieve all *active* customers and order them by their ID:
$customers = Customer::find()
Alexander Makarov committed
84
	->where(['status' => $active])
Alexander Makarov committed
85 86 87 88
	->orderBy('id')
	->all();

// to return a single customer whose ID is 1:
Qiang Xue committed
89 90 91
$customer = Customer::find(1);

// the above code is equivalent to the following:
Alexander Makarov committed
92
$customer = Customer::find()
Alexander Makarov committed
93
	->where(['id' => 1])
Alexander Makarov committed
94 95 96 97 98 99 100 101
	->one();

// to retrieve customers using a raw SQL statement:
$sql = 'SELECT * FROM tbl_customer';
$customers = Customer::findBySql($sql)->all();

// to return the number of *active* customers:
$count = Customer::find()
Alexander Makarov committed
102
	->where(['status' => $active])
Alexander Makarov committed
103 104 105
	->count();

// to return customers in terms of arrays rather than `Customer` objects:
Qiang Xue committed
106 107 108 109
$customers = Customer::find()
	->asArray()
	->all();
// each element of $customers is an array of name-value pairs
Alexander Makarov committed
110 111 112 113 114 115

// to index the result by customer IDs:
$customers = Customer::find()->indexBy('id')->all();
// $customers array is indexed by customer IDs
```

116 117 118 119
Batch query is also supported when working with Active Record. For example,

```php
// fetch 10 customers at a time
Qiang Xue committed
120
foreach (Customer::find()->batch(10) as $customers) {
121 122
	// $customers is an array of 10 or fewer Customer objects
}
Qiang Xue committed
123 124
// fetch 10 customers at a time and iterate them one by one
foreach (Customer::find()->each(10) as $customer) {
125 126 127
	// $customer is a Customer object
}
// batch query with eager loading
Qiang Xue committed
128
foreach (Customer::find()->with('orders')->each() as $customer) {
129 130 131 132 133 134
}
```

As explained in [Query Builder](query-builder.md), batch query is very useful when you are fetching
a large amount of data from database. It will keep your memory usage under a limit.

Alexander Makarov committed
135 136 137 138

Accessing Column Data
---------------------

Larry Ullman committed
139
ActiveRecord maps each column of the corresponding database table row to an attribute in the ActiveRecord
140
object. The attribute behaves like any regular object public property. The attribute's name will be the same as the corresponding column name, and is case-sensitive.
Alexander Makarov committed
141

Larry Ullman committed
142
To read the value of a column, you can use the following syntax:
Alexander Makarov committed
143 144

```php
Larry Ullman committed
145
// "id" and "email" are the names of columns in the table associated with $customer ActiveRecord object
Alexander Makarov committed
146
$id = $customer->id;
Larry Ullman committed
147
$email = $customer->email;
Alexander Makarov committed
148 149
```

Larry Ullman committed
150
To change the value of a column, assign a new value to the associated property and save the object:
Alexander Makarov committed
151 152

```
Larry Ullman committed
153 154 155
$customer->email = 'jane@example.com';
$customer->save();
```
Alexander Makarov committed
156

Larry Ullman committed
157
Manipulating Data in the Database
Qiang Xue committed
158
-----------------------------
Alexander Makarov committed
159

Qiang Xue committed
160
ActiveRecord provides the following methods to insert, update and delete data in the database:
Alexander Makarov committed
161

162 163 164 165 166 167 168 169
- [[yii\db\ActiveRecord::save()|save()]]
- [[yii\db\ActiveRecord::insert()|insert()]]
- [[yii\db\ActiveRecord::update()|update()]]
- [[yii\db\ActiveRecord::delete()|delete()]]
- [[yii\db\ActiveRecord::updateCounters()|updateCounters()]]
- [[yii\db\ActiveRecord::updateAll()|updateAll()]]
- [[yii\db\ActiveRecord::updateAllCounters()|updateAllCounters()]]
- [[yii\db\ActiveRecord::deleteAll()|deleteAll()]]
Qiang Xue committed
170

171
Note that [[yii\db\ActiveRecord::updateAll()|updateAll()]], [[yii\db\ActiveRecord::updateAllCounters()|updateAllCounters()]] and [[yii\db\ActiveRecord::deleteAll()|deleteAll()]] are static methods that apply to the whole database table. The other methods only apply to the row associated with the ActiveRecord object through which the method is being called.
Alexander Makarov committed
172 173 174

```php
// to insert a new customer record
175
$customer = new Customer();
Alexander Makarov committed
176 177 178 179 180 181 182 183 184 185 186 187 188
$customer->name = 'James';
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->insert();

// to update an existing customer record
$customer = Customer::find($id);
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->update();

// to delete an existing customer record
$customer = Customer::find($id);
$customer->delete();

Qiang Xue committed
189
// to increment the age of ALL customers by 1
Alexander Makarov committed
190
Customer::updateAllCounters(['age' => 1]);
Alexander Makarov committed
191 192
```

Qiang Xue committed
193 194 195
> Info: The `save()` method will either perform an `INSERT` or `UPDATE` SQL statement, depending
  on whether the ActiveRecord being saved is new or not by checking `ActiveRecord::isNewRecord`.

Larry Ullman committed
196 197 198 199

Data Input and Validation
-------------------------

200
ActiveRecord inherits data validation and data input features from [[yii\base\Model]]. Data validation is called
Larry Ullman committed
201 202 203
automatically when `save()` is performed. If data validation fails, the saving operation will be cancelled.

For more details refer to the [Model](model.md) section of this guide.
Alexander Makarov committed
204

Qiang Xue committed
205 206
Querying Relational Data
------------------------
Alexander Makarov committed
207

208 209 210
You can use ActiveRecord to also query a table's relational data (i.e., selection of data from Table A can also pull
in related data from Table B). Thanks to ActiveRecord, the relational data returned can be accessed like a property
of the ActiveRecord object associated with the primary table.
Larry Ullman committed
211

Qiang Xue committed
212 213
For example, with an appropriate relation declaration, by accessing `$customer->orders` you may obtain
an array of `Order` objects which represent the orders placed by the specified customer.
Alexander Makarov committed
214

215 216
To declare a relation, define a getter method which returns an [[yii\db\ActiveQuery]] object that has relation
information about the relation context and thus will only query for related records. For example,
Alexander Makarov committed
217 218 219 220 221 222

```php
class Customer extends \yii\db\ActiveRecord
{
	public function getOrders()
	{
223
		// Customer has_many Order via Order.customer_id -> id
Luciano Baraglia committed
224
		return $this->hasMany(Order::className(), ['customer_id' => 'id']);
Alexander Makarov committed
225 226 227 228 229
	}
}

class Order extends \yii\db\ActiveRecord
{
230
	// Order has_one Customer via Customer.id -> customer_id
Alexander Makarov committed
231 232
	public function getCustomer()
	{
233
		return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
Alexander Makarov committed
234 235 236 237
	}
}
```

238
The methods [[yii\db\ActiveRecord::hasMany()]] and [[yii\db\ActiveRecord::hasOne()]] used in the above
Qiang Xue committed
239 240
are used to model the many-one relationship and one-one relationship in a relational database.
For example, a customer has many orders, and an order has one customer.
241
Both methods take two parameters and return an [[yii\db\ActiveQuery]] object:
Alexander Makarov committed
242

Qiang Xue committed
243
 - `$class`: the name of the class of the related model(s). This should be a fully qualified class name.
Qiang Xue committed
244 245 246 247
 - `$link`: the association between columns from the two tables. This should be given as an array.
   The keys of the array are the names of the columns from the table associated with `$class`,
   while the values of the array are the names of the columns from the declaring class.
   It is a good practice to define relationships based on table foreign keys.
Alexander Makarov committed
248

Qiang Xue committed
249 250
After declaring relations, getting relational data is as easy as accessing a component property
that is defined by the corresponding getter method:
Alexander Makarov committed
251 252

```php
Qiang Xue committed
253 254
// get the orders of a customer
$customer = Customer::find(1);
Alexander Makarov committed
255
$orders = $customer->orders;  // $orders is an array of Order objects
Qiang Xue committed
256 257 258
```

Behind the scene, the above code executes the following two SQL queries, one for each line of code:
Alexander Makarov committed
259

Qiang Xue committed
260 261 262
```sql
SELECT * FROM tbl_customer WHERE id=1;
SELECT * FROM tbl_order WHERE customer_id=1;
Alexander Makarov committed
263 264
```

265 266
> Tip: If you access the expression `$customer->orders` again, it will not perform the second SQL query again.
The SQL query is only performed the first time when this expression is accessed. Any further
Qiang Xue committed
267 268 269 270 271 272
accesses will only return the previously fetched results that are cached internally. If you want to re-query
the relational data, simply unset the existing one first: `unset($customer->orders);`.

Sometimes, you may want to pass parameters to a relational query. For example, instead of returning
all orders of a customer, you may want to return only big orders whose subtotal exceeds a specified amount.
To do so, declare a `bigOrders` relation with the following getter method:
Alexander Makarov committed
273 274 275 276 277 278

```php
class Customer extends \yii\db\ActiveRecord
{
	public function getBigOrders($threshold = 100)
	{
279
		return $this->hasMany(Order::className(), ['customer_id' => 'id'])
Alexander Makarov committed
280
			->where('subtotal > :threshold', [':threshold' => $threshold])
Alexander Makarov committed
281 282 283 284 285
			->orderBy('id');
	}
}
```

286 287
Remember that `hasMany()` returns an [[yii\db\ActiveQuery]] object which allows you to customize the query by
calling the methods of [[yii\db\ActiveQuery]].
Qiang Xue committed
288 289 290 291 292 293 294 295

With the above declaration, if you access `$customer->bigOrders`, it will only return the orders
whose subtotal is greater than 100. To specify a different threshold value, use the following code:

```php
$orders = $customer->getBigOrders(200)->all();
```

296 297
> Note: A relation method returns an instance of [[yii\db\ActiveQuery]]. If you access the relation like
an attribute (i.e. a class property), the return value will be the query result of the relation, which could be an instance of [[yii\db\ActiveRecord]],
Qiang Xue committed
298
an array of that, or null, depending the multiplicity of the relation. For example, `$customer->getOrders()` returns
299
an `ActiveQuery` instance, while `$customer->orders` returns an array of `Order` objects (or an empty array if
Qiang Xue committed
300
the query results in nothing).
Qiang Xue committed
301

Qiang Xue committed
302 303 304 305

Relations with Pivot Table
--------------------------

306 307 308
Sometimes, two tables are related together via an intermediary table called [pivot table][]. To declare such relations,
we can customize the [[yii\db\ActiveQuery]] object by calling its [[yii\db\ActiveQuery::via()|via()]] or
[[yii\db\ActiveQuery::viaTable()|viaTable()]] method.
Alexander Makarov committed
309 310 311 312 313 314 315 316 317

For example, if table `tbl_order` and table `tbl_item` are related via pivot table `tbl_order_item`,
we can declare the `items` relation in the `Order` class like the following:

```php
class Order extends \yii\db\ActiveRecord
{
	public function getItems()
	{
318
		return $this->hasMany(Item::className(), ['id' => 'item_id'])
Alexander Makarov committed
319
			->viaTable('tbl_order_item', ['order_id' => 'id']);
Alexander Makarov committed
320 321 322 323
	}
}
```

324 325
The [[yii\db\ActiveQuery::via()|via()]] method is similar to [[yii\db\ActiveQuery::viaTable()|viaTable()]] except that
the first parameter of [[yii\db\ActiveQuery::via()|via()]] takes a relation name declared in the ActiveRecord class
Qiang Xue committed
326
instead of the pivot table name. For example, the above `items` relation can be equivalently declared as follows:
Alexander Makarov committed
327 328 329 330 331 332

```php
class Order extends \yii\db\ActiveRecord
{
	public function getOrderItems()
	{
333
		return $this->hasMany(OrderItem::className(), ['order_id' => 'id']);
Alexander Makarov committed
334 335 336 337
	}

	public function getItems()
	{
338
		return $this->hasMany(Item::className(), ['id' => 'item_id'])
Alexander Makarov committed
339 340 341 342 343
			->via('orderItems');
	}
}
```

344 345
[pivot table]: http://en.wikipedia.org/wiki/Pivot_table "Pivot table on Wikipedia"

Alexander Makarov committed
346

Qiang Xue committed
347 348 349 350
Lazy and Eager Loading
----------------------

As described earlier, when you access the related objects the first time, ActiveRecord will perform a DB query
Alexander Makarov committed
351 352 353 354 355 356 357 358 359 360 361 362
to retrieve the corresponding data and populate it into the related objects. No query will be performed
if you access the same related objects again. We call this *lazy loading*. For example,

```php
// SQL executed: SELECT * FROM tbl_customer WHERE id=1
$customer = Customer::find(1);
// SQL executed: SELECT * FROM tbl_order WHERE customer_id=1
$orders = $customer->orders;
// no SQL executed
$orders2 = $customer->orders;
```

Qiang Xue committed
363
Lazy loading is very convenient to use. However, it may suffer from a performance issue in the following scenario:
Alexander Makarov committed
364 365 366 367 368 369 370 371 372 373 374 375 376 377

```php
// SQL executed: SELECT * FROM tbl_customer LIMIT 100
$customers = Customer::find()->limit(100)->all();

foreach ($customers as $customer) {
	// SQL executed: SELECT * FROM tbl_order WHERE customer_id=...
	$orders = $customer->orders;
	// ...handle $orders...
}
```

How many SQL queries will be performed in the above code, assuming there are more than 100 customers in
the database? 101! The first SQL query brings back 100 customers. Then for each customer, a SQL query
Qiang Xue committed
378
is performed to bring back the orders of that customer.
Alexander Makarov committed
379

Carsten Brandt committed
380
To solve the above performance problem, you can use the so-called *eager loading* approach by calling [[yii\db\ActiveQuery::with()]]:
Alexander Makarov committed
381 382

```php
Qiang Xue committed
383
// SQL executed: SELECT * FROM tbl_customer LIMIT 100;
Alexander Makarov committed
384 385 386 387 388 389 390 391 392 393 394
//               SELECT * FROM tbl_orders WHERE customer_id IN (1,2,...)
$customers = Customer::find()->limit(100)
	->with('orders')->all();

foreach ($customers as $customer) {
	// no SQL executed
	$orders = $customer->orders;
	// ...handle $orders...
}
```

Qiang Xue committed
395 396 397 398 399
As you can see, only two SQL queries are needed for the same task!

> Info: In general, if you are eager loading `N` relations among which `M` relations are defined with `via()` or `viaTable()`,
> a total number of `1+M+N` SQL queries will be performed: one query to bring back the rows for the primary table, one for
> each of the `M` pivot tables corresponding to the `via()` or `viaTable()` calls, and one for each of the `N` related tables.
Alexander Makarov committed
400

Qiang Xue committed
401 402 403 404 405 406 407 408
> Note: When you are customizing `select()` with eager loading, make sure you include the columns that link
> the related models. Otherwise, the related models will not be loaded. For example,

```php
$orders = Order::find()->select(['id', 'amount'])->with('customer')->all();
// $orders[0]->customer is always null. To fix the problem, you should do the following:
$orders = Order::find()->select(['id', 'amount', 'customer_id'])->with('customer')->all();
```
Alexander Makarov committed
409

Qiang Xue committed
410
Sometimes, you may want to customize the relational queries on the fly. This can be
Alexander Makarov committed
411 412 413 414 415 416 417
done for both lazy loading and eager loading. For example,

```php
$customer = Customer::find(1);
// lazy loading: SELECT * FROM tbl_order WHERE customer_id=1 AND subtotal>100
$orders = $customer->getOrders()->where('subtotal>100')->all();

Алексей committed
418 419
// eager loading: SELECT * FROM tbl_customer LIMIT 100
//                SELECT * FROM tbl_order WHERE customer_id IN (1,2,...) AND subtotal>100
Alexander Makarov committed
420
$customers = Customer::find()->limit(100)->with([
Alexander Makarov committed
421 422 423
	'orders' => function($query) {
		$query->andWhere('subtotal>100');
	},
Alexander Makarov committed
424
])->all();
Alexander Makarov committed
425 426 427
```


428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471
Inverse Relations
-----------------

Relations can often be defined in pairs. For example, `Customer` may have a relation named `orders` while `Order` may have a relation
named `customer`:

```php
class Customer extends ActiveRecord
{
	....
	public function getOrders()
	{
		return $this->hasMany(Order::className, ['customer_id' => 'id']);
	}
}

class Order extends ActiveRecord
{
	....
	public function getCustomer()
	{
		return $this->hasOne(Customer::className, ['id' => 'customer_id']);
	}
}
```

If we perform the following query, we would find that the `customer` of an order is not the same customer object
that finds those orders, and accessing `customer->orders` will trigger one SQL execution while accessing
the `customer` of an order will trigger another SQL execution:

```php
// SELECT * FROM tbl_customer WHERE id=1
$customer = Customer::find(1);
// echoes "not equal"
// SELECT * FROM tbl_order WHERE customer_id=1
// SELECT * FROM tbl_customer WHERE id=1
if ($customer->orders[0]->customer === $customer) {
	echo 'equal';
} else {
	echo 'not equal';
}
```

To avoid the redundant execution of the last SQL statement, we could declare the inverse relations for the `customer`
472
and the `orders` relations by calling the [[yii\db\ActiveQuery::inverseOf()|inverseOf()]] method, like the following:
473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514

```php
class Customer extends ActiveRecord
{
	....
	public function getOrders()
	{
		return $this->hasMany(Order::className, ['customer_id' => 'id'])->inverseOf('customer');
	}
}
```

Now if we execute the same query as shown above, we would get:

```php
// SELECT * FROM tbl_customer WHERE id=1
$customer = Customer::find(1);
// echoes "equal"
// SELECT * FROM tbl_order WHERE customer_id=1
if ($customer->orders[0]->customer === $customer) {
	echo 'equal';
} else {
	echo 'not equal';
}
```

In the above, we have shown how to use inverse relations in lazy loading. Inverse relations also apply in
eager loading:

```php
// SELECT * FROM tbl_customer
// SELECT * FROM tbl_order WHERE customer_id IN (1, 2, ...)
$customers = Customer::find()->with('orders')->all();
// echoes "equal"
if ($customers[0]->orders[0]->customer === $customers[0]) {
	echo 'equal';
} else {
	echo 'not equal';
}
```

> Note: Inverse relation cannot be defined with a relation that involves pivoting tables.
515 516
> That is, if your relation is defined with [[yii\db\ActiveQuery::via()|via()]] or [[yii\db\ActiveQuery::viaTable()|viaTable()]],
> you cannot call [[yii\db\ActiveQuery::inverseOf()]] further.
517 518


519 520 521 522
Joining with Relations
----------------------

When working with relational databases, a common task is to join multiple tables and apply various
Carsten Brandt committed
523
query conditions and parameters to the JOIN SQL statement. Instead of calling [[yii\db\ActiveQuery::join()]]
524
explicitly to build up the JOIN query, you may reuse the existing relation definitions and call
Carsten Brandt committed
525
[[yii\db\ActiveQuery::joinWith()]] to achieve this goal. For example,
526 527

```php
528 529
// find all orders and sort the orders by the customer id and the order id. also eager loading "customer"
$orders = Order::find()->joinWith('customer')->orderBy('tbl_customer.id, tbl_order.id')->all();
530
// find all orders that contain books, and eager loading "books"
531
$orders = Order::find()->innerJoinWith('books')->all();
532 533
```

Carsten Brandt committed
534
In the above, the method [[yii\db\ActiveQuery::innerJoinWith()|innerJoinWith()]] is a shortcut to [[yii\db\ActiveQuery::joinWith()|joinWith()]]
535
with the join type set as `INNER JOIN`.
Qiang Xue committed
536

537 538
You may join with one or multiple relations; you may apply query conditions to the relations on-the-fly;
and you may also join with sub-relations. For example,
Qiang Xue committed
539 540 541 542

```php
// join with multiple relations
// find out the orders that contain books and are placed by customers who registered within the past 24 hours
543
$orders = Order::find()->innerJoinWith([
Qiang Xue committed
544 545
	'books',
	'customer' => function ($query) {
Alexander Kochetov committed
546
		$query->where('tbl_customer.created_at > ' . (time() - 24 * 3600));
Qiang Xue committed
547 548 549 550 551 552
	}
])->all();
// join with sub-relations: join with books and books' authors
$orders = Order::find()->joinWith('books.author')->all();
```

553 554 555 556
Behind the scene, Yii will first execute a JOIN SQL statement to bring back the primary models
satisfying the conditions applied to the JOIN SQL. It will then execute a query for each relation
and populate the corresponding related records.

Carsten Brandt committed
557
The difference between [[yii\db\ActiveQuery::joinWith()|joinWith()]] and [[yii\db\ActiveQuery::with()|with()]] is that
558 559 560 561 562 563 564 565
the former joins the tables for the primary model class and the related model classes to retrieve
the primary models, while the latter just queries against the table for the primary model class to
retrieve the primary models.

Because of this difference, you may apply query conditions that are only available to a JOIN SQL statement.
For example, you may filter the primary models by the conditions on the related models, like the example
above. You may also sort the primary models using columns from the related tables.

Carsten Brandt committed
566
When using [[yii\db\ActiveQuery::joinWith()|joinWith()]], you are responsible to disambiguate column names.
567 568 569
In the above examples, we use `tbl_item.id` and `tbl_order.id` to disambiguate the `id` column references
because both of the order table and the item table contain a column named `id`.

Qiang Xue committed
570 571 572
By default, when you join with a relation, the relation will also be eagerly loaded. You may change this behavior
by passing the `$eagerLoading` parameter which specifies whether to eager load the specified relations.

Carsten Brandt committed
573
And also by default, [[yii\db\ActiveQuery::joinWith()|joinWith()]] uses `LEFT JOIN` to join the related tables.
574
You may pass it with the `$joinType` parameter to customize the join type. As a shortcut to the `INNER JOIN` type,
Carsten Brandt committed
575
you may use [[yii\db\ActiveQuery::innerJoinWith()|innerJoinWith()]].
Qiang Xue committed
576 577 578 579 580

Below are some more examples,

```php
// find all orders that contain books, but do not eager loading "books".
581
$orders = Order::find()->innerJoinWith('books', false)->all();
582
// which is equivalent to the above
583
$orders = Order::find()->joinWith('books', false, 'INNER JOIN')->all();
Qiang Xue committed
584
```
585

586
Sometimes when joining two tables, you may need to specify some extra condition in the ON part of the JOIN query.
587
This can be done by calling the [[yii\db\ActiveQuery::onCondition()]] method like the following:
588 589 590 591 592 593

```php
class User extends ActiveRecord
{
	public function getBooks()
	{
594
		return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
595 596 597 598
	}
}
```

599 600
In the above, the [[yii\db\ActiveRecord::hasMany()|hasMany()]] method returns an [[yii\db\ActiveQuery]] instance,
upon which [[yii\db\ActiveQuery::onCondition()|onCondition()]] is called
601 602
to specify that only items whose `category_id` is 1 should be returned.

Carsten Brandt committed
603
When you perform query using [[yii\db\ActiveQuery::joinWith()|joinWith()]], the on-condition will be put in the ON part
604 605 606 607 608
of the corresponding JOIN query. For example,

```php
// SELECT tbl_user.* FROM tbl_user LEFT JOIN tbl_item ON tbl_item.owner_id=tbl_user.id AND category_id=1
// SELECT * FROM tbl_item WHERE owner_id IN (...) AND category_id=1
609
$users = User::find()->joinWith('books')->all();
610 611
```

Carsten Brandt committed
612
Note that if you use eager loading via [[yii\db\ActiveQuery::with()]] or lazy loading, the on-condition will be put
613 614 615 616
in the WHERE part of the corresponding SQL statement, because there is no JOIN query involved. For example,

```php
// SELECT * FROM tbl_user WHERE id=10
617
$user = User::find(10);
618 619 620 621
// SELECT * FROM tbl_item WHERE owner_id=10 AND category_id=1
$books = $user->books;
```

622

Alexander Makarov committed
623 624 625 626 627 628
Working with Relationships
--------------------------

ActiveRecord provides the following two methods for establishing and breaking a
relationship between two ActiveRecord objects:

629 630
- [[yii\db\ActiveRecord::link()|link()]]
- [[yii\db\ActiveRecord::unlink()|unlink()]]
Alexander Makarov committed
631 632 633 634 635 636

For example, given a customer and a new order, we can use the following code to make the
order owned by the customer:

```php
$customer = Customer::find(1);
637
$order = new Order();
Alexander Makarov committed
638 639 640 641
$order->subtotal = 100;
$customer->link('orders', $order);
```

642 643
The [[yii\db\ActiveRecord::link()|link()]] call above will set the `customer_id` of the order to be the primary key
value of `$customer` and then call [[yii\db\ActiveRecord::save()|save()]] to save the order into database.
Alexander Makarov committed
644 645 646 647 648 649 650 651 652 653 654 655


Life Cycles of an ActiveRecord Object
-------------------------------------

An ActiveRecord object undergoes different life cycles when it is used in different cases.
Subclasses or ActiveRecord behaviors may "inject" custom code in these life cycles through
method overriding and event handling mechanisms.

When instantiating a new ActiveRecord instance, we will have the following life cycles:

1. constructor
656
2. [[yii\db\ActiveRecord::init()|init()]]: will trigger an [[yii\db\ActiveRecord::EVENT_INIT|EVENT_INIT]] event
Alexander Makarov committed
657

658
When getting an ActiveRecord instance through the [[yii\db\ActiveRecord::find()|find()]] method, we will have the following life cycles:
Alexander Makarov committed
659 660

1. constructor
661 662
2. [[yii\db\ActiveRecord::init()|init()]]: will trigger an [[yii\db\ActiveRecord::EVENT_INIT|EVENT_INIT]] event
3. [[yii\db\ActiveRecord::afterFind()|afterFind()]]: will trigger an [[yii\db\ActiveRecord::EVENT_AFTER_FIND|EVENT_AFTER_FIND]] event
Alexander Makarov committed
663

664
When calling [[yii\db\ActiveRecord::save()|save()]] to insert or update an ActiveRecord, we will have the following life cycles:
Alexander Makarov committed
665

666 667 668
1. [[yii\db\ActiveRecord::beforeValidate()|beforeValidate()]]: will trigger an [[yii\db\ActiveRecord::EVENT_BEFORE_VALIDATE|EVENT_BEFORE_VALIDATE]] event
2. [[yii\db\ActiveRecord::afterValidate()|afterValidate()]]: will trigger an [[yii\db\ActiveRecord::EVENT_AFTER_VALIDATE|EVENT_AFTER_VALIDATE]] event
3. [[yii\db\ActiveRecord::beforeSave()|beforeSave()]]: will trigger an [[yii\db\ActiveRecord::EVENT_BEFORE_INSERT|EVENT_BEFORE_INSERT]] or [[yii\db\ActiveRecord::EVENT_BEFORE_UPDATE|EVENT_BEFORE_UPDATE]] event
Alexander Makarov committed
669
4. perform the actual data insertion or updating
670
5. [[yii\db\ActiveRecord::afterSave()|afterSave()]]: will trigger an [[yii\db\ActiveRecord::EVENT_AFTER_INSERT|EVENT_AFTER_INSERT]] or [[yii\db\ActiveRecord::EVENT_AFTER_UPDATE|EVENT_AFTER_UPDATE]] event
Alexander Makarov committed
671

672
Finally when calling [[yii\db\ActiveRecord::delete()|delete()]] to delete an ActiveRecord, we will have the following life cycles:
Alexander Makarov committed
673

674
1. [[yii\db\ActiveRecord::beforeDelete()|beforeDelete()]]: will trigger an [[yii\db\ActiveRecord::EVENT_BEFORE_DELETE|EVENT_BEFORE_DELETE]] event
Alexander Makarov committed
675
2. perform the actual data deletion
676
3. [[yii\db\ActiveRecord::afterDelete()|afterDelete()]]: will trigger an [[yii\db\ActiveRecord::EVENT_AFTER_DELETE|EVENT_AFTER_DELETE]] event
Alexander Makarov committed
677 678


Qiang Xue committed
679 680
Scopes
------
Alexander Makarov committed
681

682 683 684 685
When you call [[yii\db\ActiveRecord::find()|find()]] or [[yii\db\ActiveRecord::findBySql()|findBySql()]], it returns an
[[yii\db\ActiveQuery|ActiveQuery]] instance.
You may call additional query methods, such as [[yii\db\ActiveQuery::where()|where()]], [[yii\db\ActiveQuery::orderBy()|orderBy()]],
to further specify the query conditions.
686

Qiang Xue committed
687 688 689 690 691 692 693
It is possible that you may want to call the same set of query methods in different places. If this is the case,
you should consider defining the so-called *scopes*. A scope is essentially a method defined in a custom query class that
calls a set of query methods to modify the query object. You can then use a scope like calling a normal query method.

Two steps are required to define a scope. First create a custom query class for your model and define the needed scope
methods in this class. For example, create a `CommentQuery` class for the `Comment` model and define the `active()`
scope method like the following:
Alexander Makarov committed
694 695

```php
696 697
namespace app\models;

698
use yii\db\ActiveQuery;
699 700

class CommentQuery extends ActiveQuery
Alexander Makarov committed
701
{
702 703 704 705 706 707 708
	public function active($state = true)
	{
		$this->andWhere(['active' => $state]);
		return $this;
	}
}
```
Alexander Makarov committed
709

710 711 712 713
Important points are:

1. Class should extend from `yii\db\ActiveQuery` (or another `ActiveQuery` such as `yii\mongodb\ActiveQuery`).
2. A method should be `public` and should return `$this` in order to allow method chaining. It may accept parameters.
714
3. Check [[yii\db\ActiveQuery]] methods that are very useful for modifying query conditions.
715

716
Second, override [[yii\db\ActiveRecord::createQuery()]] to use the custom query class instead of the regular [[yii\db\ActiveQuery|ActiveQuery]].
Qiang Xue committed
717
For the example above, you need to write the following code:
718

Carsten Brandt committed
719
```php
720 721 722 723 724 725
namespace app\models;

use yii\db\ActiveRecord;

class Comment extends ActiveRecord
{
726
	public static function createQuery($config = [])
Alexander Makarov committed
727
	{
728 729
		$config['modelClass'] = get_called_class();
		return new CommentQuery($config);
Alexander Makarov committed
730 731
	}
}
732
```
Alexander Makarov committed
733

734 735 736
That's it. Now you can use your custom scope methods:

```php
737
$comments = Comment::find()->active()->all();
738
$inactiveComments = Comment::find()->active(false)->all();
739 740 741 742 743 744 745
```

You can also use scopes when defining relations. For example,

```php
class Post extends \yii\db\ActiveRecord
{
746
	public function getActiveComments()
747 748 749 750 751
	{
		return $this->hasMany(Comment::className(), ['post_id' => 'id'])->active();

	}
}
Alexander Makarov committed
752 753
```

754 755 756 757 758 759 760 761 762
Or use the scopes on-the-fly when performing relational query:

```php
$posts = Post::find()->with([
	'comments' => function($q) {
		$q->active();
	}
])->all();
```
Alexander Makarov committed
763

Qiang Xue committed
764

765 766
### Making it IDE-friendly

767 768
In order to make most modern IDE autocomplete happy you need to override return types for some methods of both model
and query like the following:
Alexander Makarov committed
769 770

```php
771 772 773 774 775
/**
 * @method \app\models\CommentQuery|static|null find($q = null) static
 * @method \app\models\CommentQuery findBySql($sql, $params = []) static
 */
class Comment extends ActiveRecord
Alexander Makarov committed
776
{
777
	// ...
Alexander Makarov committed
778 779 780
}
```

781
```php
782 783 784 785 786
/**
 * @method \app\models\Comment|array|null one($db = null)
 * @method \app\models\Comment[]|array all($db = null)
 */
class CommentQuery extends ActiveQuery
787
{
788
	// ...
789 790 791
}
```

792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809
### Default Scope

If you used Yii 1.1 before, you may know a concept called *default scope*. A default scope is a scope that
applies to ALL queries. You can define a default scope easily by overriding [[yii\db\ActiveRecord::createQuery()]]. For example,

```php
public static function createQuery($config = [])
{
	$config['modelClass'] = get_called_class();
	return (new ActiveQuery($config))->where(['deleted' => false]);
}
```

Note that all your queries should then not use [[yii\db\ActiveQuery::where()|where()]] but
[[yii\db\ActiveQuery::andWhere()|andWhere()]] and [[yii\db\ActiveQuery::orWhere()|orWhere()]]
to not override the default condition.


Qiang Xue committed
810 811 812 813
Transactional operations
------------------------

When a few DB operations are related and are executed
Alexander Makarov committed
814

815 816
TODO: FIXME: WIP, TBD, https://github.com/yiisoft/yii2/issues/226

Qiang Xue committed
817
,
818 819
[[yii\db\ActiveRecord::afterSave()|afterSave()]], [[yii\db\ActiveRecord::beforeDelete()|beforeDelete()]] and/or [[yii\db\ActiveRecord::afterDelete()|afterDelete()]] life cycle methods. Developer may come
to the solution of overriding ActiveRecord [[yii\db\ActiveRecord::save()|save()]] method with database transaction wrapping or
820 821
even using transaction in controller action, which is strictly speaking doesn't seem to be a good
practice (recall "skinny-controller / fat-model" fundamental rule).
822

823
Here these ways are (**DO NOT** use them unless you're sure what you are actually doing). Models:
824 825 826 827 828 829 830 831

```php
class Feature extends \yii\db\ActiveRecord
{
	// ...

	public function getProduct()
	{
832
		return $this->hasOne(Product::className(), ['product_id' => 'id']);
833 834 835 836 837 838 839 840 841
	}
}

class Product extends \yii\db\ActiveRecord
{
	// ...

	public function getFeatures()
	{
842
		return $this->hasMany(Feature::className(), ['id' => 'product_id']);
843 844 845 846
	}
}
```

847
Overriding [[yii\db\ActiveRecord::save()|save()]] method:
848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880

```php

class ProductController extends \yii\web\Controller
{
	public function actionCreate()
	{
		// FIXME: TODO: WIP, TBD
	}
}
```

Using transactions within controller layer:

```php
class ProductController extends \yii\web\Controller
{
	public function actionCreate()
	{
		// FIXME: TODO: WIP, TBD
	}
}
```

Instead of using these fragile methods you should consider using atomic scenarios and operations feature.

```php
class Feature extends \yii\db\ActiveRecord
{
	// ...

	public function getProduct()
	{
881
		return $this->hasOne(Product::className(), ['product_id' => 'id']);
882 883 884 885
	}

	public function scenarios()
	{
Alexander Makarov committed
886 887 888 889 890 891
		return [
			'userCreates' => [
				'attributes' => ['name', 'value'],
				'atomic' => [self::OP_INSERT],
			],
		];
892 893 894 895 896 897 898 899 900
	}
}

class Product extends \yii\db\ActiveRecord
{
	// ...

	public function getFeatures()
	{
901
		return $this->hasMany(Feature::className(), ['id' => 'product_id']);
902 903 904 905
	}

	public function scenarios()
	{
Alexander Makarov committed
906 907 908 909 910 911
		return [
			'userCreates' => [
				'attributes' => ['title', 'price'],
				'atomic' => [self::OP_INSERT],
			],
		];
912 913 914 915 916 917 918 919 920 921
	}

	public function afterValidate()
	{
		parent::afterValidate();
		// FIXME: TODO: WIP, TBD
	}

	public function afterSave($insert)
	{
922
		parent::afterSave($insert);
923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940
		if ($this->getScenario() === 'userCreates') {
			// FIXME: TODO: WIP, TBD
		}
	}
}
```

Controller is very thin and neat:

```php
class ProductController extends \yii\web\Controller
{
	public function actionCreate()
	{
		// FIXME: TODO: WIP, TBD
	}
}
```
Alexander Makarov committed
941

Qiang Xue committed
942 943 944 945 946 947 948 949 950 951
Optimistic Locks
----------------

TODO

Dirty Attributes
----------------

TODO

Alexander Makarov committed
952 953 954 955
See also
--------

- [Model](model.md)
956
- [[yii\db\ActiveRecord]]