Active Record provides an object-oriented interface for accessing and manipulating data stored in databases. 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 particular column in that row. Instead of writing raw SQL statements, you would access Active Record attributes and call Active Record methods to access and manipulate the data stored in database tables.
For example, assume Customer is an Active Record class which is associated with the customer table and name is a column of the customer table. You can write the following code to insert a new row into the customer table:
$customer = new Customer(); $customer->name = 'Qiang'; $customer->save();
The above code is equivalent to using the following raw SQL statement for MySQL, which is less intuitive, more error prone, and may even have compatibility problems if you are using a different kind of database:
$db->createCommand('INSERT INTO `customer` (`name`) VALUES (:name)', [
':name' => 'Qiang',
])->execute();
Yii provides the Active Record support for the following relational databases:
yii2-sphinx extensionyii2-elasticsearch extensionAdditionally, Yii also supports using Active Record with the following NoSQL databases:
yii2-redis extensionyii2-mongodb extensionIn 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.
To get started, declare an Active Record class by extending yii\db\ActiveRecord.
By default each Active Record class is associated with its database table. The tableName() method returns the table name by converting the class name via yii\helpers\Inflector::camel2id(). You may override this method if the table is not named after this convention.
Also a default tablePrefix can be applied. For example if tablePrefix is tbl_, Customer becomes tbl_customer and OrderItem becomes tbl_order_item.
If a table name is given as {{%TableName}}, then the percentage character % will be replaced with the table prefix. For example, {{%post}} becomes {{tbl_post}}. The brackets around the table name are used for quoting in an SQL query.
In the following example, we declare an Active Record class named Customer for the customer database table.
namespace app\models;
use yii\db\ActiveRecord;
class Customer extends ActiveRecord
{
const STATUS_INACTIVE = 0;
const STATUS_ACTIVE = 1;
/**
* @return string the name of the table associated with this ActiveRecord class.
*/
public static function tableName()
{
return '{{customer}}';
}
}
Active Record instances are considered as models. For this reason, we usually put Active Record classes under the app\models namespace (or other namespaces for keeping model classes).
Because yii\db\ActiveRecord extends from yii\base\Model, it inherits all model features, such as attributes, validation rules, data serialization, etc.
By default, Active Record uses the db application component as the DB connection to access and manipulate the database data. As explained in Database Access Objects, you can configure the db component in the application configuration like shown below,
return [
'components' => [
'db' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=testdb',
'username' => 'demo',
'password' => 'demo',
],
],
];
If you want to use a different database connection other than the db component, you should override the getDb() method:
class Customer extends ActiveRecord
{
// ...
public static function getDb()
{
// use the "db2" application component
return \Yii::$app->db2;
}
}
After declaring an Active Record class, you can use it to query data from the corresponding database table. The process usually takes the following three steps:
As you can see, this is very similar to the procedure with query builder. The only difference is that instead of using the new operator to create a query object, you call yii\db\ActiveRecord::find() to return a new query object which is of class yii\db\ActiveQuery.
Below are some examples showing how to use Active Query to query data:
// return a single customer whose ID is 123
// SELECT * FROM `customer` WHERE `id` = 123
$customer = Customer::find()
->where(['id' => 123])
->one();
// return all active customers and order them by their IDs
// SELECT * FROM `customer` WHERE `status` = 1 ORDER BY `id`
$customers = Customer::find()
->where(['status' => Customer::STATUS_ACTIVE])
->orderBy('id')
->all();
// return the number of active customers
// SELECT COUNT(*) FROM `customer` WHERE `status` = 1
$count = Customer::find()
->where(['status' => Customer::STATUS_ACTIVE])
->count();
// return all customers in an array indexed by customer IDs
// SELECT * FROM `customer`
$customers = Customer::find()
->indexBy('id')
->all();
In the above, $customer is a Customer object while $customers is an array of Customer objects. They are all populated with the data retrieved from the customer table.
Info: Because yii\db\ActiveQuery extends from yii\db\Query, you can use all query building methods and query methods as described in the Section Query Builder.
Because it is a common task to query by primary key values or a set of column values, Yii provides two shortcut methods for this purpose:
Both methods can take one of the following parameter formats:
The following code shows how these methods can be used:
// returns a single customer whose ID is 123
// SELECT * FROM `customer` WHERE `id` = 123
$customer = Customer::findOne(123);
// returns customers whose ID is 100, 101, 123 or 124
// SELECT * FROM `customer` WHERE `id` IN (100, 101, 123, 124)
$customers = Customer::findAll([100, 101, 123, 124]);
// returns an active customer whose ID is 123
// SELECT * FROM `customer` WHERE `id` = 123 AND `status` = 1
$customer = Customer::findOne([
'id' => 123,
'status' => Customer::STATUS_ACTIVE,
]);
// returns all inactive customers
// SELECT * FROM `customer` WHERE `status` = 0
$customers = Customer::findAll([
'status' => Customer::STATUS_INACTIVE,
]);
Note: Neither yii\db\ActiveRecord::findOne() nor yii\db\ActiveQuery::one() will add
LIMIT 1to the generated SQL statement. If your query may return many rows of data, you should calllimit(1)explicitly to improve the performance, e.g.,Customer::find()->limit(1)->one().
Besides using query building methods, you can also write raw SQLs to query data and populate the results into Active Record objects. You can do so by calling the yii\db\ActiveRecord::findBySql() method:
// returns all inactive customers $sql = 'SELECT * FROM customer WHERE status=:status'; $customers = Customer::findBySql($sql, [':status' => Customer::STATUS_INACTIVE])->all();
Do not call extra query building methods after calling findBySql() as they will be ignored.
As aforementioned, the data brought back from the database are populated into Active Record instances, and each row of the query result corresponds to a single Active Record instance. You can access the column values by accessing the attributes of the Active Record instances, for example,
// "id" and "email" are the names of columns in the "customer" table $customer = Customer::findOne(123); $id = $customer->id; $email = $customer->email;
Note: The Active Record attributes are named after the associated table columns in a case-sensitive manner. Yii automatically defines an attribute in Active Record for every column of the associated table. You should NOT redeclare any of the attributes.
Because Active Record attributes are named after table columns, you may find you are writing PHP code like $customer->first_name, which uses underscores to separate words in attribute names if your table columns are named in this way. If you are concerned about code style consistency, you should rename your table columns accordingly (to use camelCase, for example).
It often happens that the data being entered and/or displayed are in a format which is different from the one used in storing the data in a database. For example, in the database you are storing customers' birthdays as UNIX timestamps (which is not a good design, though), while in most cases you would like to manipulate birthdays as strings in the format of 'YYYY/MM/DD'. To achieve this goal, you can define data transformation methods in the Customer Active Record class like the following:
class Customer extends ActiveRecord
{
// ...
public function getBirthdayText()
{
return date('Y/m/d', $this->birthday);
}
public function setBirthdayText($value)
{
$this->birthday = strtotime($value);
}
}
Now in your PHP code, instead of accessing $customer->birthday, you would access $customer->birthdayText, which will allow you to input and display customer birthdays in the format of 'YYYY/MM/DD'.
Tip: The above example shows a generic way of transforming data in different formats. If you are working with date values, you may use DateValidator and DatePicker, which is easier to use and more powerful.
While retrieving data in terms of Active Record objects is convenient and flexible, it is not always desirable when you have to bring back a large amount of data due to the big memory footprint. In this case, you can retrieve data using PHP arrays by calling asArray() before executing a query method:
// return all customers
// each customer is returned as an associative array
$customers = Customer::find()
->asArray()
->all();
Note: While this method saves memory and improves performance, it is closer to the lower DB abstraction layer and you will lose most of the Active Record features. A very important distinction lies in the data type of the column values. When you return data in Active Record instances, column values will be automatically typecast according to the actual column types; on the other hand when you return data in arrays, column values will be strings (since they are the result of PDO without any processing), regardless their actual column types.
In Query Builder, we have explained that you may use batch query to minimize your memory usage when querying a large amount of data from the database. You may use the same technique in Active Record. For example,
// fetch 10 customers at a time
foreach (Customer::find()->batch(10) as $customers) {
// $customers is an array of 10 or fewer Customer objects
}
// fetch 10 customers at a time and iterate them one by one
foreach (Customer::find()->each(10) as $customer) {
// $customer is a Customer object
}
// batch query with eager loading
foreach (Customer::find()->with('orders')->each() as $customer) {
// $customer is a Customer object with the 'orders' relation populated
}
Using Active Record, you can easily save data to the database by taking the following steps:
For example,
// insert a new row of data $customer = new Customer(); $customer->name = 'James'; $customer->email = '[email protected]'; $customer->save(); // update an existing row of data $customer = Customer::findOne(123); $customer->email = '[email protected]'; $customer->save();
The save() method can either insert or update a row of data, depending on the state of the Active Record instance. If the instance is newly created via the new operator, calling save() will cause insertion of a new row; If the instance is the result of a query method, calling save() will update the row associated with the instance.
You can differentiate the two states of an Active Record instance by checking its isNewRecord property value. This property is also used by save() internally as follows:
public function save($runValidation = true, $attributeNames = null)
{
if ($this->getIsNewRecord()) {
return $this->insert($runValidation, $attributeNames);
} else {
return $this->update($runValidation, $attributeNames) !== false;
}
}
Tip: You can call insert() or update() directly to insert or update a row.
Because yii\db\ActiveRecord extends from yii\base\Model, it shares the same data validation feature. You can declare validation rules by overriding the rules() method and perform data validation by calling the validate() method.
When you call save(), by default it will call validate() automatically. Only when the validation passes, will it actually save the data; otherwise it will simply return false, and you can check the errors property to retrieve the validation error messages.
Tip: If you are certain that your data do not need validation (e.g., the data comes from trustable sources), you can call
save(false)to skip the validation.
Like normal models, Active Record instances also enjoy the massive assignment feature. Using this feature, you can assign values to multiple attributes of an Active Record instance in a single PHP statement, like shown below. Do remember that only safe attributes can be massively assigned, though.
$values = [
'name' => 'James',
'email' => '[email protected]',
];
$customer = new Customer();
$customer->attributes = $values;
$customer->save();
It is a common task to increment or decrement a column in a database table. We call these columns "counter columns". You can use updateCounters() to update one or multiple counter columns. For example,
$post = Post::findOne(100); // UPDATE `post` SET `view_count` = `view_count` + 1 WHERE `id` = 100 $post->updateCounters(['view_count' => 1]);
Note: If you use yii\db\ActiveRecord::save() to update a counter column, you may end up with inaccurate result, because it is likely the same counter is being saved by multiple requests which read and write the same counter value.
When you call save() to save an Active Record instance, only dirty attributes are being saved. An attribute is considered dirty if its value has been modified since it was loaded from DB or saved to DB most recently. Note that data validation will be performed regardless if the Active Record instance has dirty attributes or not.
Active Record automatically maintains the list of dirty attributes. It does so by maintaining an older version of the attribute values and comparing them with the latest one. You can call yii\db\ActiveRecord::getDirtyAttributes() to get the attributes that are currently dirty. You can also call yii\db\ActiveRecord::markAttributeDirty() to explicitly mark an attribute as dirty.
If you are interested in the attribute values prior to their most recent modification, you may call getOldAttributes() or getOldAttribute().
Note: The comparison of old and new values will be done using the
===operator so a value will be considered dirty even if it has the same value but a different type. This is often the case when the model receives user input from HTML forms where every value is represented as a string. To ensure the correct type for e.g. integer values you may apply a validation filter:['attributeName', 'filter', 'filter' => 'intval']. This works with all the typecasting functions of PHP like intval(), floatval(), boolval, etc...
Some of your table columns may have default values defined in the database. Sometimes, you may want to pre-populate your Web form for an Active Record instance with these default values. To avoid writing the same default values again, you can call loadDefaultValues() to populate the DB-defined default values into the corresponding Active Record attributes:
$customer = new Customer(); $customer->loadDefaultValues(); // $customer->xyz will be assigned the default value declared when defining the "xyz" column
Being populated by query results yii\db\ActiveRecord performs automatic typecast for its attribute values, using information from database table schema. This allows data retrieved from table column declared as integer to be populated in ActiveRecord instance with PHP integer, boolean with boolean and so on. However, typecasting mechanism has several limitations:
Note that attribute typecast is performed only during populating ActiveRecord instance from query result. There is no automatic conversion for the values loaded from HTTP request or set directly via property access. The table schema will also be used while preparing SQL statements for the ActiveRecord data saving, ensuring values are bound to the query with correct type. However, ActiveRecord instance attribute values will not be converted during saving process.
Tip: you may use yii\behaviors\AttributeTypecastBehavior to facilitate attribute values typecasting on ActiveRecord validation or saving.
The methods described above all work on individual Active Record instances, causing inserting or updating of individual table rows. To update multiple rows simultaneously, you should call updateAll(), instead, which is a static method.
// UPDATE `customer` SET `status` = 1 WHERE `email` LIKE `%@example.com%` Customer::updateAll(['status' => Customer::STATUS_ACTIVE], ['like', 'email', '@example.com']);
Similarly, you can call updateAllCounters() to update counter columns of multiple rows at the same time.
// UPDATE `customer` SET `age` = `age` + 1 Customer::updateAllCounters(['age' => 1]);
To delete a single row of data, first retrieve the Active Record instance corresponding to that row and then call the yii\db\ActiveRecord::delete() method.
$customer = Customer::findOne(123); $customer->delete();
You can call yii\db\ActiveRecord::deleteAll() to delete multiple or all rows of data. For example,
Customer::deleteAll(['status' => Customer::STATUS_INACTIVE]);
Note: Be very careful when calling deleteAll() because it may totally erase all data from your table if you make a mistake in specifying the condition.
It is important to understand the life cycles of Active Record when it is used for different purposes. During each life cycle, a certain sequence of methods will be invoked, and you can override these methods to get a chance to customize the life cycle. You can also respond to certain Active Record events triggered during a life cycle to inject your custom code. These events are especially useful when you are developing Active Record behaviors which need to customize Active Record life cycles.
In the following, we will summarize the various Active Record life cycles and the methods/events that are involved in the life cycles.
When creating a new Active Record instance via the new operator, the following life cycle will happen:
When querying data through one of the querying methods, each newly populated Active Record will undergo the following life cycle:
When calling save() to insert or update an Active Record instance, the following life cycle will happen:
an EVENT_BEFORE_VALIDATE event. If the method returns false or yii\base\ModelEvent::$isValid is false, the rest of the steps will be skipped.
an EVENT_AFTER_VALIDATE event.
an EVENT_BEFORE_INSERT or EVENT_BEFORE_UPDATE event. If the method returns false or yii\base\ModelEvent::$isValid is false, the rest of the steps will be skipped.
an EVENT_AFTER_INSERT or EVENT_AFTER_UPDATE event.
When calling delete() to delete an Active Record instance, the following life cycle will happen:
an EVENT_BEFORE_DELETE event. If the method returns false or yii\base\ModelEvent::$isValid is false, the rest of the steps will be skipped.
an EVENT_AFTER_DELETE event.
Note: Calling any of the following methods will NOT initiate any of the above life cycles because they work on the database directly and not on a record basis:
When calling refresh() to refresh an Active Record instance, the EVENT_AFTER_REFRESH event is triggered if refresh is successful and the method returns true.
There are two ways of using transactions while working with Active Record.
The first way is to explicitly enclose Active Record method calls in a transactional block, like shown below,
$customer = Customer::findOne(123);
Customer::getDb()->transaction(function($db) use ($customer) {
$customer->id = 200;
$customer->save();
// ...other DB operations...
});
// or alternatively
$transaction = Customer::getDb()->beginTransaction();
try {
$customer->id = 200;
$customer->save();
// ...other DB operations...
$transaction->commit();
} catch(\Exception $e) {
$transaction->rollBack();
throw $e;
} catch(\Throwable $e) {
$transaction->rollBack();
throw $e;
}
Note: in the above code we have two catch-blocks for compatibility with PHP 5.x and PHP 7.x.
\Exceptionimplements the\Throwableinterface since PHP 7.0, so you can skip the part with\Exceptionif your app uses only PHP 7.0 and higher.
The second way is to list the DB operations that require transactional support in the yii\db\ActiveRecord::transactions() method. For example,
class Customer extends ActiveRecord
{
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,
];
}
}
The yii\db\ActiveRecord::transactions() method should return an array whose keys are scenario names and values are the corresponding operations that should be enclosed within transactions. You should use the following constants to refer to different DB operations:
Use the | operators to concatenate the above constants to indicate multiple operations. You may also use the shortcut constant OP_ALL to refer to all three operations above.
Transactions that are created using this method will be started before calling beforeSave() and will be committed after afterSave() has run.
Optimistic locking is a way to prevent conflicts that may occur when a single row of data is being updated by multiple users. For example, both user A and user B are editing the same wiki article at the same time. After user A saves his edits, user B clicks on the "Save" button in an attempt to save his edits as well. Because user B was actually working on an outdated version of the article, it would be desirable to have a way to prevent him from saving the article and show him some hint message.
Optimistic locking solves the above problem by using a column to record the version number of each row. When a row is being saved with an outdated version number, a yii\db\StaleObjectException exception will be thrown, which prevents the row from being saved. Optimistic locking is only supported when you update or delete an existing row of data using yii\db\ActiveRecord::update() or yii\db\ActiveRecord::delete(), respectively.
To use optimistic locking,
BIGINT DEFAULT 0).For example, assume the version column is named as version. You can implement optimistic locking with the code like the following.
// ------ view code -------
use yii\helpers\Html;
// ...other input fields
echo Html::activeHiddenInput($model, 'version');
// ------ controller code -------
use yii\db\StaleObjectException;
public function actionUpdate($id)
{
$model = $this->findModel($id);
try {
if ($model->load(Yii::$app->request->post()) && $model->save()) {
return $this->redirect(['view', 'id' => $model->id]);
} else {
return $this->render('update', [
'model' => $model,
]);
}
} catch (StaleObjectException $e) {
// logic to resolve the conflict
}
}
Besides working with individual database tables, Active Record is also capable of bringing together related data, making them readily accessible through the primary data. For example, the customer data is related with the order data because one customer may have placed one or multiple orders. With appropriate declaration of this relation, you'll be able to access a customer's order information using the expression $customer->orders which gives back the customer's order information in terms of an array of Order Active Record instances.
To work with relational data using Active Record, you first need to declare relations in Active Record classes. The task is as simple as declaring a relation method for every interested relation, like the following,
class Customer extends ActiveRecord
{
// ...
public function getOrders()
{
return $this->hasMany(Order::className(), ['customer_id' => 'id']);
}
}
class Order extends ActiveRecord
{
// ...
public function getCustomer()
{
return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
}
}
In the above code, we have declared an orders relation for the Customer class, and a customer relation for the Order class.
Each relation method must be named as getXyz. We call xyz (the first letter is in lower case) the relation name. Note that relation names are case sensitive.
While declaring a relation, you should specify the following information:
Xyz::className() to get the class name string so that you can receive IDE auto-completion support as well as error detection at compiling stage. the link between the two types of data: specifies the column(s) through which the two types of data are related. The array values are the columns of the primary data (represented by the Active Record class that you are declaring relations), while the array keys are the columns of the related data.
An easy rule to remember this is, as you see in the example above, you write the column that belongs to the related Active Record directly next to it. You see there that customer_id is a property of Order and id is a property of Customer.
After declaring relations, you can access relational data through relation names. This is just like accessing an object property defined by the relation method. For this reason, we call it relation property. For example,
// SELECT * FROM `customer` WHERE `id` = 123 $customer = Customer::findOne(123); // SELECT * FROM `order` WHERE `customer_id` = 123 // $orders is an array of Order objects $orders = $customer->orders;
Info: When you declare a relation named
xyzvia a getter methodgetXyz(), you will be able to accessxyzlike an object property. Note that the name is case sensitive.
If a relation is declared with hasMany(), accessing this relation property will return an array of the related Active Record instances; if a relation is declared with hasOne(), accessing the relation property will return the related Active Record instance or null if no related data is found.
When you access a relation property for the first time, a SQL statement will be executed, like shown in the above example. If the same property is accessed again, the previous result will be returned without re-executing the SQL statement. To force re-executing the SQL statement, you should unset the relation property first: unset($customer->orders).
Note: While this concept looks similar to the object property feature, there is an important difference. For normal object properties the property value is of the same type as the defining getter method. A relation method however returns an yii\db\ActiveQuery instance, while accessing a relation property will either return a yii\db\ActiveRecord instance or an array of these.
$customer->orders; // is an array of `Order` objects $customer->getOrders(); // returns an ActiveQuery instanceThis is useful for creating customized queries, which is described in the next section.
Because a relation method returns an instance of yii\db\ActiveQuery, you can further build this query using query building methods before performing DB query. For example,
$customer = Customer::findOne(123);
// SELECT * FROM `order` WHERE `customer_id` = 123 AND `subtotal` > 200 ORDER BY `id`
$orders = $customer->getOrders()
->where(['>', 'subtotal', 200])
->orderBy('id')
->all();
Unlike accessing a relation property, each time you perform a dynamic relational query via a relation method, a SQL statement will be executed, even if the same dynamic relational query was performed before.
Sometimes you may even want to parametrize a relation declaration so that you can more easily perform dynamic relational query. For example, you may declare a bigOrders relation as follows,
class Customer extends ActiveRecord
{
public function getBigOrders($threshold = 100)
{
return $this->hasMany(Order::className(), ['customer_id' => 'id'])
->where('subtotal > :threshold', [':threshold' => $threshold])
->orderBy('id');
}
}
Then you will be able to perform the following relational queries:
// SELECT * FROM `order` WHERE `customer_id` = 123 AND `subtotal` > 200 ORDER BY `id` $orders = $customer->getBigOrders(200)->all(); // SELECT * FROM `order` WHERE `customer_id` = 123 AND `subtotal` > 100 ORDER BY `id` $orders = $customer->bigOrders;
In database modelling, when the multiplicity between two related tables is many-to-many, a junction table is usually introduced. For example, the order table and the item table may be related via a junction table named order_item. One order will then correspond to multiple order items, while one product item will also correspond to multiple order items.
When declaring such relations, you would call either via() or viaTable() to specify the junction table. The difference between via() and viaTable() is that the former specifies the junction table in terms of an existing relation name while the latter directly uses the junction table. For example,
class Order extends ActiveRecord
{
public function getItems()
{
return $this->hasMany(Item::className(), ['id' => 'item_id'])
->viaTable('order_item', ['order_id' => 'id']);
}
}
or alternatively,
class Order extends ActiveRecord
{
public function getOrderItems()
{
return $this->hasMany(OrderItem::className(), ['order_id' => 'id']);
}
public function getItems()
{
return $this->hasMany(Item::className(), ['id' => 'item_id'])
->via('orderItems');
}
}
The usage of relations declared with a junction table is the same as that of normal relations. For example,
// SELECT * FROM `order` WHERE `id` = 100 $order = Order::findOne(100); // SELECT * FROM `order_item` WHERE `order_id` = 100 // SELECT * FROM `item` WHERE `item_id` IN (...) // returns an array of Item objects $items = $order->items;
In Accessing Relational Data, we explained that you can access a relation property of an Active Record instance like accessing a normal object property. A SQL statement will be executed only when you access the relation property the first time. We call such relational data accessing method lazy loading. For example,
// SELECT * FROM `customer` WHERE `id` = 123 $customer = Customer::findOne(123); // SELECT * FROM `order` WHERE `customer_id` = 123 $orders = $customer->orders; // no SQL executed $orders2 = $customer->orders;
Lazy loading is very convenient to use. However, it may suffer from a performance issue when you need to access the same relation property of multiple Active Record instances. Consider the following code example. How many SQL statements will be executed?
// SELECT * FROM `customer` LIMIT 100
$customers = Customer::find()->limit(100)->all();
foreach ($customers as $customer) {
// SELECT * FROM `order` WHERE `customer_id` = ...
$orders = $customer->orders;
}
As you can see from the code comment above, there are 101 SQL statements being executed! This is because each time you access the orders relation property of a different Customer object in the for-loop, a SQL statement will be executed.
To solve this performance problem, you can use the so-called eager loading approach as shown below,
// SELECT * FROM `customer` LIMIT 100;
// SELECT * FROM `orders` WHERE `customer_id` IN (...)
$customers = Customer::find()
->with('orders')
->limit(100)
->all();
foreach ($customers as $customer) {
// no SQL executed
$orders = $customer->orders;
}
By calling yii\db\ActiveQuery::with(), you instruct Active Record to bring back the orders for the first 100 customers in one single SQL statement. As a result, you reduce the number of the executed SQL statements from 101 to 2!
You can eagerly load one or multiple relations. You can even eagerly load nested relations. A nested relation is a relation that is declared within a related Active Record class. For example, Customer is related with Order through the orders relation, and Order is related with Item through the items relation. When querying for Customer, you can eagerly load items using the nested relation notation orders.items.
The following code shows different usage of with(). We assume the Customer class has two relations orders and country, while the Order class has one relation items.
// eager loading both "orders" and "country"
$customers = Customer::find()->with('orders', 'country')->all();
// equivalent to the array syntax below
$customers = Customer::find()->with(['orders', 'country'])->all();
// no SQL executed
$orders= $customers[0]->orders;
// no SQL executed
$country = $customers[0]->country;
// eager loading "orders" and the nested relation "orders.items"
$customers = Customer::find()->with('orders.items')->all();
// access the items of the first order of the first customer
// no SQL executed
$items = $customers[0]->orders[0]->items;
You can eagerly load deeply nested relations, such as a.b.c.d. All parent relations will be eagerly loaded. That is, when you call with() using a.b.c.d, you will eagerly load a, a.b, a.b.c and a.b.c.d.
Info: In general, when eagerly loading
Nrelations among whichMrelations are defined with a junction table, a total number ofN+M+1SQL statements will be executed. Note that a nested relationa.b.c.dcounts as 4 relations.
When eagerly loading a relation, you can customize the corresponding relational query using an anonymous function. For example,
// find customers and bring back together their country and active orders
// SELECT * FROM `customer`
// SELECT * FROM `country` WHERE `id` IN (...)
// SELECT * FROM `order` WHERE `customer_id` IN (...) AND `status` = 1
$customers = Customer::find()->with([
'country',
'orders' => function ($query) {
$query->andWhere(['status' => Order::STATUS_ACTIVE]);
},
])->all();
When customizing the relational query for a relation, you should specify the relation name as an array key and use an anonymous function as the corresponding array value. The anonymous function will receive a $query parameter which represents the yii\db\ActiveQuery object used to perform the relational query for the relation. In the code example above, we are modifying the relational query by appending an additional condition about order status.
Note: If you call select() while eagerly loading relations, you have to make sure the columns referenced in the relation declarations are being selected. Otherwise, the related models may not be loaded properly. For example,
$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();
Note: The content described in this subsection is only applicable to relational databases, such as MySQL, PostgreSQL, etc.
The relational queries that we have described so far only reference the primary table columns when querying for the primary data. In reality we often need to reference columns in the related tables. For example, we may want to bring back the customers who have at least one active order. To solve this problem, we can build a join query like the following:
// SELECT `customer`.* FROM `customer`
// LEFT JOIN `order` ON `order`.`customer_id` = `customer`.`id`
// WHERE `order`.`status` = 1
//
// SELECT * FROM `order` WHERE `customer_id` IN (...)
$customers = Customer::find()
->select('customer.*')
->leftJoin('order', '`order`.`customer_id` = `customer`.`id`')
->where(['order.status' => Order::STATUS_ACTIVE])
->with('orders')
->all();
Note: It is important to disambiguate column names when building relational queries involving JOIN SQL statements. A common practice is to prefix column names with their corresponding table names.
However, a better approach is to exploit the existing relation declarations by calling yii\db\ActiveQuery::joinWith():
$customers = Customer::find()
->joinWith('orders')
->where(['order.status' => Order::STATUS_ACTIVE])
->all();
Both approaches execute the same set of SQL statements. The latter approach is much cleaner and drier, though.
By default, joinWith() will use LEFT JOIN to join the primary table with the related table. You can specify a different join type (e.g. RIGHT JOIN) via its third parameter $joinType. If the join type you want is INNER JOIN, you can simply call innerJoinWith(), instead.
Calling joinWith() will eagerly load the related data by default. If you do not want to bring in the related data, you can specify its second parameter $eagerLoading as false.
Like with(), you can join with one or multiple relations; you may customize the relation queries on-the-fly; you may join with nested relations; and you may mix the use of with() and joinWith(). For example,
$customers = Customer::find()->joinWith([
'orders' => function ($query) {
$query->andWhere(['>', 'subtotal', 100]);
},
])->with('country')
->all();
Sometimes when joining two tables, you may need to specify some extra conditions in the ON part of the JOIN query. This can be done by calling the yii\db\ActiveQuery::onCondition() method like the following:
// SELECT `customer`.* FROM `customer`
// LEFT JOIN `order` ON `order`.`customer_id` = `customer`.`id` AND `order`.`status` = 1
//
// SELECT * FROM `order` WHERE `customer_id` IN (...)
$customers = Customer::find()->joinWith([
'orders' => function ($query) {
$query->onCondition(['order.status' => Order::STATUS_ACTIVE]);
},
])->all();
This above query brings back all customers, and for each customer it brings back all active orders. Note that this differs from our earlier example which only brings back customers who have at least one active order.
Info: When yii\db\ActiveQuery is specified with a condition via onCondition(), the condition will be put in the
ONpart if the query involves a JOIN query. If the query does not involve JOIN, the on-condition will be automatically appended to theWHEREpart of the query. Thus it may only contain conditions including columns of the related table.
As noted before, when using JOIN in a query, we need to disambiguate column names. Therefor often an alias is defined for a table. Setting an alias for the relational query would be possible by customizing the relation query in the following way:
$query->joinWith([
'orders' => function ($q) {
$q->from(['o' => Order::tableName()]);
},
])
This however looks very complicated and involves either hardcoding the related objects table name or calling Order::tableName(). Since version 2.0.7, Yii provides a shortcut for this. You may now define and use the alias for the relation table like the following:
// join the orders relation and sort the result by orders.id
$query->joinWith(['orders o'])->orderBy('o.id');
The above syntax works for simple relations. If you need an alias for an intermediate table when joining over nested relations, e.g. $query->joinWith(['orders.product']), you need to nest the joinWith calls like in the following example:
$query->joinWith(['orders o' => function($q) {
$q->joinWith('product p');
}])
->where('o.amount > 100');
Relation declarations are often reciprocal between two Active Record classes. For example, Customer is related to Order via the orders relation, and Order is related back to Customer via the customer relation.
class Customer extends ActiveRecord
{
public function getOrders()
{
return $this->hasMany(Order::className(), ['customer_id' => 'id']);
}
}
class Order extends ActiveRecord
{
public function getCustomer()
{
return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
}
}
Now consider the following piece of code:
// SELECT * FROM `customer` WHERE `id` = 123 $customer = Customer::findOne(123); // SELECT * FROM `order` WHERE `customer_id` = 123 $order = $customer->orders[0]; // SELECT * FROM `customer` WHERE `id` = 123 $customer2 = $order->customer; // displays "not the same" echo $customer2 === $customer ? 'same' : 'not the same';
We would think $customer and $customer2 are the same, but they are not! Actually they do contain the same customer data, but they are different objects. When accessing $order->customer, an extra SQL statement is executed to populate a new object $customer2.
To avoid the redundant execution of the last SQL statement in the above example, we should tell Yii that customer is an inverse relation of orders by calling the inverseOf() method like shown below:
class Customer extends ActiveRecord
{
public function getOrders()
{
return $this->hasMany(Order::className(), ['customer_id' => 'id'])->inverseOf('customer');
}
}
With this modified relation declaration, we will have:
// SELECT * FROM `customer` WHERE `id` = 123 $customer = Customer::findOne(123); // SELECT * FROM `order` WHERE `customer_id` = 123 $order = $customer->orders[0]; // No SQL will be executed $customer2 = $order->customer; // displays "same" echo $customer2 === $customer ? 'same' : 'not the same';
Note: Inverse relations cannot be defined for relations involving a junction table. That is, if a relation is defined with via() or viaTable(), you should not call inverseOf() further.
When working with relational data, you often need to establish relationships between different data or destroy existing relationships. This requires setting proper values for the columns that define the relations. Using Active Record, you may end up writing the code like the following:
$customer = Customer::findOne(123); $order = new Order(); $order->subtotal = 100; // ... // setting the attribute that defines the "customer" relation in Order $order->customer_id = $customer->id; $order->save();
Active Record provides the link() method that allows you to accomplish this task more nicely:
$customer = Customer::findOne(123);
$order = new Order();
$order->subtotal = 100;
// ...
$order->link('customer', $customer);
The link() method requires you to specify the relation name and the target Active Record instance that the relationship should be established with. The method will modify the values of the attributes that link two Active Record instances and save them to the database. In the above example, it will set the customer_id attribute of the Order instance to be the value of the id attribute of the Customer instance and then save it to the database.
Note: You cannot link two newly created Active Record instances.
The benefit of using link() is even more obvious when a relation is defined via a junction table. For example, you may use the following code to link an Order instance with an Item instance:
$order->link('items', $item);
The above code will automatically insert a row in the order_item junction table to relate the order with the item.
Info: The link() method will NOT perform any data validation while saving the affected Active Record instance. It is your responsibility to validate any input data before calling this method.
The opposite operation to link() is unlink() which breaks an existing relationship between two Active Record instances. For example,
$customer = Customer::find()->with('orders')->where(['id' => 123])->one();
$customer->unlink('orders', $customer->orders[0]);
By default, the unlink() method will set the foreign key value(s) that specify the existing relationship to be null. You may, however, choose to delete the table row that contains the foreign key value by passing the $delete parameter as true to the method.
When a junction table is involved in a relation, calling unlink() will cause the foreign keys in the junction table to be cleared, or the deletion of the corresponding row in the junction table if $delete is true.
Active Record allows you to declare relations between Active Record classes that are powered by different databases. The databases can be of different types (e.g. MySQL and PostgreSQL, or MS SQL and MongoDB), and they can run on different servers. You can use the same syntax to perform relational queries. For example,
// Customer is associated with the "customer" table in a relational database (e.g. MySQL)
class Customer extends \yii\db\ActiveRecord
{
public static function tableName()
{
return 'customer';
}
public function getComments()
{
// a customer has many comments
return $this->hasMany(Comment::className(), ['customer_id' => 'id']);
}
}
// Comment is associated with the "comment" collection in a MongoDB database
class Comment extends \yii\mongodb\ActiveRecord
{
public static function collectionName()
{
return 'comment';
}
public function getCustomer()
{
// a comment has one customer
return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
}
}
$customers = Customer::find()->with('comments')->all();
You can use most of the relational query features that have been described in this section.
Note: Usage of joinWith() is limited to databases that allow cross-database JOIN queries. For this reason, you cannot use this method in the above example because MongoDB does not support JOIN.
By default, all Active Record queries are supported by yii\db\ActiveQuery. To use a customized query class in an Active Record class, you should override the yii\db\ActiveRecord::find() method and return an instance of your customized query class. For example,
// file Comment.php
namespace app\models;
use yii\db\ActiveRecord;
class Comment extends ActiveRecord
{
public static function find()
{
return new CommentQuery(get_called_class());
}
}
Now whenever you are performing a query (e.g. find(), findOne()) or defining a relation (e.g. hasOne()) with Comment, you will be calling an instance of CommentQuery instead of ActiveQuery.
You now have to define the CommentQuery class, which can be customized in many creative ways to improve your query building experience. For example,
// file CommentQuery.php
namespace app\models;
use yii\db\ActiveQuery;
class CommentQuery extends ActiveQuery
{
// conditions appended by default (can be skipped)
public function init()
{
$this->andOnCondition(['deleted' => false]);
parent::init();
}
// ... add customized query methods here ...
public function active($state = true)
{
return $this->andOnCondition(['active' => $state]);
}
}
Note: Instead of calling onCondition(), you usually should call andOnCondition() or orOnCondition() to append additional conditions when defining new query building methods so that any existing conditions are not overwritten.
This allows you to write query building code like the following:
$comments = Comment::find()->active()->all(); $inactiveComments = Comment::find()->active(false)->all();
Tip: In big projects, it is recommended that you use customized query classes to hold most query-related code so that the Active Record classes can be kept clean.
You can also use the new query building methods when defining relations about Comment or performing relational query:
class Customer extends \yii\db\ActiveRecord
{
public function getActiveComments()
{
return $this->hasMany(Comment::className(), ['customer_id' => 'id'])->active();
}
}
$customers = Customer::find()->joinWith('activeComments')->all();
// or alternatively
class Customer extends \yii\db\ActiveRecord
{
public function getComments()
{
return $this->hasMany(Comment::className(), ['customer_id' => 'id']);
}
}
$customers = Customer::find()->joinWith([
'comments' => function($q) {
$q->active();
}
])->all();
Info: In Yii 1.1, there is a concept called scope. Scope is no longer directly supported in Yii 2.0, and you should use customized query classes and query methods to achieve the same goal.
When Active Record instance is populated from query results, its attributes are filled up by corresponding column values from received data set.
You are able to fetch additional columns or values from query and store it inside the Active Record. For example, assume we have a table named room, which contains information about rooms available in the hotel. Each room stores information about its geometrical size using fields length, width, height. Imagine we need to retrieve list of all available rooms with their volume in descendant order. So you can not calculate volume using PHP, because we need to sort the records by its value, but you also want volume to be displayed in the list. To achieve the goal, you need to declare an extra field in your Room Active Record class, which will store volume value:
class Room extends \yii\db\ActiveRecord
{
public $volume;
// ...
}
Then you need to compose a query, which calculates volume of the room and performs the sort:
$rooms = Room::find()
->select([
'{{room}}.*', // select all columns
'([[length]] * [[width]] * [[height]]) AS volume', // calculate a volume
])
->orderBy('volume DESC') // apply sort
->all();
foreach ($rooms as $room) {
echo $room->volume; // contains value calculated by SQL
}
Ability to select extra fields can be exceptionally useful for aggregation queries. Assume you need to display a list of customers with the count of orders they have made. First of all, you need to declare a Customer class with orders relation and extra field for count storage:
class Customer extends \yii\db\ActiveRecord
{
public $ordersCount;
// ...
public function getOrders()
{
return $this->hasMany(Order::className(), ['customer_id' => 'id']);
}
}
Then you can compose a query, which joins the orders and calculates their count:
$customers = Customer::find()
->select([
'{{customer}}.*', // select all customer fields
'COUNT({{order}}.id) AS ordersCount' // calculate orders count
])
->joinWith('orders') // ensure table junction
->groupBy('{{customer}}.id') // group the result to ensure aggregation function works
->all();
A disadvantage of using this method would be that, if the information isn't loaded on the SQL query - it has to be calculated separately. Thus, if you have found particular record via regular query without extra select statements, it will be unable to return actual value for the extra field. Same will happen for the newly saved record.
$room = new Room(); $room->length = 100; $room->width = 50; $room->height = 2; $room->volume; // this value will be `null`, since it was not declared yet
Using the __get() and __set() magic methods we can emulate the behavior of a property:
class Room extends \yii\db\ActiveRecord
{
private $_volume;
public function setVolume($volume)
{
$this->_volume = (float) $volume;
}
public function getVolume()
{
if (empty($this->length) || empty($this->width) || empty($this->height)) {
return null;
}
if ($this->_volume === null) {
$this->setVolume(
$this->length * $this->width * $this->height
);
}
return $this->_volume;
}
// ...
}
When the select query doesn't provide the volume, the model will be able to calculate it automatically using the attributes of the model.
You can calculate the aggregation fields as well using defined relations:
class Customer extends \yii\db\ActiveRecord
{
private $_ordersCount;
public function setOrdersCount($count)
{
$this->_ordersCount = (int) $count;
}
public function getOrdersCount()
{
if ($this->isNewRecord) {
return null; // this avoid calling a query searching for null primary keys
}
if ($this->_ordersCount === null) {
$this->setOrdersCount($this->getOrders()->count()); // calculate aggregation on demand from relation
}
return $this->_ordersCount;
}
// ...
public function getOrders()
{
return $this->hasMany(Order::className(), ['customer_id' => 'id']);
}
}
With this code, in case 'ordersCount' is present in 'select' statement - Customer::ordersCount will be populated by query results, otherwise it will be calculated on demand using Customer::orders relation.
This approach can be as well used for creation of the shortcuts for some relational data, especially for the aggregation. For example:
class Customer extends \yii\db\ActiveRecord
{
/**
* Defines read-only virtual property for aggregation data.
*/
public function getOrdersCount()
{
if ($this->isNewRecord) {
return null; // this avoid calling a query searching for null primary keys
}
return empty($this->ordersAggregation) ? 0 : $this->ordersAggregation[0]['counted'];
}
/**
* Declares normal 'orders' relation.
*/
public function getOrders()
{
return $this->hasMany(Order::className(), ['customer_id' => 'id']);
}
/**
* Declares new relation based on 'orders', which provides aggregation.
*/
public function getOrdersAggregation()
{
return $this->getOrders()
->select(['customer_id', 'counted' => 'count(*)'])
->groupBy('customer_id')
->asArray(true);
}
// ...
}
foreach (Customer::find()->with('ordersAggregation')->all() as $customer) {
echo $customer->ordersCount; // outputs aggregation data from relation without extra query due to eager loading
}
$customer = Customer::findOne($pk);
$customer->ordersCount; // output aggregation data from lazy loaded relation
© 2008–2017 by Yii Software LLC
Licensed under the three clause BSD license.
http://www.yiiframework.com/doc-2.0/guide-db-active-record.html