db-active-record.md 38.2 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 13 14
For example, assume `Customer` is an Active Record class is associated with the `customer` table
and `name` is a column of `customer` table. You can write the following code to insert a new
row into `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 36 37 38 39 40 41 42 43 44 45 46 47
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]]
* Microsoft SQL Server 2010 or later: via [[yii\db\ActiveRecord]]
* Oracle: via [[yii\db\ActiveRecord]]
* CUBRID 9.1 or later: via [[yii\db\ActiveRecord]]
* Sphnix: via [[yii\sphinx\ActiveRecord]], requires `yii2-sphinx` extension
* ElasticSearch: via [[yii\elasticsearch\ActiveRecord]], requires `yii2-elasticsearch` extension
* Redis 2.6.12 or later: via [[yii\redis\ActiveRecord]], requires `yii2-redis` extension
* MongoDB 1.3.0 or later: via [[yii\mongodb\ActiveRecord]], requires `yii2-mongodb` extension

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
48

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

Qiang Xue committed
52 53
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
54 55

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

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

class Customer extends ActiveRecord
Alexander Makarov committed
61
{
62 63 64 65 66
    /**
     * @return string the name of the table associated with this ActiveRecord class.
     */
    public static function tableName()
    {
67
        return 'customer';
68
    }
Alexander Makarov committed
69 70 71
}
```

Larry Ullman committed
72

Qiang Xue committed
73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88
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
// "id" and "email" are the names of columns in the table associated with $customer ActiveRecord object
$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
89

Qiang Xue committed
90 91 92 93
```php
$customer->email = 'jane@example.com';
$customer->save();
```
Larry Ullman committed
94

Qiang Xue committed
95 96

Connecting to Database
Alexander Makarov committed
97 98
----------------------

Qiang Xue committed
99
Active Record uses a [[yii\db\Connection|DB connection]] to exchange data with database. By default,
100
it uses the `db` application component as the connection. As explained in [Database basics](db-dao.md),
Qiang Xue committed
101
you may configure the `db` component in the application configuration file like follows,
Alexander Makarov committed
102 103

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

Qiang Xue committed
116 117 118 119 120 121 122 123 124 125 126 127 128 129 130
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()
    {
        return \Yii::$app->db2;  // use "db2" application component
    }
}
```

Alexander Makarov committed
131

Qiang Xue committed
132
Querying Data from Database
Qiang Xue committed
133
---------------------------
Alexander Makarov committed
134

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

137 138
 - [[yii\db\ActiveRecord::find()]]
 - [[yii\db\ActiveRecord::findBySql()]]
Alexander Makarov committed
139

Alexander Makarov committed
140
Both methods return an [[yii\db\ActiveQuery]] instance, which extends [[yii\db\Query]], and thus supports the same set
Qiang Xue committed
141 142
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
143 144 145 146

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

// to return a single customer whose ID is 1:
$customer = Customer::find()
153 154
    ->where(['id' => 1])
    ->one();
Alexander Makarov committed
155 156 157

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

Qiang Xue committed
161 162 163 164 165
// 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:
166
$sql = 'SELECT * FROM customer';
Qiang Xue committed
167 168 169 170 171 172 173
$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.


174 175 176
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
177 178 179

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

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

// 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
195 196 197 198 199 200 201 202 203
```


### 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
204
// to return customers in terms of arrays rather than `Customer` objects:
Qiang Xue committed
205
$customers = Customer::find()
206 207
    ->asArray()
    ->all();
Qiang Xue committed
208
// each element of $customers is an array of name-value pairs
Alexander Makarov committed
209 210
```

211 212 213 214 215
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).
When using asArray the result will be returned just as such a query and no typecasting is performed anymore
so the result may contain string values for fields that are integer when accessed on the active record object.
Alexander Makarov committed
216

Qiang Xue committed
217
### Retrieving Data in Batches
Alexander Makarov committed
218

219
In [Query Builder](db-query-builder.md), we have explained that you may use *batch query* to keep your memory
Qiang Xue committed
220 221
usage under a limit when querying a large amount of data from database. You may use the same technique
in Active Record. For example,
222 223 224

```php
// fetch 10 customers at a time
Qiang Xue committed
225
foreach (Customer::find()->batch(10) as $customers) {
226
    // $customers is an array of 10 or fewer Customer objects
227
}
Qiang Xue committed
228 229
// fetch 10 customers at a time and iterate them one by one
foreach (Customer::find()->each(10) as $customer) {
230
    // $customer is a Customer object
231 232
}
// batch query with eager loading
Qiang Xue committed
233
foreach (Customer::find()->with('orders')->each() as $customer) {
234 235 236
}
```

Alexander Makarov committed
237

Qiang Xue committed
238
Manipulating Data in Database
Qiang Xue committed
239
-----------------------------
Alexander Makarov committed
240

Qiang Xue committed
241 242
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
243

244 245 246 247
- [[yii\db\ActiveRecord::save()|save()]]
- [[yii\db\ActiveRecord::insert()|insert()]]
- [[yii\db\ActiveRecord::update()|update()]]
- [[yii\db\ActiveRecord::delete()|delete()]]
Qiang Xue committed
248 249 250 251 252

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.

253 254 255 256
- [[yii\db\ActiveRecord::updateCounters()|updateCounters()]]
- [[yii\db\ActiveRecord::updateAll()|updateAll()]]
- [[yii\db\ActiveRecord::updateAllCounters()|updateAllCounters()]]
- [[yii\db\ActiveRecord::deleteAll()|deleteAll()]]
Qiang Xue committed
257

Qiang Xue committed
258 259

The following examples show how to use these methods:
Alexander Makarov committed
260 261 262

```php
// to insert a new customer record
263
$customer = new Customer();
Alexander Makarov committed
264 265 266 267 268
$customer->name = 'James';
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->insert();

// to update an existing customer record
Alexander Makarov committed
269
$customer = Customer::findOne($id);
Alexander Makarov committed
270 271 272 273
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->update();

// to delete an existing customer record
Alexander Makarov committed
274
$customer = Customer::findOne($id);
Alexander Makarov committed
275 276
$customer->delete();

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

Qiang Xue committed
280
// to increment the age of ALL customers by 1
Alexander Makarov committed
281
Customer::updateAllCounters(['age' => 1]);
Alexander Makarov committed
282 283
```

Qiang Xue committed
284 285
> 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
286
  If an Active Record is instantiated via the `new` operator, calling `save()` will
Alexander Makarov committed
287 288
  insert a row in the table; calling `save()` on active record fetched from database will update the corresponding
  row in the table.
Qiang Xue committed
289

Qiang Xue committed
290

Qiang Xue committed
291 292 293
### Data Input and Validation

Because Active Record extends from [[yii\base\Model]], it supports the same data input and validation features
294
as described in [Model](structure-models.md). For example, you may declare validation rules by overwriting the
Qiang Xue committed
295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310
[[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.

The following example shows how to use an Active Record to collect/validate user input and save them into database:

```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
311
$model = Customer::findOne($id);
Qiang Xue committed
312 313 314 315 316 317 318 319 320
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
321 322
### Loading Default Values

Qiang Xue committed
323 324 325
Your table columns may be defined with default values. Sometimes, you may want to pre-populate your
Web form for an Active Record with these values. To do so, call the `loadDefaultValues()` method before
rendering the form:
326 327 328 329

```php
$customer = new Customer();
$customer->loadDefaultValues();
Qiang Xue committed
330
// ... render HTML form for $customer ...
331 332
```

Larry Ullman committed
333

Qiang Xue committed
334
Active Record Life Cycles
Larry Ullman committed
335 336
-------------------------

Qiang Xue committed
337 338
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
339
to intercept or respond to these events. They are especially useful for developing Active Record [behaviors](concept-behaviors.md).
Qiang Xue committed
340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363

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

And Finally when calling [[yii\db\ActiveRecord::delete()|delete()]] to delete an ActiveRecord, we will have
the following life cycles:
Larry Ullman committed
364

Qiang Xue committed
365 366 367
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
368

Qiang Xue committed
369

Qiang Xue committed
370 371
Working with Relational Data
----------------------------
Alexander Makarov committed
372

373 374 375
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
376

Qiang Xue committed
377 378
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
379

380 381
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
382 383 384 385

```php
class Customer extends \yii\db\ActiveRecord
{
386 387 388 389 390
    public function getOrders()
    {
        // Customer has_many Order via Order.customer_id -> id
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
Alexander Makarov committed
391 392 393 394
}

class Order extends \yii\db\ActiveRecord
{
395 396
    public function getCustomer()
    {
397
        // Order has_one Customer via Customer.id -> customer_id
398 399
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
Alexander Makarov committed
400 401 402
}
```

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

Qiang Xue committed
408
 - `$class`: the name of the class of the related model(s). This should be a fully qualified class name.
Qiang Xue committed
409 410 411 412
 - `$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
413

Qiang Xue committed
414 415
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
416 417

```php
Qiang Xue committed
418
// get the orders of a customer
Alexander Makarov committed
419
$customer = Customer::findOne(1);
Alexander Makarov committed
420
$orders = $customer->orders;  // $orders is an array of Order objects
Qiang Xue committed
421 422 423
```

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

Qiang Xue committed
425
```sql
426 427
SELECT * FROM customer WHERE id=1;
SELECT * FROM order WHERE customer_id=1;
Alexander Makarov committed
428 429
```

430 431
> 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
432 433 434 435 436 437
accesses will only return the previously fetched results that are cached internally. If you want to re-query
the relational data, simply unset the existing one first: `unset($customer->orders);`.

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

```php
class Customer extends \yii\db\ActiveRecord
{
442 443 444 445 446 447
    public function getBigOrders($threshold = 100)
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])
            ->where('subtotal > :threshold', [':threshold' => $threshold])
            ->orderBy('id');
    }
Alexander Makarov committed
448 449 450
}
```

451 452
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
453 454 455 456 457 458 459 460

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

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

Qiang Xue committed
467 468 469 470

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

471 472 473
Sometimes, two tables are related together via an intermediary table called [pivot table][]. To declare such relations,
we can customize the [[yii\db\ActiveQuery]] object by calling its [[yii\db\ActiveQuery::via()|via()]] or
[[yii\db\ActiveQuery::viaTable()|viaTable()]] method.
Alexander Makarov committed
474

475
For example, if table `order` and table `item` are related via pivot table `order_item`,
Alexander Makarov committed
476 477 478 479 480
we can declare the `items` relation in the `Order` class like the following:

```php
class Order extends \yii\db\ActiveRecord
{
481 482 483
    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
484
            ->viaTable('order_item', ['order_id' => 'id']);
485
    }
Alexander Makarov committed
486 487 488
}
```

489 490
The [[yii\db\ActiveQuery::via()|via()]] method is similar to [[yii\db\ActiveQuery::viaTable()|viaTable()]] except that
the first parameter of [[yii\db\ActiveQuery::via()|via()]] takes a relation name declared in the ActiveRecord class
Qiang Xue committed
491
instead of the pivot table name. For example, the above `items` relation can be equivalently declared as follows:
Alexander Makarov committed
492 493 494 495

```php
class Order extends \yii\db\ActiveRecord
{
496 497 498 499 500 501 502 503 504 505
    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
506 507 508
}
```

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

Alexander Makarov committed
511

Qiang Xue committed
512 513 514 515
Lazy and Eager Loading
----------------------

As described earlier, when you access the related objects the first time, ActiveRecord will perform a DB query
Alexander Makarov committed
516 517 518 519
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
520
// SQL executed: SELECT * FROM customer WHERE id=1
Alexander Makarov committed
521
$customer = Customer::findOne(1);
522
// SQL executed: SELECT * FROM order WHERE customer_id=1
Alexander Makarov committed
523 524 525 526 527
$orders = $customer->orders;
// no SQL executed
$orders2 = $customer->orders;
```

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

```php
531
// SQL executed: SELECT * FROM customer LIMIT 100
Alexander Makarov committed
532 533 534
$customers = Customer::find()->limit(100)->all();

foreach ($customers as $customer) {
535
    // SQL executed: SELECT * FROM order WHERE customer_id=...
536 537
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
538 539 540 541 542
}
```

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

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

```php
548 549
// SQL executed: SELECT * FROM customer LIMIT 100;
//               SELECT * FROM orders WHERE customer_id IN (1,2,...)
Alexander Makarov committed
550
$customers = Customer::find()->limit(100)
551
    ->with('orders')->all();
Alexander Makarov committed
552 553

foreach ($customers as $customer) {
554 555 556
    // no SQL executed
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
557 558 559
}
```

Qiang Xue committed
560 561 562 563 564
As you can see, only two SQL queries are needed for the same task!

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

Qiang Xue committed
566 567 568 569 570 571 572 573
> 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
574

Qiang Xue committed
575
Sometimes, you may want to customize the relational queries on the fly. This can be
Alexander Makarov committed
576 577 578
done for both lazy loading and eager loading. For example,

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

583 584
// eager loading: SELECT * FROM customer LIMIT 100
//                SELECT * FROM order WHERE customer_id IN (1,2,...) AND subtotal>100
Alexander Makarov committed
585
$customers = Customer::find()->limit(100)->with([
586 587 588
    'orders' => function($query) {
        $query->andWhere('subtotal>100');
    },
Alexander Makarov committed
589
])->all();
Alexander Makarov committed
590 591 592
```


593 594 595 596 597 598 599 600 601
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
{
602 603 604 605 606
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
607 608 609 610
}

class Order extends ActiveRecord
{
611 612 613 614 615
    ....
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
616 617 618 619 620 621 622 623
}
```

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
624
// SELECT * FROM customer WHERE id=1
Alexander Makarov committed
625
$customer = Customer::findOne(1);
626
// echoes "not equal"
627 628
// SELECT * FROM order WHERE customer_id=1
// SELECT * FROM customer WHERE id=1
629
if ($customer->orders[0]->customer === $customer) {
630
    echo 'equal';
631
} else {
632
    echo 'not equal';
633 634 635 636
}
```

To avoid the redundant execution of the last SQL statement, we could declare the inverse relations for the `customer`
637
and the `orders` relations by calling the [[yii\db\ActiveQuery::inverseOf()|inverseOf()]] method, like the following:
638 639 640 641

```php
class Customer extends ActiveRecord
{
642 643 644 645 646
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])->inverseOf('customer');
    }
647 648 649 650 651 652
}
```

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

```php
653
// SELECT * FROM customer WHERE id=1
Alexander Makarov committed
654
$customer = Customer::findOne(1);
655
// echoes "equal"
656
// SELECT * FROM order WHERE customer_id=1
657
if ($customer->orders[0]->customer === $customer) {
658
    echo 'equal';
659
} else {
660
    echo 'not equal';
661 662 663 664 665 666 667
}
```

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

```php
668 669
// SELECT * FROM customer
// SELECT * FROM order WHERE customer_id IN (1, 2, ...)
670 671 672
$customers = Customer::find()->with('orders')->all();
// echoes "equal"
if ($customers[0]->orders[0]->customer === $customers[0]) {
673
    echo 'equal';
674
} else {
675
    echo 'not equal';
676 677 678 679
}
```

> Note: Inverse relation cannot be defined with a relation that involves pivoting tables.
680 681
> 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.
682 683


684 685 686 687
Joining with Relations
----------------------

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

```php
693
// find all orders and sort the orders by the customer id and the order id. also eager loading "customer"
694
$orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all();
695
// find all orders that contain books, and eager loading "books"
696
$orders = Order::find()->innerJoinWith('books')->all();
697 698
```

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

702 703
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
704 705 706 707

```php
// join with multiple relations
// find out the orders that contain books and are placed by customers who registered within the past 24 hours
708
$orders = Order::find()->innerJoinWith([
709 710
    'books',
    'customer' => function ($query) {
711
        $query->where('customer.created_at > ' . (time() - 24 * 3600));
712
    }
Qiang Xue committed
713 714 715 716 717
])->all();
// join with sub-relations: join with books and books' authors
$orders = Order::find()->joinWith('books.author')->all();
```

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

Carsten Brandt committed
722
The difference between [[yii\db\ActiveQuery::joinWith()|joinWith()]] and [[yii\db\ActiveQuery::with()|with()]] is that
723 724 725 726 727 728 729 730
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
731
When using [[yii\db\ActiveQuery::joinWith()|joinWith()]], you are responsible to disambiguate column names.
732
In the above examples, we use `item.id` and `order.id` to disambiguate the `id` column references
733 734
because both of the order table and the item table contain a column named `id`.

Qiang Xue committed
735 736 737
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
738
And also by default, [[yii\db\ActiveQuery::joinWith()|joinWith()]] uses `LEFT JOIN` to join the related tables.
739
You may pass it with the `$joinType` parameter to customize the join type. As a shortcut to the `INNER JOIN` type,
Carsten Brandt committed
740
you may use [[yii\db\ActiveQuery::innerJoinWith()|innerJoinWith()]].
Qiang Xue committed
741 742 743 744 745

Below are some more examples,

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

751
Sometimes when joining two tables, you may need to specify some extra condition in the ON part of the JOIN query.
752
This can be done by calling the [[yii\db\ActiveQuery::onCondition()]] method like the following:
753 754 755 756

```php
class User extends ActiveRecord
{
757 758 759 760
    public function getBooks()
    {
        return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
    }
761 762 763
}
```

764 765
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
766 767
to specify that only items whose `category_id` is 1 should be returned.

Carsten Brandt committed
768
When you perform query using [[yii\db\ActiveQuery::joinWith()|joinWith()]], the on-condition will be put in the ON part
769 770 771
of the corresponding JOIN query. For example,

```php
772 773
// 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
774
$users = User::find()->joinWith('books')->all();
775 776
```

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

```php
781
// SELECT * FROM user WHERE id=10
Alexander Makarov committed
782
$user = User::findOne(10);
783
// SELECT * FROM item WHERE owner_id=10 AND category_id=1
784 785 786
$books = $user->books;
```

787

Alexander Makarov committed
788 789 790 791 792 793
Working with Relationships
--------------------------

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

794 795
- [[yii\db\ActiveRecord::link()|link()]]
- [[yii\db\ActiveRecord::unlink()|unlink()]]
Alexander Makarov committed
796 797 798 799 800

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
801
$customer = Customer::findOne(1);
802
$order = new Order();
Alexander Makarov committed
803 804 805 806
$order->subtotal = 100;
$customer->link('orders', $order);
```

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


811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848
Cross-DBMS Relations
--------------------

ActiveRecord allows to establish relationship between entities from different DBMS. For example: between relational
database table and MongoDB collection. Such relation does not require any special code:

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

Klimov Paul committed
852 853 854
> 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.
855 856


Qiang Xue committed
857 858
Scopes
------
Alexander Makarov committed
859

860 861 862 863
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.
864

Qiang Xue committed
865 866 867 868 869 870 871
It is possible that you may want to call the same set of query methods in different places. If this is the case,
you should consider defining the so-called *scopes*. A scope is essentially a method defined in a custom query class that
calls a set of query methods to modify the query object. You can then use a scope like calling a normal query method.

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

```php
874 875
namespace app\models;

876
use yii\db\ActiveQuery;
877 878

class CommentQuery extends ActiveQuery
Alexander Makarov committed
879
{
880 881 882 883 884
    public function active($state = true)
    {
        $this->andWhere(['active' => $state]);
        return $this;
    }
885 886
}
```
Alexander Makarov committed
887

888 889 890 891
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.
892
3. Check [[yii\db\ActiveQuery]] methods that are very useful for modifying query conditions.
893

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

Carsten Brandt committed
897
```php
898 899 900 901 902 903
namespace app\models;

use yii\db\ActiveRecord;

class Comment extends ActiveRecord
{
Alexander Makarov committed
904 905 906 907 908
    /**
     * @inheritdoc
     * @return CommentQuery
     */
    public static function find()
909
    {
910
        return new CommentQuery(get_called_class());
911
    }
Alexander Makarov committed
912
}
913
```
Alexander Makarov committed
914

915 916 917
That's it. Now you can use your custom scope methods:

```php
918
$comments = Comment::find()->active()->all();
919
$inactiveComments = Comment::find()->active(false)->all();
920 921 922 923 924 925 926
```

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

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

931
    }
932
}
Alexander Makarov committed
933 934
```

935 936 937 938
Or use the scopes on-the-fly when performing relational query:

```php
$posts = Post::find()->with([
939 940 941
    'comments' => function($q) {
        $q->active();
    }
942 943
])->all();
```
Alexander Makarov committed
944

945 946 947
### 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
948
applies to ALL queries. You can define a default scope easily by overriding [[yii\db\ActiveRecord::find()]]. For example,
949 950

```php
Alexander Makarov committed
951
public static function find()
952
{
Alexander Makarov committed
953
    return parent::find()->where(['deleted' => false]);
954 955 956 957 958 959 960 961
}
```

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
962
Transactional operations
963
---------------------
964

965 966 967
There are two ways of dealing with transactions while working with Active Record. First way is doing everything manually
as described in "transactions" section of "[Database basics](db-dao.md)". Another way is to do it by implementing
`transactions` method where you can specify which operations are to be wrapped into transaction per model scenario:
968 969

```php
970
class Post extends \yii\db\ActiveRecord
971
{
972 973 974 975 976 977 978 979 980 981
    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,
        ];
    }
}
982 983
```

984 985 986
In the above `admin` and `api` are model scenarios and constants starting with `OP_` are operations that should
be wrapped in transaction for these sceanarios. Supported operations are `OP_INSERT`, `OP_UPDATE` and `OP_DELETE`.
`OP_ALL` stands for all three.
987

988 989
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.
990

991 992
Optimistic Locks
--------------
993

994 995 996 997
Optimistic locking allows multiple users to access the same record for edits and avoids
potential conflicts. In case when a user attempts to save the record upon some staled data
(because another user has modified the data), a [[\yii\db\StaleObjectException]] exception will be thrown,
and the update or deletion is skipped.
998

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

1001
To use Optimistic locking:
Qiang Xue committed
1002

1003 1004 1005 1006 1007 1008 1009
1. Create a column to store the version number of each row. The column type should be `BIGINT DEFAULT 0`.
   Override `optimisticLock()` method to return the name of this column.
2. In the Web form that collects the user input, add a hidden field that stores
   the lock version of the recording being updated.
3. In the controller action that does the data updating, try to catch the [[\yii\db\StaleObjectException]]
   and implement necessary business logic (e.g. merging the changes, prompting stated data)
   to resolve the conflict.
Qiang Xue committed
1010 1011

Dirty Attributes
1012
--------------
Qiang Xue committed
1013

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

Alexander Makarov committed
1018 1019 1020
See also
--------

1021
- [Model](structure-models.md)
1022
- [[yii\db\ActiveRecord]]