Active Record
Active Record It provides an object-oriented interface to access and operate the data in the database ORM (object relational mapping) And object association and aggregation framework, hereinafter referred to as ar. The AR class is associated with a database table. The AR instance corresponds to a row of the table. The attributes of the AR instance represent the values of specific columns in the row. You can access the AR attribute and call the AR method to access and manipulate the data stored in the database table without writing the original SQL statement.
For example, suppose the Customer AR class is associated with the customer table, and the name attribute of the class represents the name column of the customer table. You can write the following code to insert a new record in the customer table:
$customer = new Customer(); $customer->name = 'Qiang'; $customer->save();
For MySQL, the above code is equivalent to using the following native SQL statements, but obviously the former is more intuitive and less error prone, and it is less likely to cause compatibility problems in the face of different DBMS (Database Management System).
// INSERT INTO `customer` (`name`) VALUES ('Qiang')
$db->createCommand('INSERT INTO `customer` (`name`) VALUES (:name)', [
':name' => 'Qiang',
])->execute();Yii provides AR support for the following "relational databases":
- MySQL 4.1 and above: supported by yii\db\ActiveRecord
- PostgreSQL 7.3 and above: supported by yii\db\ActiveRecord
- SQLite 2 and 3: supported by yii\db\ActiveRecord
- Microsoft SQL Server 2008 and above: supported by yii\db\ActiveRecord
- Oracle: supported by yii\db\ActiveRecord
- CUBRID 9.3 and above: supported by yii\db\ActiveRecord (prompt, due to the extension of CUBRID PDO) bug , adding references to variables will not work, so you have to use CUBRID 9.3 client and server.
- Sphinx: supported by yii\sphinx\ActiveRecord, it relies on the yii2 Sphinx extension
- ElasticSearch: supported by yii\elasticsearch\ActiveRecord, it relies on the yii2 ElasticSearch extension
In addition, Yii's AR function also supports the following NoSQL databases:
- Redis 2.6.12 and above: supported by yii\redis\ActiveRecord and relying on yii2 redis extension
- MongoDB 1.3.0 and above: supported by yii\mongodb\ActiveRecord and relying on yii2 mongodb extension
In this article, we will mainly describe the use of AR for relational databases. However, most of the content is also applicable in AR of NoSQL.
Declare AR class
To declare an AR class, you need to declare the class and inherit yii\db\ActiveRecord or its subclasses.
set a table name
By default, each AR class is associated with its own database table. After [[Yii \ helpers \ reflector:: camel2id()]] processing, the table name returned by the [[yii\db\ActiveRecord::tableName()]] method is obtained by class name conversion by default. If the default name is incorrect, you have to override this method.
In addition, [[yii\db\Connection::$tablePrefix]] table prefix will also work. For example, if the [[yii\db\Connection::$tablePrefix]] table prefix is tbl_, Customer's class name will be converted to tbl_customer table name, and OrderItem is converted to tbl_order_item.
If the table name you define is {{% TableName}}, the percentage character% will be replaced with the table prefix. For example, {{% post}} becomes {{tbl_post}}. The parentheses around the table name are SQL query reference handle.
In the following example, we define an AR class called 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 Active Record The name of the database table associated with the class
*/
public static function tableName()
{
return '{{customer}}';
}
}AR is called model
AR instances are called Model . Therefore, we usually put the AR class under the app\models namespace (or other namespace that holds the model).
Because yii\db\ActiveRecord inherits the model yii\base\Model, it has all Model Features, such as attributes, validation rules, data serialization, etc.
Connect database
Activity record AR class, db by default assembly Access and manipulate database data as a connector yii\db\Connection. be based on Database access As explained in, you can configure db components in system configuration in this way.
return [
'components' => [
'db' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=testdb',
'username' => 'demo',
'password' => 'demo',
],
],
];If you want to connect to different databases, not just db components, you can override the [[yii\db\ActiveRecord::getDb()]] method.
class Customer extends ActiveRecord
{
// ...
public static function getDb()
{
// Using the "db2" component
return \Yii::$app->db2;
}
}Query data
After defining the AR class, you can query the data from the corresponding database table. The query process consists of three steps:
- Create a new query generator object through the [[yii\db\ActiveRecord::find()]] method;
- use Construction method of query generator To build your query;
- call Query method of query builder To fetch the data into the AR instance.
As you can see, is it with Query Builder The steps are similar. The only difference is that you use [[yii\db\ActiveRecord::find()]] to get a new query builder object, which is yii\db\ActiveQuery, instead of using the new operator to create a query builder object.
Here are some examples of how to query data using Active Query:
// Return customer with ID 123:
// SELECT * FROM `customer` WHERE `id` = 123
$customer = Customer::find()
->where(['id' => 123])
->one();
// Retrieve all active customers and sort by their ID:
// SELECT * FROM `customer` WHERE `status` = 1 ORDER BY `id`
$customers = Customer::find()
->where(['status' => Customer::STATUS_ACTIVE])
->orderBy('id')
->all();
// Number of active customers retrieved:
// SELECT COUNT(*) FROM `customer` WHERE `status` = 1
$count = Customer::find()
->where(['status' => Customer::STATUS_ACTIVE])
->count();
// Index result set by customer ID:
// SELECT * FROM `customer`
$customers = Customer::find()
->indexBy('id')
->all();In the above code, $customer is a customer object and $customers is an array with customer objects as elements. Both are populated with the result set of data retrieved from the customer table.
Tip: because yii\db\ActiveQuery inherits yii\db\Query, you can use Query Builder All query methods described in the chapter.
Obtaining data rows based on the primary key is a common operation, so Yii provides two shortcut methods:
- [[yii\db\ActiveRecord::findOne()]]: returns an AR instance filled in the first row of the query result.
- [[yii\db\ActiveRecord::findAll()]]: returns the data of an AR instance and fills all the data in the query result.
The parameter transfer formats of these two methods are as follows:
- Scalar value: this value will be queried as a primary key. Yii will identify the primary key column by reading the database schema information.
- Array of scalar values: the values in this array are regarded as the values of the primary key to be queried.
- Associative array: the key value is the column name of the table, and the element value is the corresponding condition value to query. Can arrive Hash format See more information.
The following code describes how to use these methods:
// Return the customer with id 123
// SELECT * FROM `customer` WHERE `id` = 123
$customer = Customer::findOne(123);
// Return customers whose id is 100, 101, 123, 124
// SELECT * FROM `customer` WHERE `id` IN (100, 101, 123, 124)
$customers = Customer::findAll([100, 101, 123, 124]);
// Return active customers with id 123
// SELECT * FROM `customer` WHERE `id` = 123 AND `status` = 1
$customer = Customer::findOne([
'id' => 123,
'status' => Customer::STATUS_ACTIVE,
]);
// Return all inactive customers
// SELECT * FROM `customer` WHERE `status` = 0
$customers = Customer::findAll([
'status' => Customer::STATUS_INACTIVE,
]);Warning: if you need to pass user input to these methods, please ensure that the input value is scalar or array condition, and ensure that the array structure cannot be changed by the outside:
// yii\web\Controller ensures that $id is scalar
public function actionView($id)
{
$model = Post::findOne($id);
// ...
}//Specifies the column to be searched. Passing a scalar or array here will always just find a single record
$model = Post::findOne(['id' => Yii::$app->request->get('id')]);
//Do not use the following code! You can inject an array condition to match the value of any column!
$model = Post::findOne(Yii::$app->request->get('id'));
> Tip: [[yii\db\ActiveRecord::findOne()]] and [[yii\db\ActiveQuery::one()]] Will not be added `LIMIT 1` To generated SQL Statement. You should add a lot of data if you query `limit(1)` To improve performance, such as `Customer::find()->limit(1)->one()`. In addition to using the query builder method, you can also write native SQL Statement to query the data and populate the result set to Active Record Object. through the use of [[yii\db\ActiveRecord::findBySql()]] method: ```php // Return all inactive customers $sql = 'SELECT * FROM customer WHERE status=:status'; $customers = Customer::findBySql($sql, [':status' => Customer::STATUS_INACTIVE])->all();
Don't add other query methods after the [[yii\db\ActiveRecord::findBySql()|findBySql()]] method. The redundant query methods will be ignored.
Access data
As described above, the data returned from the database is filled into the AR instance, and each row of the query result corresponds to a single ar instance. You can access column values through the properties of AR instances, for example,
// "id" and "email" are the column names in the "customer" table $customer = Customer::findOne(123); $id = $customer->id; $email = $customer->email;
Tip: the properties of Active Record name the associated table columns in a case sensitive manner. Yii will automatically define an attribute in Active Record for each column of the associated table. You should not redeclare any properties.
Since the attribute of Active Record is named after the column name of the table, you may find that you are writing PHP code like: $customer - > first_ Name, if the column names of your table are separated by underscores, the words in the attribute name are named in this way. If you are worried about the consistency of code style, you should rename the corresponding table column name (for example, camel spelling).
Data Transformation
It is often encountered that the data to be entered or displayed is in one format, while the data to be stored in the database is in another format. For example, in a database, you store a Customer's birthday as a UNIX timestamp (although this is not a good design), and in most cases, you want to process birthday data in the format of the string 'YYYY/MM/DD'. To achieve this goal, you can define the data conversion method in Customer, and define the Active Record class as follows:
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, you can access $customer - > birthdaytext to enter and display the customer's birthday in the format of 'YYYY/MM/DD', instead of accessing $customer - > birthday.
Tip: the above example shows a common way to convert data in different formats. If you are using date values, you can use DateValidator And [[yii\jui\DatePicker|DatePicker]], which will be easier to use and more powerful.
Retrieving Data in Arrays
It is very convenient and flexible to obtain data through the Active Record object. At the same time, when you need to return a large amount of data, this is not satisfactory, because it will lead to a large amount of memory occupation. In this case, you can call the [[yii\db\ActiveQuery::asArray()|asArray()] before the query method to get the result of the PHP array form:
// Return all customers
// Each customer returns an associative array
$customers = Customer::find()
->asArray()
->all();Tip: Although this method can save memory and improve performance, it is closer to the lower DB abstraction layer, and you will lose most of the functions provided by Active Record. A very important difference is the data type of the column value. When you return data in the Active Record instance, the column value will be automatically type converted according to the actual column type; However, when you return data as an array, the column values will be strings (because they are the result of an unprocessed PDO), regardless of their actual column type.
Retrieving Data in Batches
stay Query Builder In, we have explained that you can use batch queries to minimize your memory usage whenever you query a large amount of data from the database. You can use the same technique in Active Record. For example,
// Get 10 pieces of customer data each time
foreach (Customer::find()->batch(10) as $customers) {
// $customers is an array of up to 10 pieces of data
}
// Get 10 pieces of customer data at a time, and then iterate them one by one
foreach (Customer::find()->each(10) as $customer) {
// $Customer is a 'Customer' object
}
// Batch query in greedy loading mode
foreach (Customer::find()->with('orders')->each() as $customer) {
// $Customer is a 'Customer' object with associated ` 'orders'`
}Saving Data
With Active Record, you can easily save data to a database by following these steps:
- Prepare an Active Record instance
- Assign the new value to the property of Active Record
- Call [[yii\db\ActiveRecord::save()]] to save the data to the database.
For example,
// Insert new record $customer = new Customer(); $customer->name = 'James'; $customer->email = 'james@example.com'; $customer->save(); // Update existing records $customer = Customer::findOne(123); $customer->email = 'james@newexample.com'; $customer->save();
The [[yii\db\ActiveRecord::save()|save()]] method may insert or update the records of the table, depending on the status of the Active Record instance. If the instance is instantiated through the new operator, calling the [[yii\db\ActiveRecord::save()|save()]] method will insert a new record; If the instance is the result of a query method, calling the [[yii\db\ActiveRecord::save()|save()]] method will update the record row corresponding to the instance.
You can distinguish these two states by checking the [[yii\db\ActiveRecord::isNewRecord|isNewRecord]] property value of the Active Record instance. This attribute is also used inside the [[yii\db\ActiveRecord::save()|save()]] method. The code is 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 directly call the [[yii\db\ActiveRecord::insert()|insert()]] or [[yii\db\ActiveRecord::update()|update()]] method to insert or update a record.
Data Validation
Because [[yii\db\ActiveRecord]] inherits from [[yii\base\Model]], it shares the same Input validation Function. You can declare and execute validation rules by rewriting the [[yii\db\ActiveRecord::rules()|rules()]] method, and validate data by calling the [[yii\db\ActiveRecord::validate()|validate()]] method.
When you call [[yii\db\ActiveRecord::save()]]], it will automatically call [[yii\db\ActiveRecord::validate()]] by default. Only when the verification is passed, it will really save the data; Otherwise, it will simply return false. You can check the [[yii\db\ActiveRecord::errors]] property to get the error message of the verification process.
Tip: if you are sure that your data does not need to be verified (for example, the data comes from a trusted scenario), you can call save(false) to skip the verification process.
Block assignment
And ordinary Model Similarly, you can also enjoy the Active Record instance Block assignment characteristic. Using this function, you can batch assign values to multiple attributes of an Active Record instance in a single PHP statement, as shown below. Remember, only Security attributes Can be assigned in batch.
$values = [
'name' => 'James',
'email' => 'james@example.com',
];
$customer = new Customer();
$customer->attributes = $values;
$customer->save();Updating Counters
Increasing or decreasing the value of a field in a database table is a common task. We call these columns "count columns". You can update one or more count columns using [[yii\db\ActiveRecord::updateCounters()]]. 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 count column, you will eventually get the wrong result, because this may happen. Multiple requests read and write the same count column concurrently.
Dirty Attributes
When you call [[yii\db\ActiveRecord::save()]] to save the Active Record instance, only the dirty attributes are saved. If the value of an attribute has been modified, it will be considered dirty because it is loaded from dB or just saved to DB. Please note that Active Record will perform data validation regardless of whether there are dirty attributes or not.
Active Record automatically maintains the dirty attribute list. It saves the old values of all attributes and compares them with the latest attribute values. You can call [[yii\db\ActiveRecord::getDirtyAttributes()]] to get the current dirty attributes. You can also call [[yii\db\ActiveRecord::markAttributeDirty()]] to explicitly mark the attribute as dirty.
If you need to get the original value of the attribute, you can call [[yii\db\ActiveRecord::getOldAttributes()]] or [[yii\db\ActiveRecord::getOldAttribute()]]].
Note: the = = = operator is used to compare the old and new values of attributes, so the same value but different types are still considered dirty. This usually happens when a model receives user input from an HTML form, where each value is represented as a string type. In order to ensure the correct type, for example, integer needs to be used Filter verifier : ['attributeName', 'filter', 'filter' => 'intval']. The same applies to other PHP type conversion functions, such as intval()， floatval()， boolval , wait
Default Attribute Values
Some table columns may have default values defined in the database. Sometimes, you may want to pre populate the Web form of an Active Record instance with these default values. To avoid writing the same default value again, you can call [[yii\db\ActiveRecord::loadDefaultValues()]] to fill in the default value defined by dB and enter the corresponding Active Record attribute:
$customer = new Customer(); $customer->loadDefaultValues(); // $customer - > XYZ will be assigned by the default value defined in the "zyz" column
Attribute typecasting
When the query result is filled with [[yii\db\ActiveRecord]], the attribute value will be automatically type converted based on Database table schema Information in. This allows you to get data from the data table, declare it as an integer, fill the ActiveRecord instance with PHP integer, and fill boolean with boolean, etc. However, the type conversion mechanism has several limitations:
- Floating point values are not converted and will be represented as strings, otherwise they may reduce precision.
- The conversion of integer values depends on the integer capacity of the operating system you are using. In particular: the values of columns declared as "unsigned integer" or "large integer" will only be converted to PHP integers for 64 bit operating systems, while in 32-bit operating systems - they will be represented as strings.
It is worth noting that property type conversion is performed only when the ActiveRecord instance is populated from the query results. However, values loaded from HTTP requests or assigned values accessed directly through attributes are not automatically converted. When preparing the SQL statement for saving in ActiveRecord, the table mode is also used to ensure that the value at query time is bound to the with the correct type. However, the property values of ActiveRecord instances are not converted during the save process.
Tip: you can use [[yii\behaviors\AttributeTypecastBehavior]] to simplify attribute type conversion during ActiveRecord validation or saving.
Starting from 2.0.14, Yii ActiveRecord supports more complex data types, such as JSON or multidimensional arrays.
JSON in MySQL and PostgreSQL
After data filling, values from JSON columns will be automatically decoded based on JSON standard decoding rules.
On the other hand, in order to save the attribute value to the JSON column, ActiveRecord will automatically create a [[yii\db\JsonExpression|JsonExpression]] object, which will be QueryBuilder The layer is encoded as a JSON string.
Arrays in PostgreSQL
After data filling, the value from the Array column will be automatically decoded from the encoded value of PgSQL into an [[yii\db\ArrayExpression|ArrayExpression]] object. It inherits from the ArrayAccess interface of PHP, so you can use it as an Array, or call - > getValue () to get the Array itself.
On the other hand, in order to save the attribute value to the array column, ActiveRecord will automatically create a [[yii\db\ArrayExpression|ArrayExpression]] object, which will be QueryBuilder PgSQL string expression encoded into an array in.
You can also use the condition of JSON column in this way:
$query->andWhere(['=', 'json', new ArrayExpression(['foo' => 'bar'])
To learn more about the expression building system, visit Query Builder – add custom conditions and statements article.
Updating Multiple Rows
The above methods can be used for a single Active Record instance to insert or update a single table data row. To update multiple data rows at the same time, you should call [[yii\db\ActiveRecord::updateAll()|updateAll()]], 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 [[yii\db\ActiveRecord::updateAllCounters()|updateAllCounters()]] to update the count column of multiple records at the same time.
// UPDATE `customer` SET `age` = `age` + 1 Customer::updateAllCounters(['age' => 1]);
Deleting Data
To delete single row data, first obtain the instance of the Active Record corresponding to the row, then call the [[yii\db\ActiveRecord::delete()] method.
$customer = Customer::findOne(123); $customer->delete();
You can call the [[yii\db\ActiveRecord::deleteAll()]] method to delete multiple rows or even all of the data. For example,
Customer::deleteAll(['status' => Customer::STATUS_INACTIVE]);
Tip: be very careful when calling [[yii\db\ActiveRecord::deleteAll()|deleteAll()]], because if an error occurs when specifying the condition, it may completely erase all the data in the table.
Active Record Life Cycles
When you implement various functions, you will find it important to understand the life cycle of Active Record. In each life cycle, a series of methods will be called and executed. You can rewrite these methods to customize the life cycle you want. You can also respond to trigger certain Active Record events to inject your custom code into the lifecycle. These events are in the process of developing Active Record behavior It is particularly useful when using the Active Record. The Active Record life cycle can be customized through behavior.
Next, we will summarize the life cycle of various active records, as well as various methods and events involved in the life cycle.
New Instance Life Cycle
When you create a new Active Record instance through the new operator, the following life cycles occur:
- Class
- [[yii\db\ActiveRecord::init()|init()]]: trigger the [[Yii \ DB \ activerecord:: event|init|event|init]] event.
Querying Data Life Cycle
When passed Query method When querying data, the following life cycle will occur for each newly populated Active Record instance:
- Class.
- [[yii\db\ActiveRecord::init()|init()]]: trigger the [[Yii \ DB \ activerecord:: event|init|event|init]] event.
- [[yii\db\ActiveRecord::afterFind()|afterFind()]]: trigger the [[yii\db\ActiveRecord::EVENT_AFTER_FIND|EVENT_AFTER_FIND]] event.
Saving Data Life Cycle
The following lifecycle occurs when an Active Record instance is inserted or updated through [[yii\db\ActiveRecord::save()|save()]]:
- [[yii\db\ActiveRecord::beforeValidate()|beforeValidate()]]: trigger the [[yii\db\ActiveRecord::EVENT_BEFORE_VALIDATE|EVENT_BEFORE_VALIDATE]] event. If this method returns false or the value of [[yii\base\ModelEvent::isValid]] is false, the next steps will be skipped.
- Perform data validation. If the data validation fails, the steps after step 3 will be skipped.
- [[yii\db\ActiveRecord::afterValidate()|afterValidate()]]: trigger the [[Yii \ DB \ activerecord:: event_after|event_after|validate]] event.
- [[yii\db\ActiveRecord::beforeSave()|beforeSave()]]: trigger [[yii\db\ActiveRecord::EVENT_BEFORE_INSERT|EVENT_BEFORE_INSERT]] or [[yii\db\ActiveRecord::EVENT_BEFORE_UPDATE|EVENT_BEFORE_UPDATE]] event. If this method returns false or the value of [[yii\base\ModelEvent::isValid]] is false, the next steps will be skipped.
- Perform real data insertion or update.
- [[yii\db\ActiveRecord::afterSave()|afterSave()]]: trigger the [[yii\db\ActiveRecord::EVENT_AFTER_INSERT|EVENT_AFTER_INSERT]] or [[Yii \ DB \ activerecord:: event_update| event_after_update]] event.
Deleting Data Life Cycle
When you delete an Active Record instance through [[yii\db\ActiveRecord::delete()|delete()]]], the following lifecycle occurs:
- [[yii\db\ActiveRecord::beforeDelete()|beforeDelete()]]: trigger the [[Yii \ DB \ activerecord:: event_before| event_before_delete]] event. If this method returns false or the value of [[yii\base\ModelEvent::isValid]] is false, the next steps will be skipped.
- Perform real data deletion.
- [[yii\db\ActiveRecord::afterDelete()|afterDelete()]]: trigger the [[Yii \ DB \ activerecord:: event_after| event_delete]] event.
Tip: calling the following methods will not start any of the above life cycles, because these methods operate directly on the database rather than based on the Active Record model:
- [[yii\db\ActiveRecord::updateAll()]]
- [[yii\db\ActiveRecord::deleteAll()]]
- [[yii\db\ActiveRecord::updateCounters()]]
- [[yii\db\ActiveRecord::updateAllCounters()]]
Refreshing Data Life Cycle
When refreshing the Active Record instance through [[yii\db\ActiveRecord::refresh()|refresh()]], if the refresh succeeds and the method returns true, the [[Yii \ DB \ activerecord:: event_after|event_refresh]] event will be triggered.
Working with Transactions
AR can be used in two ways affair.
The first method is to explicitly include each method call of Active Record in the transaction block, as shown below,
$customer = Customer::findOne(123);
Customer::getDb()->transaction(function($db) use ($customer) {
$customer->id = 200;
$customer->save();
// ... Other DB operations
});
// perhaps
$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;
}Tip: in the above code, we have two catch blocks for compatibility with PHP 5 X and PHP 7 x. \Exception inherited from \Throwable interface Due to changes in PHP 7.0, if your application only uses php 7.0 and later, you can skip the \ Exception section.
The second method is to list the DB operations that need transaction 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:
// 'api' => self::OP_ALL,
];
}
}The [[yii\db\ActiveRecord::transactions()]] method should return scene Is an array with the key and the value of the DB operation that needs to be put into the transaction. The following constants can represent the corresponding DB operations:
- [[yii\db\ActiveRecord::OP_INSERT|OP_INSERT]]: the insert operation is used to execute [[yii\db\ActiveRecord::insert()|insert()]];
- [[yii\db\ActiveRecord::OP_UPDATE|OP_UPDATE]]: the update operation is used to execute [[yii\db\ActiveRecord::update()|update()]];
- [[yii\db\ActiveRecord::OP_DELETE|OP_DELETE]]: the delete operation is used to execute [[yii\db\ActiveRecord::delete()|delete()]].
Use the | operator to connect the above constants to indicate multiple operations. You can also use the shortcut constant [[yii\db\ActiveRecord::OP_ALL]] to refer to all three operations above.
The principle of this transaction method is: the corresponding transaction is opened when calling the [[yii\db\ActiveRecord::beforeSave()]] method and committed when calling the [[yii\db\ActiveRecord::afterSave()]] method.
Optimistic Locks
Optimistic locking is A way to prevent this conflict: A row of data is updated by multiple users at the same time. 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 also clicks the "save" button to save his edits. In fact, user B is dealing with an outdated version of the article, so it's best to find A way to prevent him from saving the article and prompt him with some information.
Optimistic lock solves the above problem by using a field to record the version number of each line. When saving a row of data with an outdated version number, [[yii\db\StaleObjectException]] exception will be thrown, which prevents the saving of the row. Optimistic lock only supports updating [[yii\db\ActiveRecord::update()]] or deleting a single data row that already exists in [[yii\db\ActiveRecord::delete()]].
Steps for using optimistic locks,
- Create a column in the DB table associated with the Active Record class to store the version number of each row. This column should be of long integer type (BIGINT DEFAULT 0 in MySQL).
- Override the [[Yii \ DB \ activerecord:: optimizationlock()]] method to return the name of this column.
- Implement [[\ Yii \ behaviors \ optisticlockbehavior|optisticlockbehavior]] behavior in your Model class (Note: this behavior class is added in version 2.0.16) to automatically parse the value of this column from the request parameters. Then remove the version attribute from the validation rule because [[\ Yii \ behaviors \ optisticlockbehavior|optisticlockbehavior]] has processed it
- In the Web form for users to fill in, add a hidden field to store the current version number of the row being updated.
- In the controller action of updating data with Active Record, the (try/catch) [[yii\db\StaleObjectException]] exception should be caught. Implement some business logic to resolve conflicts (such as merging changes, prompting obsolete data, etc.).
For example, suppose the version column is named version. You can use the following code to implement optimistic locking.
// ------View layer 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) {
// Conflict resolution code
}
}
// ------Model code-------
use yii\behaviors\OptimisticLockBehavior;
public function behaviors()
{
return [
OptimisticLockBehavior::class,
];
}
public function optimisticLock()
{
return 'version';
}Note: because [[\ yii\behaviors\OptimisticLockBehavior]] is only confirmed when saving the record, if the valid version number submitted by the user is directly resolved: [[\ yii\web\Request::getBodyParam()]], your Model will be extended to trigger the behavior of the sub class in step 3, and at the same time, call the definition of the parent class in step 2, In this way, while you bind the Model to the controller responsible for receiving user input, you have an instance dedicated to internal logic processing. Alternatively, you can implement your own logic by configuring its [[\ Yii \ Behaviors \ optimizationlockbehavior:: $value]] attribute. (Note: this pile is explaining the principles of Behaviors)
Working with Relational Data
In addition to processing a single database table, Active Record can also centralize relevant data so that it can be easily accessed through the original data. For example, customer data is related to order data because a customer may have placed one or more orders. With the appropriate declaration of this relationship, you can use the $customer - > orders expression to access the customer's order information. This expression will return an array containing the customer order information of the Order Active Record instance.
Declaring relationships
You must define the association relationship in the Active Record class before you can use the association data of Active Record. Simply declare an association method for each association that needs to be defined, as shown below,
class Customer extends ActiveRecord
{
// ...
public function getOrders()
{
return $this->hasMany(Order::class, ['customer_id' => 'id']);
}
}
class Order extends ActiveRecord
{
// ...
public function getCustomer()
{
return $this->hasOne(Customer::class, ['id' => 'customer_id']);
}
}In the above code, we declare an orders Association for the customer class and a customer Association for Order.
Each associated method must be named like this: getXyz. Then we call the association name through xyz (lowercase). Note that association names are case sensitive.
When declaring an association relationship, the following information must be specified:
Correspondence of association: specified by calling [[yii\db\ActiveRecord::hasMany()|hasMany()]] or [[yii\db\ActiveRecord::hasOne()|hasOne()]]. In the above example, you can easily see the Association statement: a customer can have many orders, and each order has only one customer.
Associated Active Record class name: used to specify the first parameter of the [[yii\db\ActiveRecord::hasMany()|hasMany()]] or [[yii\db\ActiveRecord::hasOne()|hasOne()]] method. The recommended approach is to call Xyz::class to get the string of the class name, so that you can use the automatic completion of the IDE and make the error detection at the compilation stage effective.
Associated column of two groups of data: used to specify the columns related to two groups of data (the second parameter of hasOne()/hasMany()). The value of the array is filled in the column of the main data (currently, the associated Active Record class is to be declared as the main data), while the key of the array is filled in the column of relevant data.
A simple formula, first the primary key of the attached table, and then the primary key of the main table. As in the example above, customer_id is the attribute of Order and id is the attribute of Customer. (Note: for the second parameter of hasMany(), do not reverse the key value Order of this array)
Accessing Relational Data
After defining the association relationship, you can access the corresponding association data through the association name. Just like accessing an object defined by an associated method, please see the specific concepts attribute . Therefore, we can now call it Association attribute.
// SELECT * FROM `customer` WHERE `id` = 123 $customer = Customer::findOne(123); // SELECT * FROM `order` WHERE `customer_id` = 123 // $orders is an array of Order classes $orders = $customer->orders;
Tip: when you declare an association property called xyz through the getter method getXyz(), you can attribute Visit xyz like that. Note that this naming is case sensitive.
If the association is declared with [[yii\db\ActiveRecord::hasMany()], accessing this association property will return an array of related AR instances; If the association is declared with [[yii\db\ActiveRecord::hasOne()]], accessing this association property will return the relevant AR instance. If the relevant data is not found, it will return null.
When you access the association attribute for the first time, the SQL statement will be executed to obtain the data, as shown in the above example. If you access the same property again, the previous result will be returned without re executing the SQL statement. To force the re execution of SQL statements, you should first unset the associated attribute, such as unset ($customer - > orders).
Tip: Although this concept is different from this one attribute The features are similar, but there is still a very important difference. The property value of a common object property is the same as the type of getter method it defines. The association method returns an instance of [[yii\db\ActiveQuery]] active query generator. The [[yii\db\ActiveRecord]] Active Record instance or an array of Active Record instances will be returned only when the associated property is accessed.
$customer->orders; // Get an array of 'Order' objects $customer->getOrders(); // Returns an instance of the ActiveQuery class
This is useful for creating custom queries, which will be described in the next section.
Dynamic Relational Query
Because the association method returns an instance of [[yii\db\ActiveQuery]], you can use the query construction method to further build this query before executing the 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 association properties, SQL statements will be executed every time you execute a dynamic association query through the association method, even if you have executed the same dynamic association query before.
Sometimes you may need to pass parameters to your Association Declaration so that you can more easily execute dynamic relational queries. For example, you can declare a bigOrders Association as follows,
class Customer extends ActiveRecord
{
public function getBigOrders($threshold = 100) // Reminder from the old driver: the $threshold parameter must be given a default value
{
return $this->hasMany(Order::class, ['customer_id' => 'id'])
->where('subtotal > :threshold', [':threshold' => $threshold])
->orderBy('id');
}
}Then you can execute the following Association query:
// 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;
Relationships via a junction table
In database modeling, when the correspondence between two associated tables is many to many, one is usually introduced Connection table . For example, the order table and the item table can be created by calling order_item is associated with the connection table. An order will be associated with multiple order items, and an order item will also be associated with multiple orders.
After declaring this table Association, you can call [[yii\db\ActiveQuery::via()]] or [[yii\db\ActiveQuery::viaTable()]] to indicate the connection table. The difference between [[yii\db\ActiveQuery::via()]] and [[yii\db\ActiveQuery::viaTable()]] is that the former specifies the connection table according to the existing association name, while the latter directly uses the connection table. For example,
class Order extends ActiveRecord
{
public function getItems()
{
return $this->hasMany(Item::class, ['id' => 'item_id'])
->viaTable('order_item', ['order_id' => 'id']);
}
}Or,
class Order extends ActiveRecord
{
public function getOrderItems()
{
return $this->hasMany(OrderItem::class, ['order_id' => 'id']);
}
public function getItems()
{
return $this->hasMany(Item::class, ['id' => 'item_id'])
->via('orderItems');
}
}An association declared using a join table is equivalent to a normally declared Association, 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 classes $items = $order->items;
Connecting Association definitions via multiple tables
By using the [[yii\db\ActiveQuery::via()|via()]] method, it can also define Association declarations through multiple tables. Considering the above example, we have Customer, Order and Item classes. We can add an association relationship to the Customer class. This association can list all items placed under the Customer's Order. This association is named getPurchasedItems(), and the Association Declaration is shown in the following code example:
class Customer extends ActiveRecord
{
// ...
public function getPurchasedItems()
{
// For the customer's goods, match the 'id' column in the Item with the 'Item' column in the OrderItem_ Match id '
return $this->hasMany(Item::class, ['id' => 'item_id'])
->via('orderItems');
}
public function getOrderItems()
{
// For the goods in the customer Order, the 'id' column of 'Order' and the 'Order' column of OrderItem_ Match id 'column
return $this->hasMany(OrderItem::class, ['order_id' => 'id'])
->via('orders');
}
public function getOrders()
{
// See the above list
return $this->hasMany(Order::class, ['customer_id' => 'id']);
}
}Lazy Loading and Eager Loading
stay Access associated data In, we explained that the associated properties of Active Record instances can be accessed like normal object properties. The SQL statement is executed only the first time you access the associated property. We call this associated data access method deferred 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 statements were executed $orders2 = $customer->orders;
Delayed loading is very convenient. However, you may encounter performance problems when you need to access the same associated properties with 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;
}You see, the above code will generate 101 SQL queries! This is because the SQL statement will be executed every time you access the orders association property of different Customer objects in the for loop.
To solve the above performance problems, you can use the so-called instant loading, 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 execution
$orders = $customer->orders;
}By calling the [[yii\db\ActiveQuery::with()]] method, you can make Active Record return the orders of these 100 customers in one SQL statement. As a result, you reduce the number of SQL statements to be executed from 101 to 2!
You can load one or more associations instantly. You can even load nested associations on the fly. A nested association is an association declared in an associated Active Record class. For example, Customer is associated with Order through the orders Association attribute, and Order is associated with Item through the items Association attribute. When querying customers, you can immediately load orders through nested associations Items the items associated with items.
The following code shows various uses of [[yii\db\ActiveQuery::with()|with()]]. Let's assume that the Customer class has two associations, orders and country, and the Order class has one association, items.
// Load "orders" and "country" immediately
$customers = Customer::find()->with('orders', 'country')->all();
// Equivalent to using an array, the syntax is as follows
$customers = Customer::find()->with(['orders', 'country'])->all();
// No SQL execution
$orders= $customers[0]->orders;
// No SQL execution
$country = $customers[0]->country;
// Instantly load "orders" and nested relationship "orders.items"
$customers = Customer::find()->with('orders.items')->all();
// Access the items in the first order of the first customer
// No SQL query execution
$items = $customers[0]->orders[0]->items;You can also instantly load deeper nested associations, such as a.b.c.d. All parent associations are loaded immediately. That is, when you call [[yii\db\ActiveQuery::with()|with()]] to with a.b.c.d, you will load a, a.b, a.b.c and a.b.c.d immediately.
Tip: Generally speaking, when N associations are loaded immediately, another M associations pass Connection table Declaration, N+M+1 SQL statements will be executed. Note that such a nested Association a.b.c.d counts as four associations.
When an association is loaded immediately, you can customize the corresponding association query through anonymous functions. For example,
// Find all customers and bring 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 association query, you should specify the association name as the key of the array and use the anonymous function as the value of the corresponding array. The anonymous function will accept a $query parameter, which is used to represent the [[yii\db\ActiveQuery]] object of the custom association to execute the association query. In the above code example, we modify the association query by attaching an additional condition about the order status.
Tip: if you call [[yii\db\Query::select()|select()] in the context of the immediate loading, you have to make sure that the columns referenced in the association declaration must be select. Otherwise, the corresponding Models may not load. For example,
$orders = Order::find()->select(['id', 'amount'])->with('customer')->all();
// $orders [0] - > customer will always be 'null'. You should write this to solve this problem:
$orders = Order::find()->select(['id', 'amount', 'customer_id'])->with('customer')->all();Joining with relationships
Tip: the content in this section is only applicable to relational databases, such as MySQL, PostgreSQL, etc.
So far, the association query we have introduced only uses the main table column to query the main table data. In practical application, we often need to use these columns in the association table. For example, we may want to take out customers with at least one active order. To solve this problem, we can build a join query as follows:
// 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();Tip: when building join queries involving JOIN SQL statements, it is important to clear the ambiguity of column names. The common practice is to add the table name as a prefix to the corresponding column name.
However, a better way is to use the existing Association Declaration by calling [[yii\db\ActiveQuery::joinWith()]]:
$customers = Customer::find()
->joinWith('orders')
->where(['order.status' => Order::STATUS_ACTIVE])
->all();Both methods execute the same set of SQL statements. However, the latter method is cleaner and simpler.
By default, [[yii\db\ActiveQuery::joinWith()]] will use LEFT JOIN to connect the main table and the associated table. You can specify different connection types (such as RIGHT JOIN) through the $joinType parameter. If the connection type you want is INNER JOIN, you can directly use the [[yii\db\ActiveQuery::innerJoinWith()]] method instead.
Calling the [[yii\db\ActiveQuery::joinWith()]] method defaults to Instant loading Corresponding associated data. If you don't need those associated data, you can specify its second parameter $eagerLoading as false.
Note: even if you use [[yii\db\ActiveQuery::joinWith()]] or [[yii\db\ActiveQuery::innerJoinWith()]] when instant loading is enabled, the corresponding associated data will not be filled from the results of this JOIN query. Therefore, there is an additional query for each connection, as shown in Instant loading Described in section.
Like [[yii\db\ActiveQuery::with()]], you can join multiple associated tables; You can dynamically customize your association query; You can join using nested associations. You can also combine [[yii\db\ActiveQuery::with()]] and [[yii\db\ActiveQuery::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 additional conditions in the ON part of the JOIN query. This can be done by calling the [[yii\db\ActiveQuery::onCondition()]] method, as follows:
// 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();The above query takes out all customers and retrieves all active orders for each customer. Please note that this is different from our previous example, which only takes out customers with at least one active order.
Tip: when [[yii\db\ActiveQuery]] is modified through [[yii\db\ActiveQuery::onCondition()|onCondition()]], if the query involves a JOIN query, the condition will be placed in the ON part. If the query does not involve a JOIN, the condition is automatically appended to the WHERE part of the query. Therefore, it can contain only the conditions that contain the columns of the associated table. (Note: it means that only the columns of the associated table can be written in oncondition (), and the columns of the main table can be written or not)
Relation table aliases
As mentioned earlier, when using JOIN in query, we need to disambiguate column names. Therefore, an alias is usually defined for a table. You can customize the association query to set the alias of the association query in the following ways:
$query->joinWith([
'orders' => function ($q) {
$q->from(['o' => Order::tableName()]);
},
])However, this seems complex and coupled, whether it's hard coding the table name or calling Order::tableName(). Since version 2.0.7, Yii has provided a shortcut for this. You can now define and use aliases for associated tables as follows:
// Connect the 'orders' association table according to the' orders ID ` sort
$query->joinWith(['orders o'])->orderBy('o.id');The above syntax applies to simple associations. If you need to use the alias of the intermediate table when joining nested associations, such as $query - > joinWith (['orders. Product ']), you need to nest joinWith calls, as shown in the following example:
$query->joinWith(['orders o' => function($q) {
$q->joinWith('product p');
}])
->where('o.amount > 100');Reverse relationships
The association declarations between two Active Record classes are often interrelated. For example, Customer is associated to Order through orders, and Order is associated back to Customer through Customer.
class Customer extends ActiveRecord
{
public function getOrders()
{
return $this->hasMany(Order::class, ['customer_id' => 'id']);
}
}
class Order extends ActiveRecord
{
public function getCustomer()
{
return $this->hasOne(Customer::class, ['id' => 'customer_id']);
}
}Now consider the following 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; // Display "not the same" echo $customer2 === $customer ? 'same' : 'not the same';
We thought $customer and $customer2 were the same, but not! In fact, they do contain the same customer data, but they are different objects. When accessing $order - > customer, you need to execute additional SQL statements to populate a new object $customer2.
In order to avoid redundant execution of the last SQL statement in the above example, we should tell Yii customer that it is the reverse association of orders, which can be declared by calling the [[Yii \ DB \ activequery:: inverteof()]] method, as shown below:
class Customer extends ActiveRecord
{
public function getOrders()
{
return $this->hasMany(Order::class, ['customer_id' => 'id'])->inverseOf('customer');
}
}After modifying the Association Declaration in this way:
// 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; // Output "same" echo $customer2 === $customer ? 'same' : 'not the same';
Note: reverse association cannot be used with Connection table Association Declaration. In other words, if an association is declared through [[yii\db\ActiveQuery::via()]] or [[yii\db\ActiveQuery::viaTable()]]], you can no longer call [[Yii \ DB \ activequery:: inverteof()]].
Saving relationships
When using associated data, you often need to establish associations between different data or destroy existing associations. This requires setting the correct values for the defined associated columns. By using Active Record, you can write the following code:
$customer = Customer::findOne(123); $order = new Order(); $order->subtotal = 100; // ... // Set the property associated with "customer" as the "customer" definition $order->customer_id = $customer->id; $order->save();
AR provides a [[yii\db\ActiveRecord::link()]] method to better complete this task:
$customer = Customer::findOne(123);
$order = new Order();
$order->subtotal = 100;
// ...
$order->link('customer', $customer);The [[yii\db\ActiveRecord::link()]] method needs to specify the association name and the target AR instance to be associated. This method will modify the value of the attribute to connect two AR instances and save them to the database. In the above example, it will set the Customer of the Order instance_ The id attribute is the value of the id attribute of the Customer instance, and then saved to the database.
Note: you cannot associate two new Active Record instances.
The benefits of using [[yii\db\ActiveRecord::link()]] are junction table It is more obvious when defining relationships. For example, you can associate an Order instance with an Item instance using the following code:
$order->link('items', $item);The above code will be automatically displayed in order_ Insert a row into the item association table to associate the two data records order and item.
Info: [[yii\db\ActiveRecord::link()]] method will not perform any data validation when saving the corresponding Active Record instance. Before calling this method, you should verify all input data.
The reverse operation of the [[yii\db\ActiveRecord::link()]] method is the [[yii\db\ActiveRecord::unlink()]] method, which will break the existing association 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 [[yii\db\ActiveRecord::unlink()]] method sets the specified foreign key value to specify the existing association as null. In addition, you can choose to delete the record line containing the key value by setting the $delete parameter to true and passing it to the method.
If [[yii\db\ActiveRecord::unlink()]] is called when there is a connection table in the association relationship, if the $delete parameter is true, the foreign key or corresponding row in the connection table will be deleted.
Cross database relationships
Active Record allows you to declare associations between Active Record classes driven by different databases. These databases can be of different types (such as MySQL and PostgreSQL, or MS SQL and MongoDB), and they can also run on different servers. You can use the same syntax to execute associative queries. For example,
// The table corresponding to customer is the "customer" table in the relational database (such as 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::class, ['customer_id' => 'id']);
}
}
// Comment corresponds to the "comment" set in the MongoDB database
class Comment extends \yii\mongodb\ActiveRecord
{
public static function collectionName()
{
return 'comment';
}
public function getCustomer()
{
// A comment corresponds to a customer
return $this->hasOne(Customer::class, ['id' => 'customer_id']);
}
}
$customers = Customer::find()->with('comments')->all();You can copy most of the association query functions described in this section.
Note: [[yii\db\ActiveQuery::joinWith()|joinWith()]] this function is limited to whether some databases support cross database JOIN queries. Therefore, you can't use this method in the above code, because MongoDB doesn't support JOIN query.
Customizing Query Classes
By default, yii\db\ActiveQuery supports all AR queries. To use a custom query class in an AR class, you should override the [[yii\db\ActiveRecord::find()]] method and return an instance of your custom 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, for the Comment class, whether you execute a query (such as find(), findOne()) or define an association (such as hasOne()), you will call the CommentQuery instance instead of the ActiveQuery instance.
Now you can define the CommentQuery class and use your skills to improve the query construction experience. For example,
// file CommentQuery.php
namespace app\models;
use yii\db\ActiveQuery;
class CommentQuery extends ActiveQuery
{
// Add some conditions by default (you can skip)
public function init()
{
$this->andOnCondition(['deleted' => false]);
parent::init();
}
// ... Add a custom query method here
public function active($state = true)
{
return $this->andOnCondition(['active' => $state]);
}
}Note: as an alternative to the [[yii\db\ActiveQuery::onCondition()]] method, you should call the [[yii\db\ActiveQuery::andOnCondition()]] or [[yii\db\ActiveQuery::orOnCondition()]] method to attach the new conditions, otherwise the existing conditions may be overwritten in a newly defined query method.
Then you can build your query as follows:
$comments = Comment::find()->active()->all(); $inactiveComments = Comment::find()->active(false)->all();
Tip: in large projects, it is recommended that you use a custom query class to accommodate most of the query related code to keep the Active Record class concise.
You can also use the newly created query construction method in the definition of Comment association relationship or when executing Association query:
class Customer extends \yii\db\ActiveRecord
{
public function getActiveComments()
{
return $this->hasMany(Comment::class, ['customer_id' => 'id'])->active();
}
}
$customers = Customer::find()->joinWith('activeComments')->all();
// Or so
class Customer extends \yii\db\ActiveRecord
{
public function getComments()
{
return $this->hasMany(Comment::class, ['customer_id' => 'id']);
}
}
$customers = Customer::find()->joinWith([
'comments' => function($q) {
$q->active();
}
])->all();Tip: in Yii 1.1, there is a concept called naming range. Naming range is no longer supported in Yii 2.0. You can still use custom query classes and query methods to achieve the same effect.
Selecting extra fields
When the Active Record instance is filled from the query result, the value of its attribute will be filled by the corresponding column from the data result set.
You can get other columns or values from the query and store them in the Active Record activity record. For example, suppose we have a table called room that contains information about the rooms available in the hotel. Each room uses the fields length, width and height to store information about its space size. Imagine that we need to retrieve a list of all available rooms in reverse order of size. You can't use PHP to calculate the volume, but since we need to sort these records according to its value, you still need volume to display in this list. To achieve this goal, you need to declare an additional field in your room activity record class, which will store the value of volume:
class Room extends \yii\db\ActiveRecord
{
public $volume;
// ...
}Then you need to write a query that can calculate the size of the room and perform sorting:
$rooms = Room::find()
->select([
'{{room}}.*', // select all columns
'([[length]] * [[width]] * [[height]]) AS volume', // calculate volume
])
->orderBy('volume DESC') // Use sorting
->all();
foreach ($rooms as $room) {
echo $room->volume; // Contains the value calculated by SQL
}The extra field feature is useful for aggregate queries. Suppose you need to display the order quantity of a series of customers. First, you need to declare a Customer class using the orders relationship and specify additional fields to store the count result:
class Customer extends \yii\db\ActiveRecord
{
public $ordersCount;
// ...
public function getOrders()
{
return $this->hasMany(Order::class, ['customer_id' => 'id']);
}
}Then you can write a query to JOIN the order table and calculate the total number of orders:
$customers = Customer::find()
->select([
'{{customer}}.*', // Select all fields of the customer table
'COUNT({{order}}.id) AS ordersCount' // Calculate total orders
])
->joinWith('orders') // Connection table
->groupBy('{{customer}}.id') // Group queries to ensure that the aggregation function takes effect
->all();One disadvantage of using this method is that if the data is not loaded from the SQL query, it must be calculated separately again. Therefore, if you get individual data records through regular query without additional select statement, it will not be able to return the actual value of additional fields. The same can happen to newly saved records.
$room = new Room(); $room->length = 100; $room->width = 50; $room->height = 2; $room->volume; // Is' null 'because it is not declared (assigned)
Through [[yii\db\BaseActiveRecord::get()]] and [[yii\db\BaseActiveRecord::set()]] magic methods, we can assign attributes to behavior characteristics:
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 does not provide volume volume, the model will be able to automatically calculate the value of volume when accessing the properties of the model.
When defining an association relationship, you can also calculate aggregation fields:
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 can avoid calling an empty primary key for query
}
if ($this->_ordersCount === null) {
$this->setOrdersCount($this->getOrders()->count()); // Aggregate fields are calculated on demand according to the association relationship
}
return $this->_ordersCount;
}
// ...
public function getOrders()
{
return $this->hasMany(Order::class, ['customer_id' => 'id']);
}
}Using this code, if 'ordercount' exists in the 'select' statement - it will get data from the query result set to fill in the customer:: ordercount attribute, otherwise it will use the Customer::orders association to calculate on demand when accessed.
This method is also suitable for creating shortcuts to some associated data, especially for aggregation. For example:
class Customer extends \yii\db\ActiveRecord
{
/**
* Define a read-only virtual property for aggregated data
*/
public function getOrdersCount()
{
if ($this->isNewRecord) {
return null; // This can avoid calling an empty primary key for query
}
return empty($this->ordersAggregation) ? 0 : $this->ordersAggregation[0]['counted'];
}
/**
* Declare a regular 'orders' Association
*/
public function getOrders()
{
return $this->hasMany(Order::class, ['customer_id' => 'id']);
}
/**
* Based on the 'orders' Association, declare a new association for query 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; // Output the associated aggregate data without additional queries, because we use instant loading
}
$customer = Customer::findOne($pk);
$customer->ordersCount; // Output aggregate data from a deferred loaded Association💖 Those who like this document are welcome to like, collect, leave a message or forward it. Thank you for your support! Author email: zhuzixian520@126.com
This article was first published in LearnKu.com On the website.