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

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

Qiang Xue committed
6 7 8 9 10
[Active Record](http://en.wikipedia.org/wiki/Active_record_pattern) provides an object-oriented interface
for accessing data stored in a database. An Active Record class is associated with a database table,
an Active Record instance corresponds to a row of that table, and an attribute of an Active Record
instance represents the value of a column in that row. Instead of writing raw SQL statements,
you can work with Active Record in an object-oriented fashion to manipulate the data in database tables.
Larry Ullman committed
11

12
For example, assume `Customer` is an Active Record class which is associated with the `customer` table
13 14
and `name` is a column of the `customer` table. You can write the following code to insert a new
row into the `customer` table:
Alexander Makarov committed
15 16 17 18

```php
$customer = new Customer();
$customer->name = 'Qiang';
Qiang Xue committed
19 20 21 22
$customer->save();
```

The above code is equivalent to using the following raw SQL statement, which is less
23
intuitive, more error prone, and may have compatibility problems for different DBMS:
Qiang Xue committed
24 25

```php
26
$db->createCommand('INSERT INTO customer (name) VALUES (:name)', [
Qiang Xue committed
27 28
    ':name' => 'Qiang',
])->execute();
Alexander Makarov committed
29 30
```

Qiang Xue committed
31 32 33 34 35
Below is the list of databases that are currently supported by Yii Active Record:

* MySQL 4.1 or later: via [[yii\db\ActiveRecord]]
* PostgreSQL 7.3 or later: via [[yii\db\ActiveRecord]]
* SQLite 2 and 3: via [[yii\db\ActiveRecord]]
Qiang Xue committed
36
* Microsoft SQL Server 2008 or later: via [[yii\db\ActiveRecord]]
Qiang Xue committed
37
* Oracle: via [[yii\db\ActiveRecord]]
38 39
* CUBRID 9.3 or later: via [[yii\db\ActiveRecord]] (Note that due to a [bug](http://jira.cubrid.org/browse/APIS-658) in
  the cubrid PDO extension, quoting of values will not work, so you need CUBRID 9.3 as the client as well as the server)
40 41 42 43
* Sphnix: via [[yii\sphinx\ActiveRecord]], requires the `yii2-sphinx` extension
* ElasticSearch: via [[yii\elasticsearch\ActiveRecord]], requires the `yii2-elasticsearch` extension
* Redis 2.6.12 or later: via [[yii\redis\ActiveRecord]], requires the `yii2-redis` extension
* MongoDB 1.3.0 or later: via [[yii\mongodb\ActiveRecord]], requires the `yii2-mongodb` extension
Qiang Xue committed
44 45 46 47 48

As you can see, Yii provides Active Record support for relational databases as well as NoSQL databases.
In this tutorial, we will mainly describe the usage of Active Record for relational databases.
However, most content described here are also applicable to Active Record for NoSQL databases.

Alexander Makarov committed
49

Qiang Xue committed
50
Declaring Active Record Classes
Alexander Makarov committed
51 52
------------------------------

Qiang Xue committed
53 54
To declare an Active Record class you need to extend [[yii\db\ActiveRecord]] and implement
the `tableName` method that returns the name of the database table associated with the class:
Alexander Makarov committed
55 56

```php
Qiang Xue committed
57 58
namespace app\models;

Qiang Xue committed
59 60 61
use yii\db\ActiveRecord;

class Customer extends ActiveRecord
Alexander Makarov committed
62
{
63 64 65
    const STATUS_ACTIVE = 'active';
    const STATUS_DELETED = 'deleted';
    
66 67 68 69 70
    /**
     * @return string the name of the table associated with this ActiveRecord class.
     */
    public static function tableName()
    {
71
        return 'customer';
72
    }
Alexander Makarov committed
73 74 75
}
```

Larry Ullman committed
76

Qiang Xue committed
77 78 79 80 81 82 83 84 85 86
Accessing Column Data
---------------------

Active Record maps each column of the corresponding database table row to an attribute in the Active Record
object. An attribute behaves like a regular object public property. The name of an attribute is the same
as the corresponding column name and is case-sensitive.

To read the value of a column, you can use the following syntax:

```php
87
// "id" and "email" are the names of columns in the table associated with the $customer ActiveRecord object
Qiang Xue committed
88 89 90 91 92
$id = $customer->id;
$email = $customer->email;
```

To change the value of a column, assign a new value to the associated property and save the object:
Larry Ullman committed
93

Qiang Xue committed
94 95 96 97
```php
$customer->email = 'jane@example.com';
$customer->save();
```
Larry Ullman committed
98

Qiang Xue committed
99 100

Connecting to Database
Alexander Makarov committed
101 102
----------------------

103
Active Record uses a [[yii\db\Connection|DB connection]] to exchange data with the database. By default,
104
it uses the `db` [application component](structure-application-components.md) as the connection. As explained in [Database basics](db-dao.md),
105
you may configure the `db` component in the application configuration file as follows,
Alexander Makarov committed
106 107

```php
Alexander Makarov committed
108
return [
109 110 111 112 113 114 115 116
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=testdb',
            'username' => 'demo',
            'password' => 'demo',
        ],
    ],
Alexander Makarov committed
117
];
Alexander Makarov committed
118 119
```

Qiang Xue committed
120 121 122 123 124 125 126 127 128 129
If you are using multiple databases in your application and you want to use a different DB connection
for your Active Record class, you may override the [[yii\db\ActiveRecord::getDb()|getDb()]] method:

```php
class Customer extends ActiveRecord
{
    // ...

    public static function getDb()
    {
130
        return \Yii::$app->db2;  // use the "db2" application component
Qiang Xue committed
131 132 133 134
    }
}
```

Alexander Makarov committed
135

Qiang Xue committed
136
Querying Data from Database
Qiang Xue committed
137
---------------------------
Alexander Makarov committed
138

Qiang Xue committed
139
Active Record provides two entry methods for building DB queries and populating data into Active Record instances:
Alexander Makarov committed
140

141 142
 - [[yii\db\ActiveRecord::find()]]
 - [[yii\db\ActiveRecord::findBySql()]]
Alexander Makarov committed
143

Alexander Makarov committed
144
Both methods return an [[yii\db\ActiveQuery]] instance, which extends [[yii\db\Query]], and thus supports the same set
Qiang Xue committed
145 146
of flexible and powerful DB query building methods, such as `where()`, `join()`, `orderBy()`, etc. The following examples
demonstrate some of the possibilities.
Alexander Makarov committed
147 148 149 150

```php
// to retrieve all *active* customers and order them by their ID:
$customers = Customer::find()
151 152 153
    ->where(['status' => Customer::STATUS_ACTIVE])
    ->orderBy('id')
    ->all();
Alexander Makarov committed
154 155 156

// to return a single customer whose ID is 1:
$customer = Customer::find()
157 158
    ->where(['id' => 1])
    ->one();
Alexander Makarov committed
159 160 161

// to return the number of *active* customers:
$count = Customer::find()
162 163
    ->where(['status' => Customer::STATUS_ACTIVE])
    ->count();
Alexander Makarov committed
164

Qiang Xue committed
165 166 167 168 169
// to index the result by customer IDs:
$customers = Customer::find()->indexBy('id')->all();
// $customers array is indexed by customer IDs

// to retrieve customers using a raw SQL statement:
170
$sql = 'SELECT * FROM customer';
Qiang Xue committed
171 172 173 174 175 176 177
$customers = Customer::findBySql($sql)->all();
```

> Tip: In the code above `Customer::STATUS_ACTIVE` is a constant defined in `Customer`. It is a good practice to
  use meaningful constant names rather than hardcoded strings or numbers in your code.


178 179 180
Two shortcut methods are provided to return Active Record instances matching a primary key value or a set of
column values: `findOne()` and `findAll()`. The former returns the first matching instance while the latter
returns all of them. For example,
Qiang Xue committed
181 182 183

```php
// to return a single customer whose ID is 1:
Alexander Makarov committed
184
$customer = Customer::findOne(1);
Qiang Xue committed
185 186

// to return an *active* customer whose ID is 1:
Alexander Makarov committed
187
$customer = Customer::findOne([
Qiang Xue committed
188 189 190
    'id' => 1,
    'status' => Customer::STATUS_ACTIVE,
]);
191 192 193 194 195 196 197 198

// to return customers whose ID is 1, 2 or 3:
$customers = Customer::findAll([1, 2, 3]);

// to return customers whose status is "deleted":
$customer = Customer::findAll([
    'status' => Customer::STATUS_DELETED,
]);
Qiang Xue committed
199 200
```

Qiang Xue committed
201 202 203 204
> Note: By default neither `findOne()` nor `one()` will add `LIMIT 1` to the query. This is fine and preferred
  if you know the query will return only one or a few rows of data (e.g. if you are querying with some primary keys).
  However, if the query may potentially return many rows of data, you should call `limit(1)` to improve the performance.
  For example, `Customer::find()->where(['status' => Customer::STATUS_ACTIVE])->limit(1)->one()`.
205

Qiang Xue committed
206 207 208 209 210 211 212

### Retrieving Data in Arrays

Sometimes when you are processing a large amount of data, you may want to use arrays to hold the data
retrieved from database to save memory. This can be done by calling `asArray()`:

```php
Alexander Makarov committed
213
// to return customers in terms of arrays rather than `Customer` objects:
Qiang Xue committed
214
$customers = Customer::find()
215 216
    ->asArray()
    ->all();
Qiang Xue committed
217
// each element of $customers is an array of name-value pairs
Alexander Makarov committed
218 219
```

220 221 222
Note that while this method saves memory and improves performance it is a step to a lower abstraction
layer and you will loose some features that the active record layer has.
Fetching data using asArray is nearly equal to running a normal query using the [query builder](db-dao.md).
223
When using asArray the result will be returned as a simple array with no typecasting performed 
224
so the result may contain string values for fields that are integer when accessed on the active record object.
Alexander Makarov committed
225

Qiang Xue committed
226
### Retrieving Data in Batches
Alexander Makarov committed
227

228 229
In [Query Builder](db-query-builder.md), we have explained that you may use *batch query* to minimize your memory
usage when querying a large amount of data from the database. You may use the same technique
Qiang Xue committed
230
in Active Record. For example,
231 232 233

```php
// fetch 10 customers at a time
Qiang Xue committed
234
foreach (Customer::find()->batch(10) as $customers) {
235
    // $customers is an array of 10 or fewer Customer objects
236
}
Qiang Xue committed
237 238
// fetch 10 customers at a time and iterate them one by one
foreach (Customer::find()->each(10) as $customer) {
239
    // $customer is a Customer object
240 241
}
// batch query with eager loading
Qiang Xue committed
242
foreach (Customer::find()->with('orders')->each() as $customer) {
243 244 245
}
```

Alexander Makarov committed
246

Qiang Xue committed
247
Manipulating Data in Database
Qiang Xue committed
248
-----------------------------
Alexander Makarov committed
249

Qiang Xue committed
250 251
Active Record provides the following methods to insert, update and delete a single row in a table associated with
a single Active Record instance:
Alexander Makarov committed
252

253 254 255 256
- [[yii\db\ActiveRecord::save()|save()]]
- [[yii\db\ActiveRecord::insert()|insert()]]
- [[yii\db\ActiveRecord::update()|update()]]
- [[yii\db\ActiveRecord::delete()|delete()]]
Qiang Xue committed
257 258 259 260 261

Active Record also provides the following static methods that apply to a whole table associated with
an Active Record class. Be extremely careful when using these methods as they affect the whole table.
For example, `deleteAll()` will delete ALL rows in the table.

262 263 264 265
- [[yii\db\ActiveRecord::updateCounters()|updateCounters()]]
- [[yii\db\ActiveRecord::updateAll()|updateAll()]]
- [[yii\db\ActiveRecord::updateAllCounters()|updateAllCounters()]]
- [[yii\db\ActiveRecord::deleteAll()|deleteAll()]]
Qiang Xue committed
266

Qiang Xue committed
267 268

The following examples show how to use these methods:
Alexander Makarov committed
269 270 271

```php
// to insert a new customer record
272
$customer = new Customer();
Alexander Makarov committed
273 274 275 276 277
$customer->name = 'James';
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->insert();

// to update an existing customer record
Alexander Makarov committed
278
$customer = Customer::findOne($id);
Alexander Makarov committed
279 280 281 282
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->update();

// to delete an existing customer record
Alexander Makarov committed
283
$customer = Customer::findOne($id);
Alexander Makarov committed
284 285
$customer->delete();

Budi Irawan committed
286 287 288
// to delete several customers
Customer::deleteAll('age > :age AND gender = :gender', [':age' => 20, ':gender' => 'M']);

Qiang Xue committed
289
// to increment the age of ALL customers by 1
Alexander Makarov committed
290
Customer::updateAllCounters(['age' => 1]);
Alexander Makarov committed
291 292
```

Qiang Xue committed
293 294
> Info: The `save()` method will call either `insert()` or `update()`, depending on whether
  the Active Record instance is new or not (internally it will check the value of [[yii\db\ActiveRecord::isNewRecord]]).
Qiang Xue committed
295
  If an Active Record is instantiated via the `new` operator, calling `save()` will
296
  insert a row in the table; calling `save()` on an active record fetched from the database will update the corresponding
Alexander Makarov committed
297
  row in the table.
Qiang Xue committed
298

Qiang Xue committed
299

Qiang Xue committed
300 301 302
### Data Input and Validation

Because Active Record extends from [[yii\base\Model]], it supports the same data input and validation features
303
as described in [Model](structure-models.md). For example, you may declare validation rules by overwriting the
Qiang Xue committed
304 305 306 307 308 309
[[yii\base\Model::rules()|rules()]] method; you may massively assign user input data to an Active Record instance;
and you may call [[yii\base\Model::validate()|validate()]] to trigger data validation.

When you call `save()`, `insert()` or `update()`, these methods will automatically call [[yii\base\Model::validate()|validate()]].
If the validation fails, the corresponding data saving operation will be cancelled.

310
The following example shows how to use an Active Record to collect/validate user input and save them into the database:
Qiang Xue committed
311 312 313 314 315 316 317 318 319

```php
// creating a new record
$model = new Customer;
if ($model->load(Yii::$app->request->post()) && $model->save()) {
    // the user input has been collected, validated and saved
}

// updating a record whose primary key is $id
Alexander Makarov committed
320
$model = Customer::findOne($id);
Qiang Xue committed
321 322 323 324 325 326 327 328 329
if ($model === null) {
    throw new NotFoundHttpException;
}
if ($model->load(Yii::$app->request->post()) && $model->save()) {
    // the user input has been collected, validated and saved
}
```


Qiang Xue committed
330 331
### Loading Default Values

Qiang Xue committed
332
Your table columns may be defined with default values. Sometimes, you may want to pre-populate your
333 334
Web form for an Active Record with these values. To do so, call the
[[yii\db\ActiveRecord::loadDefaultValues()|loadDefaultValues()]] method before rendering the form:
335 336 337 338

```php
$customer = new Customer();
$customer->loadDefaultValues();
Qiang Xue committed
339
// ... render HTML form for $customer ...
340 341
```

342 343 344 345 346 347 348
If you want to set some initial values for the attributes yourself you can override the `init()` method
of the active record class and set the values there. For example to set the default value for the `status` attribute:

```php
public function init()
{
    parent::init();
349
    $this->status = self::STATUS_ACTIVE;
350 351
}
```
Larry Ullman committed
352

Qiang Xue committed
353
Active Record Life Cycles
Larry Ullman committed
354 355
-------------------------

Qiang Xue committed
356 357
It is important to understand the life cycles of Active Record when it is used to manipulate data in database.
These life cycles are typically associated with corresponding events which allow you to inject code
358
to intercept or respond to these events. They are especially useful for developing Active Record [behaviors](concept-behaviors.md).
Qiang Xue committed
359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380

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

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

When querying data through the [[yii\db\ActiveRecord::find()|find()]] method, we will have the following life cycles
for EVERY newly populated Active Record instance:

1. constructor
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

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

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
4. perform the actual data insertion or updating
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

381
And finally, when calling [[yii\db\ActiveRecord::delete()|delete()]] to delete an ActiveRecord, we will have
Qiang Xue committed
382
the following life cycles:
Larry Ullman committed
383

Qiang Xue committed
384 385 386
1. [[yii\db\ActiveRecord::beforeDelete()|beforeDelete()]]: will trigger an [[yii\db\ActiveRecord::EVENT_BEFORE_DELETE|EVENT_BEFORE_DELETE]] event
2. perform the actual data deletion
3. [[yii\db\ActiveRecord::afterDelete()|afterDelete()]]: will trigger an [[yii\db\ActiveRecord::EVENT_AFTER_DELETE|EVENT_AFTER_DELETE]] event
Alexander Makarov committed
387

Qiang Xue committed
388

Qiang Xue committed
389 390
Working with Relational Data
----------------------------
Alexander Makarov committed
391

392 393 394
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
395

Qiang Xue committed
396 397
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
398

399 400
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
401 402 403 404

```php
class Customer extends \yii\db\ActiveRecord
{
405 406 407 408 409
    public function getOrders()
    {
        // Customer has_many Order via Order.customer_id -> id
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
Alexander Makarov committed
410 411 412 413
}

class Order extends \yii\db\ActiveRecord
{
414 415
    public function getCustomer()
    {
416
        // Order has_one Customer via Customer.id -> customer_id
417 418
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
Alexander Makarov committed
419 420 421
}
```

422
The methods [[yii\db\ActiveRecord::hasMany()]] and [[yii\db\ActiveRecord::hasOne()]] used in the above
Qiang Xue committed
423 424
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.
425
Both methods take two parameters and return an [[yii\db\ActiveQuery]] object:
Alexander Makarov committed
426

Qiang Xue committed
427
 - `$class`: the name of the class of the related model(s). This should be a fully qualified class name.
Qiang Xue committed
428 429 430 431
 - `$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
432

Qiang Xue committed
433 434
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
435 436

```php
Qiang Xue committed
437
// get the orders of a customer
Alexander Makarov committed
438
$customer = Customer::findOne(1);
Alexander Makarov committed
439
$orders = $customer->orders;  // $orders is an array of Order objects
Qiang Xue committed
440 441
```

442
Behind the scenes, the above code executes the following two SQL queries, one for each line of code:
Alexander Makarov committed
443

Qiang Xue committed
444
```sql
445 446
SELECT * FROM customer WHERE id=1;
SELECT * FROM order WHERE customer_id=1;
Alexander Makarov committed
447 448
```

449 450
> 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
451
accesses will only return the previously fetched results that are cached internally. If you want to re-query
452
the relational data, simply unset the existing expression first: `unset($customer->orders);`.
Qiang Xue committed
453 454 455 456

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
457 458 459 460

```php
class Customer extends \yii\db\ActiveRecord
{
461 462 463 464 465 466
    public function getBigOrders($threshold = 100)
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])
            ->where('subtotal > :threshold', [':threshold' => $threshold])
            ->orderBy('id');
    }
Alexander Makarov committed
467 468 469
}
```

470 471
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
472 473 474 475 476 477 478 479

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();
```

480 481
> 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]],
482
an array of that, or null, depending on the multiplicity of the relation. For example, `$customer->getOrders()` returns
483
an `ActiveQuery` instance, while `$customer->orders` returns an array of `Order` objects (or an empty array if
Qiang Xue committed
484
the query results in nothing).
Qiang Xue committed
485

Qiang Xue committed
486

487 488
Relations with Junction Table
-----------------------------
Qiang Xue committed
489

490
Sometimes, two tables are related together via an intermediary table called a [junction table][]. To declare such relations,
491 492
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
493

494
For example, if table `order` and table `item` are related via the junction table `order_item`,
Alexander Makarov committed
495 496 497 498 499
we can declare the `items` relation in the `Order` class like the following:

```php
class Order extends \yii\db\ActiveRecord
{
500 501 502
    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
503
            ->viaTable('order_item', ['order_id' => 'id']);
504
    }
Alexander Makarov committed
505 506 507
}
```

508 509
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
510
instead of the junction table name. For example, the above `items` relation can be equivalently declared as follows:
Alexander Makarov committed
511 512 513 514

```php
class Order extends \yii\db\ActiveRecord
{
515 516 517 518 519 520 521 522 523 524
    public function getOrderItems()
    {
        return $this->hasMany(OrderItem::className(), ['order_id' => 'id']);
    }

    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
            ->via('orderItems');
    }
Alexander Makarov committed
525 526 527
}
```

528
[junction table]: https://en.wikipedia.org/wiki/Junction_table "Junction table on Wikipedia"
529

Alexander Makarov committed
530

Qiang Xue committed
531 532 533
Lazy and Eager Loading
----------------------

534
As described earlier, when you access the related objects for the first time, ActiveRecord will perform a DB query
Alexander Makarov committed
535 536 537 538
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
539
// SQL executed: SELECT * FROM customer WHERE id=1
Alexander Makarov committed
540
$customer = Customer::findOne(1);
541
// SQL executed: SELECT * FROM order WHERE customer_id=1
Alexander Makarov committed
542 543 544 545 546
$orders = $customer->orders;
// no SQL executed
$orders2 = $customer->orders;
```

Qiang Xue committed
547
Lazy loading is very convenient to use. However, it may suffer from a performance issue in the following scenario:
Alexander Makarov committed
548 549

```php
550
// SQL executed: SELECT * FROM customer LIMIT 100
Alexander Makarov committed
551 552 553
$customers = Customer::find()->limit(100)->all();

foreach ($customers as $customer) {
554
    // SQL executed: SELECT * FROM order WHERE customer_id=...
555 556
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
557 558 559 560 561
}
```

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
562
is performed to bring back the orders of that customer.
Alexander Makarov committed
563

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

```php
567 568
// SQL executed: SELECT * FROM customer LIMIT 100;
//               SELECT * FROM orders WHERE customer_id IN (1,2,...)
Alexander Makarov committed
569
$customers = Customer::find()->limit(100)
570
    ->with('orders')->all();
Alexander Makarov committed
571 572

foreach ($customers as $customer) {
573 574 575
    // no SQL executed
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
576 577 578
}
```

Qiang Xue committed
579 580 581 582
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
583
> each of the `M` junction tables corresponding to the `via()` or `viaTable()` calls, and one for each of the `N` related tables.
Alexander Makarov committed
584

Qiang Xue committed
585 586 587 588 589 590 591 592
> 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
593

Qiang Xue committed
594
Sometimes, you may want to customize the relational queries on the fly. This can be
Alexander Makarov committed
595 596 597
done for both lazy loading and eager loading. For example,

```php
Alexander Makarov committed
598
$customer = Customer::findOne(1);
599
// lazy loading: SELECT * FROM order WHERE customer_id=1 AND subtotal>100
Alexander Makarov committed
600 601
$orders = $customer->getOrders()->where('subtotal>100')->all();

602 603
// eager loading: SELECT * FROM customer LIMIT 100
//                SELECT * FROM order WHERE customer_id IN (1,2,...) AND subtotal>100
Alexander Makarov committed
604
$customers = Customer::find()->limit(100)->with([
605 606 607
    'orders' => function($query) {
        $query->andWhere('subtotal>100');
    },
Alexander Makarov committed
608
])->all();
Alexander Makarov committed
609 610 611
```


612 613 614 615 616 617 618 619 620
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
{
621 622 623 624 625
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
626 627 628 629
}

class Order extends ActiveRecord
{
630 631 632 633 634
    ....
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
635 636 637 638 639 640 641 642
}
```

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
643
// SELECT * FROM customer WHERE id=1
Alexander Makarov committed
644
$customer = Customer::findOne(1);
645
// echoes "not equal"
646 647
// SELECT * FROM order WHERE customer_id=1
// SELECT * FROM customer WHERE id=1
648
if ($customer->orders[0]->customer === $customer) {
649
    echo 'equal';
650
} else {
651
    echo 'not equal';
652 653 654 655
}
```

To avoid the redundant execution of the last SQL statement, we could declare the inverse relations for the `customer`
656
and the `orders` relations by calling the [[yii\db\ActiveQuery::inverseOf()|inverseOf()]] method, like the following:
657 658 659 660

```php
class Customer extends ActiveRecord
{
661 662 663 664 665
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])->inverseOf('customer');
    }
666 667 668 669 670 671
}
```

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

```php
672
// SELECT * FROM customer WHERE id=1
Alexander Makarov committed
673
$customer = Customer::findOne(1);
674
// echoes "equal"
675
// SELECT * FROM order WHERE customer_id=1
676
if ($customer->orders[0]->customer === $customer) {
677
    echo 'equal';
678
} else {
679
    echo 'not equal';
680 681 682 683 684 685 686
}
```

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

```php
687 688
// SELECT * FROM customer
// SELECT * FROM order WHERE customer_id IN (1, 2, ...)
689 690 691
$customers = Customer::find()->with('orders')->all();
// echoes "equal"
if ($customers[0]->orders[0]->customer === $customers[0]) {
692
    echo 'equal';
693
} else {
694
    echo 'not equal';
695 696 697 698
}
```

> Note: Inverse relation cannot be defined with a relation that involves pivoting tables.
699 700
> 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.
701 702


703
Joining with Relations <a name="joining-with-relations"></a>
704 705 706
----------------------

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

```php
712
// find all orders and sort the orders by the customer id and the order id. also eager loading "customer"
713
$orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all();
714
// find all orders that contain books, and eager loading "books"
715
$orders = Order::find()->innerJoinWith('books')->all();
716 717
```

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

721 722
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
723 724 725

```php
// join with multiple relations
726
// find the orders that contain books and were placed by customers who registered within the past 24 hours
727
$orders = Order::find()->innerJoinWith([
728 729
    'books',
    'customer' => function ($query) {
730
        $query->where('customer.created_at > ' . (time() - 24 * 3600));
731
    }
Qiang Xue committed
732 733 734 735 736
])->all();
// join with sub-relations: join with books and books' authors
$orders = Order::find()->joinWith('books.author')->all();
```

737
Behind the scenes, Yii will first execute a JOIN SQL statement to bring back the primary models
738 739 740
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
741
The difference between [[yii\db\ActiveQuery::joinWith()|joinWith()]] and [[yii\db\ActiveQuery::with()|with()]] is that
742 743 744 745 746 747 748 749
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
750
When using [[yii\db\ActiveQuery::joinWith()|joinWith()]], you are responsible to disambiguate column names.
751
In the above examples, we use `item.id` and `order.id` to disambiguate the `id` column references
752 753
because both of the order table and the item table contain a column named `id`.

Qiang Xue committed
754 755 756
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
757
And also by default, [[yii\db\ActiveQuery::joinWith()|joinWith()]] uses `LEFT JOIN` to join the related tables.
758
You may pass it with the `$joinType` parameter to customize the join type. As a shortcut to the `INNER JOIN` type,
Carsten Brandt committed
759
you may use [[yii\db\ActiveQuery::innerJoinWith()|innerJoinWith()]].
Qiang Xue committed
760 761 762 763

Below are some more examples,

```php
764
// find all orders that contain books, but do not eager load "books".
765
$orders = Order::find()->innerJoinWith('books', false)->all();
766
// which is equivalent to the above
767
$orders = Order::find()->joinWith('books', false, 'INNER JOIN')->all();
Qiang Xue committed
768
```
769

770
Sometimes when joining two tables, you may need to specify some extra condition in the ON part of the JOIN query.
771
This can be done by calling the [[yii\db\ActiveQuery::onCondition()]] method like the following:
772 773 774 775

```php
class User extends ActiveRecord
{
776 777 778 779
    public function getBooks()
    {
        return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
    }
780 781 782
}
```

783 784
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
785 786
to specify that only items whose `category_id` is 1 should be returned.

787
When you perform a query using [[yii\db\ActiveQuery::joinWith()|joinWith()]], the ON condition will be put in the ON part
788 789 790
of the corresponding JOIN query. For example,

```php
791 792
// SELECT user.* FROM user LEFT JOIN item ON item.owner_id=user.id AND category_id=1
// SELECT * FROM item WHERE owner_id IN (...) AND category_id=1
793
$users = User::find()->joinWith('books')->all();
794 795
```

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

```php
800
// SELECT * FROM user WHERE id=10
Alexander Makarov committed
801
$user = User::findOne(10);
802
// SELECT * FROM item WHERE owner_id=10 AND category_id=1
803 804 805
$books = $user->books;
```

806

Alexander Makarov committed
807 808 809 810 811 812
Working with Relationships
--------------------------

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

813 814
- [[yii\db\ActiveRecord::link()|link()]]
- [[yii\db\ActiveRecord::unlink()|unlink()]]
Alexander Makarov committed
815 816 817 818 819

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

```php
Alexander Makarov committed
820
$customer = Customer::findOne(1);
821
$order = new Order();
Alexander Makarov committed
822 823 824 825
$order->subtotal = 100;
$customer->link('orders', $order);
```

826
The [[yii\db\ActiveRecord::link()|link()]] call above will set the `customer_id` of the order to be the primary key
827
value of `$customer` and then call [[yii\db\ActiveRecord::save()|save()]] to save the order into the database.
Alexander Makarov committed
828 829


830 831 832
Cross-DBMS Relations
--------------------

833
ActiveRecord allows you to establish relationships between entities from different DBMS. For example: between a relational database table and MongoDB collection. Such a relation does not require any special code:
834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866

```php
// Relational database Active Record
class Customer extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'customer';
    }

    public function getComments()
    {
        // Customer, stored in relational database, has many Comments, stored in MongoDB collection:
        return $this->hasMany(Comment::className(), ['customer_id' => 'id']);
    }
}

// MongoDb Active Record
class Comment extends \yii\mongodb\ActiveRecord
{
    public static function collectionName()
    {
        return 'comment';
    }

    public function getCustomer()
    {
        // Comment, stored in MongoDB collection, has one Customer, stored in relational database:
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
}
```

Klimov Paul committed
867
All Active Record features like eager and lazy loading, establishing and breaking a relationship and so on, are
868 869
available for cross-DBMS relations.

Klimov Paul committed
870 871 872
> Note: do not forget Active Record solutions for different DBMS may have specific methods and features, which may not be
  applied for cross-DBMS relations. For example: usage of [[yii\db\ActiveQuery::joinWith()]] will obviously not work with
  relation to the MongoDB collection.
873 874


Qiang Xue committed
875 876
Scopes
------
Alexander Makarov committed
877

878 879 880 881
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.
882

Qiang Xue committed
883
It is possible that you may want to call the same set of query methods in different places. If this is the case,
884
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 instead of calling a normal query method.
Qiang Xue committed
885

886
Two steps are required to define a scope. First, create a custom query class for your model and define the needed scope
Qiang Xue committed
887 888
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
889 890

```php
891 892
namespace app\models;

893
use yii\db\ActiveQuery;
894 895

class CommentQuery extends ActiveQuery
Alexander Makarov committed
896
{
897 898 899 900 901
    public function active($state = true)
    {
        $this->andWhere(['active' => $state]);
        return $this;
    }
902 903
}
```
Alexander Makarov committed
904

905 906 907 908
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.
909
3. Check [[yii\db\ActiveQuery]] methods that are very useful for modifying query conditions.
910

Alexander Makarov committed
911
Second, override [[yii\db\ActiveRecord::find()]] to use the custom query class instead of the regular [[yii\db\ActiveQuery|ActiveQuery]].
Qiang Xue committed
912
For the example above, you need to write the following code:
913

Carsten Brandt committed
914
```php
915 916 917 918 919 920
namespace app\models;

use yii\db\ActiveRecord;

class Comment extends ActiveRecord
{
Alexander Makarov committed
921 922 923 924 925
    /**
     * @inheritdoc
     * @return CommentQuery
     */
    public static function find()
926
    {
927
        return new CommentQuery(get_called_class());
928
    }
Alexander Makarov committed
929
}
930
```
Alexander Makarov committed
931

932 933 934
That's it. Now you can use your custom scope methods:

```php
935
$comments = Comment::find()->active()->all();
936
$inactiveComments = Comment::find()->active(false)->all();
937 938 939 940 941 942 943
```

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

```php
class Post extends \yii\db\ActiveRecord
{
944 945 946
    public function getActiveComments()
    {
        return $this->hasMany(Comment::className(), ['post_id' => 'id'])->active();
947

948
    }
949
}
Alexander Makarov committed
950 951
```

952
Or use the scopes on-the-fly when performing a relational query:
953 954 955

```php
$posts = Post::find()->with([
956 957 958
    'comments' => function($q) {
        $q->active();
    }
959 960
])->all();
```
Alexander Makarov committed
961

962 963 964
### Default Scope

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

```php
Alexander Makarov committed
968
public static function find()
969
{
Alexander Makarov committed
970
    return parent::find()->where(['deleted' => false]);
971 972 973 974 975 976 977 978
}
```

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
979
Transactional operations
980
---------------------
981

982
There are two ways of dealing with transactions while working with Active Record. First way is doing everything manually
983 984
as described in the "transactions" section of "[Database basics](db-dao.md)". Another way is to implement the
`transactions` method where you can specify which operations are to be wrapped into transactions on a per model scenario:
985 986

```php
987
class Post extends \yii\db\ActiveRecord
988
{
989 990 991 992 993 994 995 996 997 998
    public function transactions()
    {
        return [
            'admin' => self::OP_INSERT,
            'api' => self::OP_INSERT | self::OP_UPDATE | self::OP_DELETE,
            // the above is equivalent to the following:
            // 'api' => self::OP_ALL,
        ];
    }
}
999 1000
```

1001 1002
In the above `admin` and `api` are model scenarios and the constants starting with `OP_` are operations that should
be wrapped in transactions for these scenarios. Supported operations are `OP_INSERT`, `OP_UPDATE` and `OP_DELETE`.
1003
`OP_ALL` stands for all three.
1004

1005 1006
Such automatic transactions are especially useful if you're doing additional database changes in `beforeSave`,
`afterSave`, `beforeDelete`, `afterDelete` and want to be sure that both succeeded before they are saved.
1007

1008 1009
Optimistic Locks
--------------
1010

1011
Optimistic locking allows multiple users to access the same record for edits and avoids
1012
potential conflicts. For example, when a user attempts to save the record upon some staled data
1013 1014
(because another user has modified the data), a [[\yii\db\StaleObjectException]] exception will be thrown,
and the update or deletion is skipped.
1015

1016
Optimistic locking is only supported by `update()` and `delete()` methods and isn't used by default.
Alexander Makarov committed
1017

1018
To use Optimistic locking:
Qiang Xue committed
1019

1020
1. Create a column to store the version number of each row. The column type should be `BIGINT DEFAULT 0`.
1021
   Override the `optimisticLock()` method to return the name of this column.
1022
2. In the Web form that collects the user input, add a hidden field that stores
1023
   the lock version of the record being updated.
1024
3. In the controller action that does the data updating, try to catch the [[\yii\db\StaleObjectException]]
1025
   and implement necessary business logic (e.g. merging the changes, prompting staled data)
1026
   to resolve the conflict.
Qiang Xue committed
1027 1028

Dirty Attributes
1029
--------------
Qiang Xue committed
1030

1031
An attribute is considered dirty if its value was modified after the model was loaded from database or since the most recent data save. When saving record data by calling `save()`, `update()`, `insert()` etc. only dirty attributes are saved into the database. If there are no dirty attributes then there is nothing to be saved so no query will be issued at all.
Qiang Xue committed
1032

Alexander Makarov committed
1033 1034 1035
See also
--------

1036
- [Model](structure-models.md)
1037
- [[yii\db\ActiveRecord]]