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

Qiang Xue committed
4 5 6 7 8
[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
9

10 11 12
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
13 14 15 16

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

The above code is equivalent to using the following raw SQL statement, which is less
intuitive, more error prone, and may have compatibility problem for different DBMS:

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

Qiang Xue committed
29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
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
46

Qiang Xue committed
47
Declaring Active Record Classes
Alexander Makarov committed
48 49
------------------------------

Qiang Xue committed
50 51
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
52 53

```php
Qiang Xue committed
54 55
namespace app\models;

Qiang Xue committed
56 57 58
use yii\db\ActiveRecord;

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

Larry Ullman committed
70

Qiang Xue committed
71 72 73 74 75 76 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
// "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
87

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

Qiang Xue committed
93 94

Connecting to Database
Alexander Makarov committed
95 96
----------------------

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

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

Qiang Xue committed
114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
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
129

Qiang Xue committed
130
Querying Data from Database
Qiang Xue committed
131
---------------------------
Alexander Makarov committed
132

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

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

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

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

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

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

Qiang Xue committed
159 160 161 162 163
// 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:
164
$sql = 'SELECT * FROM customer';
Qiang Xue committed
165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194
$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.


The `find()` method also supports the following shortcut usage which allows you to retrieve an Active Record
instance based on a primary key value or a set of column values. The main difference here is that instead of
returning a [[yii\db\ActiveQuery]] instance, the method takes the column value(s) and returns an Active Record
instance directly without the need to call `one()`.

```php
// to return a single customer whose ID is 1:
$customer = Customer::find(1);

// to return an *active* customer whose ID is 1:
$customer = Customer::find([
    'id' => 1,
    'status' => Customer::STATUS_ACTIVE,
]);
```


### 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
195
// to return customers in terms of arrays rather than `Customer` objects:
Qiang Xue committed
196
$customers = Customer::find()
197 198
    ->asArray()
    ->all();
Qiang Xue committed
199
// each element of $customers is an array of name-value pairs
Alexander Makarov committed
200 201
```

Alexander Makarov committed
202

Qiang Xue committed
203
### Retrieving Data in Batches
Alexander Makarov committed
204

Qiang Xue committed
205 206 207
In [Query Builder](query-builder.md), we have explained that you may use *batch query* to keep your memory
usage under a limit when querying a large amount of data from database. You may use the same technique
in Active Record. For example,
208 209 210

```php
// fetch 10 customers at a time
Qiang Xue committed
211
foreach (Customer::find()->batch(10) as $customers) {
212
    // $customers is an array of 10 or fewer Customer objects
213
}
Qiang Xue committed
214 215
// fetch 10 customers at a time and iterate them one by one
foreach (Customer::find()->each(10) as $customer) {
216
    // $customer is a Customer object
217 218
}
// batch query with eager loading
Qiang Xue committed
219
foreach (Customer::find()->with('orders')->each() as $customer) {
220 221 222
}
```

Alexander Makarov committed
223

Qiang Xue committed
224
Manipulating Data in Database
Qiang Xue committed
225
-----------------------------
Alexander Makarov committed
226

Qiang Xue committed
227 228
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
229

230 231 232 233
- [[yii\db\ActiveRecord::save()|save()]]
- [[yii\db\ActiveRecord::insert()|insert()]]
- [[yii\db\ActiveRecord::update()|update()]]
- [[yii\db\ActiveRecord::delete()|delete()]]
Qiang Xue committed
234 235 236 237 238

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.

239 240 241 242
- [[yii\db\ActiveRecord::updateCounters()|updateCounters()]]
- [[yii\db\ActiveRecord::updateAll()|updateAll()]]
- [[yii\db\ActiveRecord::updateAllCounters()|updateAllCounters()]]
- [[yii\db\ActiveRecord::deleteAll()|deleteAll()]]
Qiang Xue committed
243

Qiang Xue committed
244 245

The following examples show how to use these methods:
Alexander Makarov committed
246 247 248

```php
// to insert a new customer record
249
$customer = new Customer();
Alexander Makarov committed
250 251 252 253 254 255 256 257 258 259 260 261 262
$customer->name = 'James';
$customer->email = 'james@example.com';
$customer->save();  // equivalent to $customer->insert();

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

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

Qiang Xue committed
263
// to increment the age of ALL customers by 1
Alexander Makarov committed
264
Customer::updateAllCounters(['age' => 1]);
Alexander Makarov committed
265 266
```

Qiang Xue committed
267 268
> 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
269
  If an Active Record is instantiated via the `new` operator, calling `save()` will
Qiang Xue committed
270
  insert a row in the table; if an Active Record is obtained by `find()`, calling `save()` will
Qiang Xue committed
271 272
  update the corresponding row in the table.

Qiang Xue committed
273

Qiang Xue committed
274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303
### Data Input and Validation

Because Active Record extends from [[yii\base\Model]], it supports the same data input and validation features
as described in [Model](model.md). For example, you may declare validation rules by overwriting the
[[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
$model = Customer::find($id);
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
304 305
### Loading Default Values

Qiang Xue committed
306 307 308
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:
309 310 311 312

```php
$customer = new Customer();
$customer->loadDefaultValues();
Qiang Xue committed
313
// ... render HTML form for $customer ...
314 315
```

Larry Ullman committed
316

Qiang Xue committed
317
Active Record Life Cycles
Larry Ullman committed
318 319
-------------------------

Qiang Xue committed
320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346
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
to intercept or respond to these events. They are especially useful for developing Active Record [behaviors](behaviors.md).

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
347

Qiang Xue committed
348 349 350
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
351

Qiang Xue committed
352

Qiang Xue committed
353 354
Working with Relational Data
----------------------------
Alexander Makarov committed
355

356 357 358
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
359

Qiang Xue committed
360 361
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
362

363 364
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
365 366 367 368

```php
class Customer extends \yii\db\ActiveRecord
{
369 370 371 372 373
    public function getOrders()
    {
        // Customer has_many Order via Order.customer_id -> id
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
Alexander Makarov committed
374 375 376 377
}

class Order extends \yii\db\ActiveRecord
{
378 379 380 381 382
    // Order has_one Customer via Customer.id -> customer_id
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
Alexander Makarov committed
383 384 385
}
```

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

Qiang Xue committed
391
 - `$class`: the name of the class of the related model(s). This should be a fully qualified class name.
Qiang Xue committed
392 393 394 395
 - `$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
396

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

```php
Qiang Xue committed
401 402
// get the orders of a customer
$customer = Customer::find(1);
Alexander Makarov committed
403
$orders = $customer->orders;  // $orders is an array of Order objects
Qiang Xue committed
404 405 406
```

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

Qiang Xue committed
408
```sql
409 410
SELECT * FROM customer WHERE id=1;
SELECT * FROM order WHERE customer_id=1;
Alexander Makarov committed
411 412
```

413 414
> 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
415 416 417 418 419 420
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
421 422 423 424

```php
class Customer extends \yii\db\ActiveRecord
{
425 426 427 428 429 430
    public function getBigOrders($threshold = 100)
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])
            ->where('subtotal > :threshold', [':threshold' => $threshold])
            ->orderBy('id');
    }
Alexander Makarov committed
431 432 433
}
```

434 435
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
436 437 438 439 440 441 442 443

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

444 445
> 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
446
an array of that, or null, depending the multiplicity of the relation. For example, `$customer->getOrders()` returns
447
an `ActiveQuery` instance, while `$customer->orders` returns an array of `Order` objects (or an empty array if
Qiang Xue committed
448
the query results in nothing).
Qiang Xue committed
449

Qiang Xue committed
450 451 452 453

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

454 455 456
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
457

458
For example, if table `order` and table `item` are related via pivot table `order_item`,
Alexander Makarov committed
459 460 461 462 463
we can declare the `items` relation in the `Order` class like the following:

```php
class Order extends \yii\db\ActiveRecord
{
464 465 466
    public function getItems()
    {
        return $this->hasMany(Item::className(), ['id' => 'item_id'])
467
            ->viaTable('order_item', ['order_id' => 'id']);
468
    }
Alexander Makarov committed
469 470 471
}
```

472 473
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
474
instead of the pivot table name. For example, the above `items` relation can be equivalently declared as follows:
Alexander Makarov committed
475 476 477 478

```php
class Order extends \yii\db\ActiveRecord
{
479 480 481 482 483 484 485 486 487 488
    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
489 490 491
}
```

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

Alexander Makarov committed
494

Qiang Xue committed
495 496 497 498
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
499 500 501 502
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
503
// SQL executed: SELECT * FROM customer WHERE id=1
Alexander Makarov committed
504
$customer = Customer::find(1);
505
// SQL executed: SELECT * FROM order WHERE customer_id=1
Alexander Makarov committed
506 507 508 509 510
$orders = $customer->orders;
// no SQL executed
$orders2 = $customer->orders;
```

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

```php
514
// SQL executed: SELECT * FROM customer LIMIT 100
Alexander Makarov committed
515 516 517
$customers = Customer::find()->limit(100)->all();

foreach ($customers as $customer) {
518
    // SQL executed: SELECT * FROM order WHERE customer_id=...
519 520
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
521 522 523 524 525
}
```

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

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

```php
531 532
// SQL executed: SELECT * FROM customer LIMIT 100;
//               SELECT * FROM orders WHERE customer_id IN (1,2,...)
Alexander Makarov committed
533
$customers = Customer::find()->limit(100)
534
    ->with('orders')->all();
Alexander Makarov committed
535 536

foreach ($customers as $customer) {
537 538 539
    // no SQL executed
    $orders = $customer->orders;
    // ...handle $orders...
Alexander Makarov committed
540 541 542
}
```

Qiang Xue committed
543 544 545 546 547
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
548

Qiang Xue committed
549 550 551 552 553 554 555 556
> 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
557

Qiang Xue committed
558
Sometimes, you may want to customize the relational queries on the fly. This can be
Alexander Makarov committed
559 560 561 562
done for both lazy loading and eager loading. For example,

```php
$customer = Customer::find(1);
563
// lazy loading: SELECT * FROM order WHERE customer_id=1 AND subtotal>100
Alexander Makarov committed
564 565
$orders = $customer->getOrders()->where('subtotal>100')->all();

566 567
// eager loading: SELECT * FROM customer LIMIT 100
//                SELECT * FROM order WHERE customer_id IN (1,2,...) AND subtotal>100
Alexander Makarov committed
568
$customers = Customer::find()->limit(100)->with([
569 570 571
    'orders' => function($query) {
        $query->andWhere('subtotal>100');
    },
Alexander Makarov committed
572
])->all();
Alexander Makarov committed
573 574 575
```


576 577 578 579 580 581 582 583 584
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
{
585 586 587 588 589
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
590 591 592 593
}

class Order extends ActiveRecord
{
594 595 596 597 598
    ....
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
599 600 601 602 603 604 605 606
}
```

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
607
// SELECT * FROM customer WHERE id=1
608 609
$customer = Customer::find(1);
// echoes "not equal"
610 611
// SELECT * FROM order WHERE customer_id=1
// SELECT * FROM customer WHERE id=1
612
if ($customer->orders[0]->customer === $customer) {
613
    echo 'equal';
614
} else {
615
    echo 'not equal';
616 617 618 619
}
```

To avoid the redundant execution of the last SQL statement, we could declare the inverse relations for the `customer`
620
and the `orders` relations by calling the [[yii\db\ActiveQuery::inverseOf()|inverseOf()]] method, like the following:
621 622 623 624

```php
class Customer extends ActiveRecord
{
625 626 627 628 629
    ....
    public function getOrders()
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])->inverseOf('customer');
    }
630 631 632 633 634 635
}
```

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

```php
636
// SELECT * FROM customer WHERE id=1
637 638
$customer = Customer::find(1);
// echoes "equal"
639
// SELECT * FROM order WHERE customer_id=1
640
if ($customer->orders[0]->customer === $customer) {
641
    echo 'equal';
642
} else {
643
    echo 'not equal';
644 645 646 647 648 649 650
}
```

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

```php
651 652
// SELECT * FROM customer
// SELECT * FROM order WHERE customer_id IN (1, 2, ...)
653 654 655
$customers = Customer::find()->with('orders')->all();
// echoes "equal"
if ($customers[0]->orders[0]->customer === $customers[0]) {
656
    echo 'equal';
657
} else {
658
    echo 'not equal';
659 660 661 662
}
```

> Note: Inverse relation cannot be defined with a relation that involves pivoting tables.
663 664
> 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.
665 666


667 668 669 670
Joining with Relations
----------------------

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

```php
676
// find all orders and sort the orders by the customer id and the order id. also eager loading "customer"
677
$orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all();
678
// find all orders that contain books, and eager loading "books"
679
$orders = Order::find()->innerJoinWith('books')->all();
680 681
```

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

685 686
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
687 688 689 690

```php
// join with multiple relations
// find out the orders that contain books and are placed by customers who registered within the past 24 hours
691
$orders = Order::find()->innerJoinWith([
692 693
    'books',
    'customer' => function ($query) {
694
        $query->where('customer.created_at > ' . (time() - 24 * 3600));
695
    }
Qiang Xue committed
696 697 698 699 700
])->all();
// join with sub-relations: join with books and books' authors
$orders = Order::find()->joinWith('books.author')->all();
```

701 702 703 704
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
705
The difference between [[yii\db\ActiveQuery::joinWith()|joinWith()]] and [[yii\db\ActiveQuery::with()|with()]] is that
706 707 708 709 710 711 712 713
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
714
When using [[yii\db\ActiveQuery::joinWith()|joinWith()]], you are responsible to disambiguate column names.
715
In the above examples, we use `item.id` and `order.id` to disambiguate the `id` column references
716 717
because both of the order table and the item table contain a column named `id`.

Qiang Xue committed
718 719 720
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
721
And also by default, [[yii\db\ActiveQuery::joinWith()|joinWith()]] uses `LEFT JOIN` to join the related tables.
722
You may pass it with the `$joinType` parameter to customize the join type. As a shortcut to the `INNER JOIN` type,
Carsten Brandt committed
723
you may use [[yii\db\ActiveQuery::innerJoinWith()|innerJoinWith()]].
Qiang Xue committed
724 725 726 727 728

Below are some more examples,

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

734
Sometimes when joining two tables, you may need to specify some extra condition in the ON part of the JOIN query.
735
This can be done by calling the [[yii\db\ActiveQuery::onCondition()]] method like the following:
736 737 738 739

```php
class User extends ActiveRecord
{
740 741 742 743
    public function getBooks()
    {
        return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]);
    }
744 745 746
}
```

747 748
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
749 750
to specify that only items whose `category_id` is 1 should be returned.

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

```php
755 756
// 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
757
$users = User::find()->joinWith('books')->all();
758 759
```

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

```php
764
// SELECT * FROM user WHERE id=10
765
$user = User::find(10);
766
// SELECT * FROM item WHERE owner_id=10 AND category_id=1
767 768 769
$books = $user->books;
```

770

Alexander Makarov committed
771 772 773 774 775 776
Working with Relationships
--------------------------

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

777 778
- [[yii\db\ActiveRecord::link()|link()]]
- [[yii\db\ActiveRecord::unlink()|unlink()]]
Alexander Makarov committed
779 780 781 782 783 784

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

```php
$customer = Customer::find(1);
785
$order = new Order();
Alexander Makarov committed
786 787 788 789
$order->subtotal = 100;
$customer->link('orders', $order);
```

790 791
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
792 793


Qiang Xue committed
794 795
Scopes
------
Alexander Makarov committed
796

797 798 799 800
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.
801

Qiang Xue committed
802 803 804 805 806 807 808
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
809 810

```php
811 812
namespace app\models;

813
use yii\db\ActiveQuery;
814 815

class CommentQuery extends ActiveQuery
Alexander Makarov committed
816
{
817 818 819 820 821
    public function active($state = true)
    {
        $this->andWhere(['active' => $state]);
        return $this;
    }
822 823
}
```
Alexander Makarov committed
824

825 826 827 828
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.
829
3. Check [[yii\db\ActiveQuery]] methods that are very useful for modifying query conditions.
830

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

Carsten Brandt committed
834
```php
835 836 837 838 839 840
namespace app\models;

use yii\db\ActiveRecord;

class Comment extends ActiveRecord
{
841 842 843 844 845
    public static function createQuery($config = [])
    {
        $config['modelClass'] = get_called_class();
        return new CommentQuery($config);
    }
Alexander Makarov committed
846
}
847
```
Alexander Makarov committed
848

849 850 851
That's it. Now you can use your custom scope methods:

```php
852
$comments = Comment::find()->active()->all();
853
$inactiveComments = Comment::find()->active(false)->all();
854 855 856 857 858 859 860
```

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

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

865
    }
866
}
Alexander Makarov committed
867 868
```

869 870 871 872
Or use the scopes on-the-fly when performing relational query:

```php
$posts = Post::find()->with([
873 874 875
    'comments' => function($q) {
        $q->active();
    }
876 877
])->all();
```
Alexander Makarov committed
878

Qiang Xue committed
879

880 881
### Making it IDE-friendly

882 883
In order to make most modern IDE autocomplete happy you need to override return types for some methods of both model
and query like the following:
Alexander Makarov committed
884 885

```php
886 887 888 889 890
/**
 * @method \app\models\CommentQuery|static|null find($q = null) static
 * @method \app\models\CommentQuery findBySql($sql, $params = []) static
 */
class Comment extends ActiveRecord
Alexander Makarov committed
891
{
892
    // ...
Alexander Makarov committed
893 894 895
}
```

896
```php
897 898 899 900 901
/**
 * @method \app\models\Comment|array|null one($db = null)
 * @method \app\models\Comment[]|array all($db = null)
 */
class CommentQuery extends ActiveQuery
902
{
903
    // ...
904 905 906
}
```

907 908 909 910 911 912 913 914
### Default Scope

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

```php
public static function createQuery($config = [])
{
915 916
    $config['modelClass'] = get_called_class();
    return (new ActiveQuery($config))->where(['deleted' => false]);
917 918 919 920 921 922 923 924
}
```

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
925 926 927 928
Transactional operations
------------------------

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

930 931
TODO: FIXME: WIP, TBD, https://github.com/yiisoft/yii2/issues/226

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

938
Here these ways are (**DO NOT** use them unless you're sure what you are actually doing). Models:
939 940 941 942

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

945 946 947 948
    public function getProduct()
    {
        return $this->hasOne(Product::className(), ['product_id' => 'id']);
    }
949 950 951 952
}

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

955 956 957 958
    public function getFeatures()
    {
        return $this->hasMany(Feature::className(), ['id' => 'product_id']);
    }
959 960 961
}
```

962
Overriding [[yii\db\ActiveRecord::save()|save()]] method:
963 964 965 966 967

```php

class ProductController extends \yii\web\Controller
{
968 969 970 971
    public function actionCreate()
    {
        // FIXME: TODO: WIP, TBD
    }
972 973 974 975 976 977 978 979
}
```

Using transactions within controller layer:

```php
class ProductController extends \yii\web\Controller
{
980 981 982 983
    public function actionCreate()
    {
        // FIXME: TODO: WIP, TBD
    }
984 985 986 987 988 989 990 991
}
```

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

```php
class Feature extends \yii\db\ActiveRecord
{
992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007
    // ...

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

    public function scenarios()
    {
        return [
            'userCreates' => [
                'attributes' => ['name', 'value'],
                'atomic' => [self::OP_INSERT],
            ],
        ];
    }
1008 1009 1010 1011
}

class Product extends \yii\db\ActiveRecord
{
1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041
    // ...

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

    public function scenarios()
    {
        return [
            'userCreates' => [
                'attributes' => ['title', 'price'],
                'atomic' => [self::OP_INSERT],
            ],
        ];
    }

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

    public function afterSave($insert)
    {
        parent::afterSave($insert);
        if ($this->getScenario() === 'userCreates') {
            // FIXME: TODO: WIP, TBD
        }
    }
1042 1043 1044 1045 1046 1047 1048 1049
}
```

Controller is very thin and neat:

```php
class ProductController extends \yii\web\Controller
{
1050 1051 1052 1053
    public function actionCreate()
    {
        // FIXME: TODO: WIP, TBD
    }
1054 1055
}
```
Alexander Makarov committed
1056

Qiang Xue committed
1057 1058 1059 1060 1061 1062 1063 1064 1065 1066
Optimistic Locks
----------------

TODO

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

TODO

Alexander Makarov committed
1067 1068 1069 1070
See also
--------

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