db-active-record.md 39.9 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

99 100 101
> Note: Obviously, because column names become attribute names of the active record class directly, you
> get attribute names with underscores if you have that kind of naming schema in your database. For example
> a column `user_name` will be accessed as `$user->user_name` on the active record object. If you are concerned about code style
Alexander Makarov committed
102
> you should adopt your database naming schema to use camelCase too. However, camelCase is not a requirement, Yii can work
103 104
> well with any other naming style.

Qiang Xue committed
105 106

Connecting to Database
Alexander Makarov committed
107 108
----------------------

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

```php
Alexander Makarov committed
114
return [
115 116 117 118 119 120 121 122
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=testdb',
            'username' => 'demo',
            'password' => 'demo',
        ],
    ],
Alexander Makarov committed
123
];
Alexander Makarov committed
124 125
```

Qiang Xue committed
126 127 128 129 130 131 132 133 134 135
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()
    {
136
        return \Yii::$app->db2;  // use the "db2" application component
Qiang Xue committed
137 138 139 140
    }
}
```

Alexander Makarov committed
141

Qiang Xue committed
142
Querying Data from Database
Qiang Xue committed
143
---------------------------
Alexander Makarov committed
144

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

147 148
 - [[yii\db\ActiveRecord::find()]]
 - [[yii\db\ActiveRecord::findBySql()]]
Alexander Makarov committed
149

Alexander Makarov committed
150
Both methods return an [[yii\db\ActiveQuery]] instance, which extends [[yii\db\Query]], and thus supports the same set
Qiang Xue committed
151 152
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
153 154 155 156

```php
// to retrieve all *active* customers and order them by their ID:
$customers = Customer::find()
157 158 159
    ->where(['status' => Customer::STATUS_ACTIVE])
    ->orderBy('id')
    ->all();
Alexander Makarov committed
160 161 162

// to return a single customer whose ID is 1:
$customer = Customer::find()
163 164
    ->where(['id' => 1])
    ->one();
Alexander Makarov committed
165 166 167

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

Qiang Xue committed
171 172 173 174 175
// 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:
176
$sql = 'SELECT * FROM customer';
Qiang Xue committed
177 178 179 180 181 182 183
$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.


184 185 186
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
187 188 189

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

// to return an *active* customer whose ID is 1:
Alexander Makarov committed
193
$customer = Customer::findOne([
Qiang Xue committed
194 195 196
    'id' => 1,
    'status' => Customer::STATUS_ACTIVE,
]);
197 198 199 200 201 202 203 204

// 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
205 206
```

Qiang Xue committed
207 208 209 210
> 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()`.
211

Qiang Xue committed
212 213 214 215 216 217 218

### 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
219
// to return customers in terms of arrays rather than `Customer` objects:
Qiang Xue committed
220
$customers = Customer::find()
221 222
    ->asArray()
    ->all();
Qiang Xue committed
223
// each element of $customers is an array of name-value pairs
Alexander Makarov committed
224 225
```

226 227 228
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).
229
When using asArray the result will be returned as a simple array with no typecasting performed 
230
so the result may contain string values for fields that are integer when accessed on the active record object.
Alexander Makarov committed
231

Qiang Xue committed
232
### Retrieving Data in Batches
Alexander Makarov committed
233

234 235
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
236
in Active Record. For example,
237 238 239

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

Alexander Makarov committed
252

Qiang Xue committed
253
Manipulating Data in Database
Qiang Xue committed
254
-----------------------------
Alexander Makarov committed
255

Qiang Xue committed
256 257
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
258

259 260 261 262
- [[yii\db\ActiveRecord::save()|save()]]
- [[yii\db\ActiveRecord::insert()|insert()]]
- [[yii\db\ActiveRecord::update()|update()]]
- [[yii\db\ActiveRecord::delete()|delete()]]
Qiang Xue committed
263 264 265 266 267

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.

268 269 270 271
- [[yii\db\ActiveRecord::updateCounters()|updateCounters()]]
- [[yii\db\ActiveRecord::updateAll()|updateAll()]]
- [[yii\db\ActiveRecord::updateAllCounters()|updateAllCounters()]]
- [[yii\db\ActiveRecord::deleteAll()|deleteAll()]]
Qiang Xue committed
272

Qiang Xue committed
273 274

The following examples show how to use these methods:
Alexander Makarov committed
275 276 277

```php
// to insert a new customer record
278
$customer = new Customer();
Alexander Makarov committed
279 280 281 282 283
$customer->name = 'James';
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->insert();

// to update an existing customer record
Alexander Makarov committed
284
$customer = Customer::findOne($id);
Alexander Makarov committed
285 286 287 288
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->update();

// to delete an existing customer record
Alexander Makarov committed
289
$customer = Customer::findOne($id);
Alexander Makarov committed
290 291
$customer->delete();

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

Qiang Xue committed
295
// to increment the age of ALL customers by 1
Alexander Makarov committed
296
Customer::updateAllCounters(['age' => 1]);
Alexander Makarov committed
297 298
```

Qiang Xue committed
299 300
> 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
301
  If an Active Record is instantiated via the `new` operator, calling `save()` will
302
  insert a row in the table; calling `save()` on an active record fetched from the database will update the corresponding
Alexander Makarov committed
303
  row in the table.
Qiang Xue committed
304

Qiang Xue committed
305

Qiang Xue committed
306 307 308
### Data Input and Validation

Because Active Record extends from [[yii\base\Model]], it supports the same data input and validation features
309
as described in [Model](structure-models.md). For example, you may declare validation rules by overwriting the
Qiang Xue committed
310 311 312 313 314 315
[[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.

316
The following example shows how to use an Active Record to collect/validate user input and save them into the database:
Qiang Xue committed
317 318 319 320 321 322 323 324 325

```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
326
$model = Customer::findOne($id);
Qiang Xue committed
327 328 329 330 331 332 333 334 335
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
336 337
### Loading Default Values

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

```php
$customer = new Customer();
$customer->loadDefaultValues();
Qiang Xue committed
345
// ... render HTML form for $customer ...
346 347
```

348 349 350 351 352 353 354
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();
355
    $this->status = self::STATUS_ACTIVE;
356 357
}
```
Larry Ullman committed
358

Qiang Xue committed
359
Active Record Life Cycles
Larry Ullman committed
360 361
-------------------------

Qiang Xue committed
362 363
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
364
to intercept or respond to these events. They are especially useful for developing Active Record [behaviors](concept-behaviors.md).
Qiang Xue committed
365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386

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

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

Qiang Xue committed
390 391 392
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
393

Qiang Xue committed
394

Qiang Xue committed
395 396
Working with Relational Data
----------------------------
Alexander Makarov committed
397

398 399 400
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
401

Qiang Xue committed
402 403
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
404

405 406
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
407 408 409 410

```php
class Customer extends \yii\db\ActiveRecord
{
411 412 413 414 415
    public function getOrders()
    {
        // Customer has_many Order via Order.customer_id -> id
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
Alexander Makarov committed
416 417 418 419
}

class Order extends \yii\db\ActiveRecord
{
420 421
    public function getCustomer()
    {
422
        // Order has_one Customer via Customer.id -> customer_id
423 424
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
Alexander Makarov committed
425 426 427
}
```

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

Qiang Xue committed
433
 - `$class`: the name of the class of the related model(s). This should be a fully qualified class name.
Qiang Xue committed
434 435 436 437
 - `$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
438

Qiang Xue committed
439 440
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
441 442

```php
Qiang Xue committed
443
// get the orders of a customer
Alexander Makarov committed
444
$customer = Customer::findOne(1);
Alexander Makarov committed
445
$orders = $customer->orders;  // $orders is an array of Order objects
Qiang Xue committed
446 447
```

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

Qiang Xue committed
450
```sql
451 452
SELECT * FROM customer WHERE id=1;
SELECT * FROM order WHERE customer_id=1;
Alexander Makarov committed
453 454
```

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

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
463 464 465 466

```php
class Customer extends \yii\db\ActiveRecord
{
467 468 469 470 471 472
    public function getBigOrders($threshold = 100)
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])
            ->where('subtotal > :threshold', [':threshold' => $threshold])
            ->orderBy('id');
    }
Alexander Makarov committed
473 474 475
}
```

476 477
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
478 479 480 481 482 483 484 485

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

486 487
> 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]],
488
an array of that, or null, depending on the multiplicity of the relation. For example, `$customer->getOrders()` returns
489
an `ActiveQuery` instance, while `$customer->orders` returns an array of `Order` objects (or an empty array if
Qiang Xue committed
490
the query results in nothing).
Qiang Xue committed
491

Qiang Xue committed
492

493 494
Relations with Junction Table
-----------------------------
Qiang Xue committed
495

496
Sometimes, two tables are related together via an intermediary table called a [junction table][]. To declare such relations,
497 498
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
499

500
For example, if table `order` and table `item` are related via the junction table `order_item`,
Alexander Makarov committed
501 502 503 504 505
we can declare the `items` relation in the `Order` class like the following:

```php
class Order extends \yii\db\ActiveRecord
{
506 507 508
    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
509
            ->viaTable('order_item', ['order_id' => 'id']);
510
    }
Alexander Makarov committed
511 512 513
}
```

514 515
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
516
instead of the junction table name. For example, the above `items` relation can be equivalently declared as follows:
Alexander Makarov committed
517 518 519 520

```php
class Order extends \yii\db\ActiveRecord
{
521 522 523 524 525 526 527 528 529 530
    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
531 532 533
}
```

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

Alexander Makarov committed
536

Qiang Xue committed
537 538 539
Lazy and Eager Loading
----------------------

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

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

```php
556
// SQL executed: SELECT * FROM customer LIMIT 100
Alexander Makarov committed
557 558 559
$customers = Customer::find()->limit(100)->all();

foreach ($customers as $customer) {
560
    // SQL executed: SELECT * FROM order WHERE customer_id=...
561 562
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
563 564 565 566 567
}
```

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

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

```php
573 574
// SQL executed: SELECT * FROM customer LIMIT 100;
//               SELECT * FROM orders WHERE customer_id IN (1,2,...)
Alexander Makarov committed
575
$customers = Customer::find()->limit(100)
576
    ->with('orders')->all();
Alexander Makarov committed
577 578

foreach ($customers as $customer) {
579 580 581
    // no SQL executed
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
582 583 584
}
```

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

Qiang Xue committed
591 592 593 594 595 596 597 598
> 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
599

Qiang Xue committed
600
Sometimes, you may want to customize the relational queries on the fly. This can be
Alexander Makarov committed
601 602 603
done for both lazy loading and eager loading. For example,

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

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


618 619 620 621 622 623 624 625 626
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
{
627 628 629 630 631
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
632 633 634 635
}

class Order extends ActiveRecord
{
636 637 638 639 640
    ....
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
641 642 643 644 645 646 647 648
}
```

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

To avoid the redundant execution of the last SQL statement, we could declare the inverse relations for the `customer`
662
and the `orders` relations by calling the [[yii\db\ActiveQuery::inverseOf()|inverseOf()]] method, like the following:
663 664 665 666

```php
class Customer extends ActiveRecord
{
667 668 669 670 671
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])->inverseOf('customer');
    }
672 673 674 675 676 677
}
```

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

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

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

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

> Note: Inverse relation cannot be defined with a relation that involves pivoting tables.
705 706
> 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.
707 708


709
Joining with Relations <a name="joining-with-relations"></a>
710 711 712
----------------------

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

```php
718
// find all orders and sort the orders by the customer id and the order id. also eager loading "customer"
719
$orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all();
720
// find all orders that contain books, and eager loading "books"
721
$orders = Order::find()->innerJoinWith('books')->all();
722 723
```

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

727 728
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
729 730 731

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

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

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

Below are some more examples,

```php
770
// find all orders that contain books, but do not eager load "books".
771
$orders = Order::find()->innerJoinWith('books', false)->all();
772
// which is equivalent to the above
773
$orders = Order::find()->joinWith('books', false, 'INNER JOIN')->all();
Qiang Xue committed
774
```
775

776
Sometimes when joining two tables, you may need to specify some extra condition in the ON part of the JOIN query.
777
This can be done by calling the [[yii\db\ActiveQuery::onCondition()]] method like the following:
778 779 780 781

```php
class User extends ActiveRecord
{
782 783 784 785
    public function getBooks()
    {
        return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
    }
786 787 788
}
```

789 790
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
791 792
to specify that only items whose `category_id` is 1 should be returned.

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

```php
797 798
// 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
799
$users = User::find()->joinWith('books')->all();
800 801
```

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

```php
806
// SELECT * FROM user WHERE id=10
Alexander Makarov committed
807
$user = User::findOne(10);
808
// SELECT * FROM item WHERE owner_id=10 AND category_id=1
809 810 811
$books = $user->books;
```

812

Alexander Makarov committed
813 814 815 816 817 818
Working with Relationships
--------------------------

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

819 820
- [[yii\db\ActiveRecord::link()|link()]]
- [[yii\db\ActiveRecord::unlink()|unlink()]]
Alexander Makarov committed
821 822 823 824 825

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
826
$customer = Customer::findOne(1);
827
$order = new Order();
Alexander Makarov committed
828 829 830 831
$order->subtotal = 100;
$customer->link('orders', $order);
```

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


836 837 838
Cross-DBMS Relations
--------------------

839
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:
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 867 868 869 870 871 872

```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
873
All Active Record features like eager and lazy loading, establishing and breaking a relationship and so on, are
874 875
available for cross-DBMS relations.

Klimov Paul committed
876 877 878
> 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.
879 880


Qiang Xue committed
881 882
Scopes
------
Alexander Makarov committed
883

884 885 886 887
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.
888

Qiang Xue committed
889
It is possible that you may want to call the same set of query methods in different places. If this is the case,
890
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
891

892
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
893 894
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
895 896

```php
897 898
namespace app\models;

899
use yii\db\ActiveQuery;
900 901

class CommentQuery extends ActiveQuery
Alexander Makarov committed
902
{
903 904 905 906 907
    public function active($state = true)
    {
        $this->andWhere(['active' => $state]);
        return $this;
    }
908 909
}
```
Alexander Makarov committed
910

911 912 913 914
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.
915
3. Check [[yii\db\ActiveQuery]] methods that are very useful for modifying query conditions.
916

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

Carsten Brandt committed
920
```php
921 922 923 924 925 926
namespace app\models;

use yii\db\ActiveRecord;

class Comment extends ActiveRecord
{
Alexander Makarov committed
927 928 929 930 931
    /**
     * @inheritdoc
     * @return CommentQuery
     */
    public static function find()
932
    {
933
        return new CommentQuery(get_called_class());
934
    }
Alexander Makarov committed
935
}
936
```
Alexander Makarov committed
937

938 939 940
That's it. Now you can use your custom scope methods:

```php
941
$comments = Comment::find()->active()->all();
942
$inactiveComments = Comment::find()->active(false)->all();
943 944 945 946 947 948 949
```

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

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

954
    }
955
}
Alexander Makarov committed
956 957
```

958
Or use the scopes on-the-fly when performing a relational query:
959 960 961

```php
$posts = Post::find()->with([
962 963 964
    'comments' => function($q) {
        $q->active();
    }
965 966
])->all();
```
Alexander Makarov committed
967

968 969 970
### 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
971
applies to ALL queries. You can define a default scope easily by overriding [[yii\db\ActiveRecord::find()]]. For example,
972 973

```php
Alexander Makarov committed
974
public static function find()
975
{
Alexander Makarov committed
976
    return parent::find()->where(['deleted' => false]);
977 978 979 980 981 982 983 984
}
```

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
985
Transactional operations
986
---------------------
987

988
There are two ways of dealing with transactions while working with Active Record. First way is doing everything manually
989 990
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:
991 992

```php
993
class Post extends \yii\db\ActiveRecord
994
{
995 996 997 998 999 1000 1001 1002 1003 1004
    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,
        ];
    }
}
1005 1006
```

1007 1008
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`.
1009
`OP_ALL` stands for all three.
1010

1011 1012
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.
1013

1014 1015
Optimistic Locks
--------------
1016

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

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

1024
To use Optimistic locking:
Qiang Xue committed
1025

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

Dirty Attributes
1035
--------------
Qiang Xue committed
1036

1037
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
1038

Alexander Makarov committed
1039 1040 1041
See also
--------

1042
- [Model](structure-models.md)
1043
- [[yii\db\ActiveRecord]]