1. Data Access Object (DAO)
Yii
DAO is built on PHP Data Objects (PDO). It is an extension that provides unified data access for many popular DBMS, including
MySQL, PostgreSQL, etc. Therefore, to use Yii DAO, PDO extension and specific PDO database driver (such as PDO_MYSQL)
Must be installed.
Yii DAO mainly includes the following four categories:
CDbConnection: Represents a database connection.
CDbCommand: Represents a SQL statement executed through the database.
CDbDataReader: Represents a forward-only stream of rows from a query result set.
CDbTransaction: Represents a database transaction.
1. Establish database connection
Yes
Establish a database connection and create a CDbConnection
Instance and activate it. Connecting to a database requires a data source name (DSN) to specify connection information. Username and password may also be used. When an error occurs while connecting to the database
(e.g. wrong DSN or invalid username/password), an exception will be thrown.
$connection=new CDbConnection($dsn,$username,$password);
// Establish a connection. You can use try...catch to catch exceptions that may be thrown
$connection->active=true;
......
$connection->active=false; // Close connection
The format of the DSN depends on the PDO database driver used. In general, the DSN contains the name of the PDO driver, followed by a colon, followed by driver-specific connection syntax. Check out the PDO documentation for more information. Below is a list of commonly used DSN formats.
* SQLite: sqlite:/path/to/dbfile
* MySQL: mysql:host=localhost;dbname=testdb
* PostgreSQL: pgsql:host=localhost;port=5432;dbname=testdb
* SQL Server: mssql:host=localhost;dbname=testdb
* Oracle: oci:dbname=//localhost:1521/testdb
Since CDbConnection inherits from CApplicationComponent, we can also use it as an application component. To do this, configure a db (or other name) application component in the application configuration as follows:
array(
......
'components'=>array(
......
'db'=>array(
'class'=>'CDbConnection',
'connectionString'=>'mysql:host=localhost;dbname=testdb',
'username'=>'root',
'password'=>'password',
'emulatePrepare'=>true, // needed by some MySQL installations
),
),
)
Then we can access the database connection through Yii::app()->db. It is automatically activated unless we specifically configure CDbConnection::autoConnect to false. This way, this single DB connection can be shared in many places in our code.
2. Execute SQL statement
After the database connection is established, SQL statements can be executed by using CDbCommand. You can create a CDbCommand instance by calling CDbConnection::createCommand() with the specified SQL statement as argument.
$connection=Yii::app()->db; // Assume you have established a "db" connection
// If not, you may need to explicitly establish a connection:
// $connection=new CDbConnection($dsn,$username,$password);
$command=$connection->createCommand($sql);
// If necessary, this SQL statement can be modified as follows:
// $command->text=$newSQL;
A SQL statement will be executed through CDbCommand in the following two ways:
execute(): Execute a non-query SQL statement, such as INSERT, UPDATE and DELETE. If successful, it returns the number of rows affected by this execution.
query(): Execute a SQL statement that returns several rows of data, such as SELECT. If successful, it returns a CDbDataReader instance through which the resulting rows of data can be iterated. For simplicity, (Yii) also implements a series of queryXXX() methods to directly return query results.
If an error occurs while executing the SQL statement, an exception will be thrown.
$rowCount=$command->execute(); // Execute query-free SQL
$dataReader=$command->query(); // Execute a SQL query
$rows=$command->queryAll(); // Query and return all rows in the result
$row=$command->queryRow(); // Query and return the first row in the result
$column=$command->queryColumn(); // Query and return the first column in the result
$value=$command->queryScalar(); // Query and return the first field of the first row in the result
3. Get query results
at
After CDbCommand::query() generates a CDbDataReader instance, you can call it repeatedly
CDbDataReader::read() Gets the rows in the result. You can also use it in PHP’s foreach language construct
CDbDataReader retrieves data line by line.
$dataReader=$command->query();
// Call read() repeatedly until it returns false
while(($row=$dataReader->read())!==false) { ... }
// Use foreach to iterate through each row in the data
foreach($dataReader as $row) { ... }
//Extract all rows into an array at once
$rows=$dataReader->readAll();
Note: Unlike query(), all queryXXX() methods will return data directly. For example, queryRow() returns an array representing the first row of the query results.
4. Use transactions
Transactions, represented in Yii as CDbTransaction instances, may be started in the following situations:
* Start transaction.
* Execute queries one by one. Any updates to the database are not visible to the outside world.
* Commit the transaction. If the transaction succeeds, the update becomes visible.
* If one of the queries fails, the entire transaction is rolled back.
The above workflow can be implemented through the following code:
$transaction=$connection->beginTransaction();
try
{
$connection->createCommand($sql1)->execute();
$connection->createCommand($sql2)->execute();
//.... other SQL executions
$transaction->commit();
}
catch(Exception $e) // If a query fails, an exception will be thrown
{
$transaction->rollBack();
}
5. Binding parameters
To avoid SQL injection attacks and improve the efficiency of repeatedly executing SQL statements, you can "prepare" a SQL statement with optional parameter placeholders. When the parameters are bound, these placeholders will be Replace with actual parameters.
ginseng
Number placeholders can be named (appear as a unique tag) or unnamed (appear as a question mark). Call CDbCommand::bindParam() or
CDbCommand::bindValue()
to replace these placeholders with actual parameters. These parameters do not need to be enclosed in quotes: the underlying database driver takes care of this for you. Parameter binding must be completed before the SQL statement is executed.
// A SQL
with two placeholders ":username" and ":email"$sql="INSERT INTO tbl_user (username, email) VALUES(:username,:email)";
$command=$connection->createCommand($sql);
// Replace the placeholder ":username" with the actual username
$command->bindParam(":username",$username,PDO::PARAM_STR);
// Replace the placeholder ":email" with the actual Email
$command->bindParam(":email",$email,PDO::PARAM_STR);
$command->execute();
// Insert another row with new parameter set
$command->bindParam(":username",$username2,PDO::PARAM_STR);
$command->bindParam(":email",$email2,PDO::PARAM_STR);
$command->execute();
Methods bindParam() and bindValue() are very similar. The only difference is that the former uses a PHP variable to bind the parameter, while the latter uses a value. For those large data block parameters in memory, for performance reasons, the former should be used first.
6. Binding column
When getting query results, you can also use PHP variables to bind columns. This will automatically fill in the latest value every time a row in the query results is obtained.
$sql="SELECT username, email FROM tbl_user";
$dataReader=$connection->createCommand($sql)->query();
//Use $username variable to bind the first column (username)
$dataReader->bindColumn(1,$username);
// Use the $email variable to bind the second column (email)
$dataReader->bindColumn(2,$email);
while($dataReader->read()!==false)
{
// $username and $email contain the username and email in the current line
}
7. Use table prefix
Yes
Use the table prefix and configure the CDbConnection::tablePrefix property to the desired table prefix. Then, use
in the SQL statement{{TableName}} represents the name of the table, where TableName refers to the table name without prefix. For example, if the database contains a database named tbl_user
table, and tbl_ is configured as the table prefix, then we can use the following code to execute user-related queries:
$sql='SELECT * FROM {{user}}';
$users=$connection->createCommand($sql)->queryAll();
2. Active Record
Though
While Yii DAO can handle almost any database-related task, it is likely that we will spend 90% of the time writing something that performs ordinary CRUD (create, read,
SQL statements for update and delete) operations. And it will become difficult to maintain when our code is mixed with SQL statements. To solve these problems, we can use Active
Record.
Active Record (AR) is a popular object-relational mapping (ORM) technology. Every AR
The class represents a data table (or view). The columns of the data table (or view) are reflected in the AR class as attributes of the class. An AR instance represents a row in the table. Common CRUD operations as AR
method implementation. Therefore, we can access the data in a more object-oriented way. For example, we can use the following code to insert a new row into the tbl_post table.
$post=new Post;
$post->title='sample post';
$post->content='post body content';
$post->save();
Note: AR is not intended to solve all database-related tasks. Its best applications are modeling data tables into PHP structures and executing queries that do not contain complex SQL statements. For complex query scenarios, Yii DAO should be used.
1. Establish database connection
AR relies on a database connection to perform database-related operations. By default, it assumes that the db application component provides the required CDbConnection database connection instance. The following application configuration provides an example:
return array(
'components'=>array(
'db'=>array(
'class'=>'system.db.CDbConnection',
'connectionString'=>'sqlite:path/to/dbfile',
//Enable schema caching to improve performance
// 'schemaCachingDuration'=>3600,
),
),
);
Mention
indicates: Since Active Record relies on the metadata of the table to determine column information, it takes time to read the metadata and parse it.
If the table structure of your database rarely changes, you should enable it by configuring the CDbConnection::schemaCachingDuration attribute to a value greater than zero
Table structure cache.
If you want to use an application component that is not a db, or if you want to use AR to handle multiple databases, you should override CActiveRecord::getDbConnection(). The CActiveRecord class is the base class for all AR classes.
Tip: There are two ways to use multiple databases through AR. If the structure of the database is different, you can create different AR base classes to implement different getDbConnection(). Otherwise, it's a good idea to dynamically change the static variable CActiveRecord::db.
2. Define AR class
To access a data table, we first need to define an AR class by integrating CActiveRecord. Each AR class represents a separate data table, and an AR instance represents a row in that table.
The following example demonstrates the simplest code for the AR class representing the tbl_post table:
class Post extends CActiveRecord
{
public static function model($className=__CLASS__)
{
return parent::model($className);
}
public function tableName()
{
return 'tbl_post';
}
}
Tip: Since AR classes are often referenced in multiple places, we can import the entire directory containing AR classes instead of importing them one by one. For example, if all our AR class files are in the protected/models directory, we can configure the application as follows:
return array(
'import'=>array(
'application.models.*',
),
);
By default, the name of the AR class is the same as the name of the data table. If different, override the tableName() method.
To use the table prefix function, the tableName() method of the AR class can be overridden as follows
public function tableName()
{
return '{{post}}';
}
This means that we enclose the table name without a prefix in double curly brackets so that Yii can automatically add the prefix and return the complete table name.
The values of columns in data table rows can be accessed as properties of the corresponding AR instance. For example, the following code sets the title column (attribute):
$post=new Post;
$post->title='a sample post';
Though
Although we have never explicitly defined the attribute title in the Post class, we can still access it through the above code. This is because title is a
in the tbl_post tablecolumn, CActiveRecord makes it an accessible property through PHP’s __get() magic method. If we try to access a non-existent column in the same way, a
will be thrownException.
If a table does not have a primary key, you must override the primaryKey() method in the corresponding AR class as follows to specify which column or columns serve as the primary key.
public function primaryKey()
{
return 'id';
// For composite primary keys, return an array similar to the following
// return array('pk1', 'pk2');
}
3. Create records
To insert a new row into the data table, we need to create an instance of the corresponding AR class, set its properties related to the table's columns, and then call the save() method to complete the insertion:
$post=new Post;
$post->title='sample post';
$post->content='content for the sample post';
$post->create_time=time();
$post->save();
If the table's primary key is auto-incrementing, the AR instance will contain an updated primary key after the insert is completed. In the example above, the id attribute will reflect the primary key value of the newly inserted post, even though we never explicitly changed it.
If a column is defined with a static default value (such as a string, a number) in the table structure. Then the corresponding attributes in the AR instance will automatically contain this default value when the instance is created. One way to change this default value is to explicitly define this attribute in the AR class:
class Post extends CActiveRecord
{
public $title='please enter a title';
......
}
$post=new Post;
echo $post->title; // This will show: please enter a title
Before the record is saved (inserted or updated) to the database, its properties can be assigned to the CDbExpression type. For example, to save a timestamp returned by MySQL's NOW() function, we can use the following code:
$post=new Post;
$post->create_time=new CDbExpression('NOW()'); //The CDbExpression class is to calculate the value of database expression
// $post->create_time='NOW()'; will not work because
// 'NOW()' will be processed as a string.
$post->save();
Mention
Example: Since AR allows us to perform database operations without writing a lot of SQL statements,
We often wonder what SQL statements AR executes behind the scenes. This can be achieved by enabling Yii's logging function. For example, we enable CWebLogRoute in the application configuration, I
We will see the executed SQL statement at the end of each web page.
We can also set CDbConnection::enableParamLogging to true in the application configuration, so that the parameter values bound in the SQL statement will also be recorded
Record.
4. Read records
To read the data in the data table, we can call one of the find series methods as follows:
// Find the first row in the results that meets the specified conditions
$post=Post::model()->find($condition,$params);
// Find the row with the specified primary key value
$post=Post::model()->findByPk($postID,$condition,$params);
// Find rows with specified attribute values
$post=Post::model()->findByAttributes($attributes,$condition,$params);
// Find the first row in the result through the specified SQL statement
$post=Post::model()->findBySql($sql,$params);
As shown above, we call the find method through Post::model(). Remember that the static method model() is required for every AR class. This method returns an AR instance in the object context for accessing class-level methods (something like static class methods).
If the find method finds a row that meets the query conditions, it will return a Post instance whose attributes contain the values of the corresponding columns in the data table row. We can then read the loaded value like a normal object property, for example echo $post->title;.
The find method will return null if nothing is found in the database using the given query criteria.
When calling find, we use $condition and $params to specify the query conditions. Here $condition can be a WHERE string in the SQL statement and $params is an array of parameters whose values should be bound to the placeholders in $condation. For example:
//Find the line with postID=10
$post=Post::model()->find('postID=:postID', array(':postID'=>10));
Note: In the above example, we may need to escape the reference to the postID column in a specific DBMS. For example, if we were using PostgreSQL, we would have to write this expression as "postID"=:postID because PostgreSQL is case insensitive to column names by default.
We can also use $condition to specify more complex query conditions. Instead of using strings, we can make $condition an instance of CDbCriteria , which allows us to specify conditions that are not limited to WHERE. For example:
$criteria=new CDbCriteria;
$criteria->select='title'; // Select only the 'title' column
$criteria->condition='postID=:postID';
$criteria->params=array(':postID'=>10);
$post=Post::model()->find($criteria); // $params are not needed
Note that when using CDbCriteria as query criteria, the $params parameter is no longer needed as it can be specified in the CDbCriteria, just like above.
An alternative to CDbCriteria is to pass an array to the find method. The keys and values of the array respectively correspond to the attribute names and values of the criterion. The above example can be rewritten as follows:
$post=Post::model()->find(array(
'select'=>'title',
'condition'=>'postID=:postID',
'params'=>array(':postID'=>10),
));
When
When a query condition is about matching several columns by specified values, we can use findByAttributes(). We make $attributes
The parameter is an array of values indexed by column name. In some frameworks, this task can be accomplished by calling something like findByNameAndTitle
method implementation. Although this approach looks tempting, it often causes confusion, conflicts, and issues such as case-sensitivity of column names.
When multiple rows of data match the specified query conditions, we can bring them all back through the findAll method below. Each has its own find method, as we've already covered.
//Find all rows that meet the specified conditions
$posts=Post::model()->findAll($condition,$params);
// Find all rows with the specified primary key
$posts=Post::model()->findAllByPk($postIDs,$condition,$params);
// Find all rows with the specified attribute value
$posts=Post::model()->findAllByAttributes($attributes,$condition,$params);
// Find all rows through the specified SQL statement
$posts=Post::model()->findAllBySql($sql,$params);
If nothing matches the query, findAll will return an empty array. This is different from find, which returns null if nothing is found.
In addition to the find and findAll methods described above, for convenience, (Yii) also provides the following methods:
// Get the number of rows that meet the specified conditions
$n=Post::model()->count($condition,$params);
// Get the number of result rows through the specified SQL
$n=Post::model()->countBySql($sql,$params);
// Check if there is at least one line compounding the specified condition
$exists=Post::model()->exists($condition,$params);
5. Update record
After the AR instance is populated with column values, we can change them and save them back to the data table.
$post=Post::model()->findByPk(10);
$post->title='new post title';
$post->save(); // Save changes to database
Positive
As we can see, we use the same save() method to perform insert and update operations. If an AR instance was created using the new operator, call save()
A new row of data will be inserted into the data table; if the AR instance is the result of a find or findAll method, call save()
Will update existing rows in the table. In fact, we use CActiveRecord::isNewRecord to indicate whether an AR instance is new.
It is also possible to directly update one or more rows in a data table without loading it first. AR provides the following convenient class-level methods to achieve this purpose:
//Update the rows that meet the specified conditions
Post::model()->updateAll($attributes,$condition,$params);
//Update rows that match the specified conditions and primary keys
Post::model()->updateByPk($pk,$attributes,$condition,$params);
//Update the count column of rows that meet the specified conditions
Post::model()->updateCounters($counters,$condition,$params);
In the above code, $attributes is an array containing column values indexed by column names; $counters is an array of incrementable values indexed by column names; $condition and $params are in the previous paragraph Already described.
6. Delete records
If an AR instance is filled with a row of data, we can also delete this row of data.
$post=Post::model()->findByPk(10); // Suppose there is a post with an ID of 10
$post->delete(); // Delete this row from the data table
Note that after deletion, the AR instance remains unchanged, but the corresponding row in the data table is gone.
Using the class level code below it is possible to delete a row without loading it first.
//Delete rows that match specified conditions
Post::model()->deleteAll($condition,$params);
//Delete rows that match the specified conditions and primary keys
Post::model()->deleteByPk($pk,$condition,$params);
7. Data verification
When inserting or updating a row, we often need to check whether the column value conforms to the corresponding rules. This is even more important if the column's values are provided by the end user. Overall, we can never trust any data coming from the client.
AR automatically performs data validation when save() is called. Validation is based on the rules specified in the rules() method of the AR class. For more details about validation rules, see the Declaring Validation Rules section. The following is a typical workflow required when saving records.
if($post->save())
{
//The data is valid and successfully inserted/updated
}
else
{
//The data is invalid, call getErrors() to extract error information
}
When the data to be inserted or updated is submitted by the end user in an HTML form, we need to assign it to the corresponding AR attribute. We can achieve this in a similar way:
$post->title=$_POST['title'];
$post->content=$_POST['content'];
$post->save();
If there are many columns, we can see a very long list for this kind of copying. This can be simplified by using the attributes property as shown below. More information can be found in the Safe Attribute Assignment section and the Creating Actions section.
// Assume $_POST['Post'] is an array with the column name index column value as the value
$post->attributes=$_POST['Post'];
$post->save();
8. Comparison records
Similar to table records, AR instances are identified by their primary key values. Therefore, to compare two AR instances, assuming they belong to the same AR class, we only need to compare their primary key values. However, a simpler way is to call CActiveRecord::equals().
Unlike AR implementations in other frameworks, Yii supports multiple primary keys in its AR. A composite primary key consists of two or more fields. Correspondingly, the primary key value is represented as an array in Yii. The primaryKey attribute gives the primary key value of an AR instance.
9. Customization
CActiveRecord provides several placeholder methods that can be overridden in subclasses to customize its workflow.
beforeva lidate and afterValidate: These two will be called before and after verifying the validity of the data.
beforeSave and afterSave: These two will be called before and after saving the AR instance.
beforeDelete and afterDelete: These two will be called before and after an AR instance is deleted.
afterConstruct: This will be called after each AR instance is created using the new operator.
beforeFind: This will be called before an AR finder is used to execute a query (e.g. find(), findAll()).
afterFind: This will be called when each AR instance is created as a result of a query.
10. Use AR to handle affairs
Each AR instance contains a property named dbConnection , which is an instance of CDbConnection, so that we can use the transaction function provided by Yii DAO with AR when needed:
$model=Post::model();
$transaction=$model->dbConnection->beginTransaction();
try
{
// Find and save are two steps that may be intervened by another request
// This way we use a transaction to ensure its consistency and integrity
$post=$model->findByPk(10);
$post->title='new post title';
$post->save();
$transaction->commit();
}
catch(Exception $e)
{
$transaction->rollBack();
}
11. Naming range
Named scope represents a named query rule, which can be used in conjunction with other named scopes and applied to Active Record queries.
Named scopes are mainly declared in the form of name-rule pairs in the CActiveRecord::scopes() method. The following code declares two named ranges, published and recently, in the Post model class.
class Post extends CActiveRecord
{
......
public function scopes()
{
return array(
'published'=>array(
'condition'=>'status=1',
),
'recently'=>array(
'order'=>'create_time DESC',
'limit'=>5,
),
);
}
}
Each named range is declared as an array that can be used to initialize a CDbCriteria instance. For example, a recently named range specifies that the order attribute is create_time DESC and the limit attribute is 5. After they are translated into query rules, the last 5 posts will be returned.
Named ranges are mostly used as modifiers for find method calls. Several named ranges can be chained together to form a more restrictive query result set. For example, to find recent posts we can use code like this:
$posts=Post::model()->published()->recently()->findAll();
In general, named ranges must appear on the left side of a find method call. Each of them provides a query rule and is united to other rules, including the one passed to the find method call. The end result is like adding a series of filters to a query.
Named ranges can also be used in update and delete methods. For example, the following code will delete all recent posts:
Post::model()->published()->recently()->delete();
Note: Named scopes can only be used for class-level methods. That is, this method must be called using ClassName::model().
12. Parameterized naming range
Named ranges can be parameterized. For example, we want to customize the number of posts specified in the recently named scope. To achieve this, instead of declaring the named scope in the CActiveRecord::scopes method, we need to define a method with the same name as this named scope:
public function recently($limit=5)
{
$this->getDbCriteria()->mergeWith(array(
'order'=>'create_time DESC',
'limit'=>$limit,
));
return $this;
}
Then, we can use the following statement to get the 3 most recent posts.
$posts=Post::model()->published()->recently(3)->findAll();
In the above code, if we do not provide parameter 3, we will get the 5 most recent posts by default.
13. Default naming range
Model
Type classes can have a default named scope which will apply to all (including related ones)
Query about this model. For example, a website that supports multiple languages may only display content in the language specified by the current user. Because there may be many queries about the content of this website, we can define a default
named scope to solve this problem. To achieve this, we override the CActiveRecord::defaultScope method as follows:
class Content extends CActiveRecord
{
public function defaultScope()
{
return array(
'condition'=>"language='".Yii::app()->language."'",
);
}
}
Now, if the following method is called, the query rules defined above will be automatically used:
$contents=Content::model()->findAll();
Note that the default named range will only apply to SELECT queries. INSERT, UPDATE and DELETE queries will be ignored.
3. Relational Active Record (Related Query)
We already know how to obtain data from a single data table through Active Record (AR). In this section, we will introduce how to use AR to connect to associated data tables to obtain data.
Before using associated AR, you must first establish the primary key-foreign key association between associated data tables in the database. AR needs to determine how to connect data by analyzing the meta-information that defines the association of data tables in the database.
1. How to declare association
Before using AR to perform related queries, we need to tell AR how the various AR classes are related.
The association between AR classes directly reflects the association between the data tables represented by this class in the database. From the perspective of a relational database, there are three possible associations between two data tables A and B: one-to-many, one-to-one, and many-to-many. In AR, there are four types of associations:
BELONGS_TO: If the relationship between data tables A and B is one-to-many, then we say that B belongs to A.
HAS_MANY: If the relationship between data tables A and B is many-to-one, then we say that B has many A (B has many A).
HAS_ONE: This is a special case of the 'HAS_MANY' relationship. When A has at most one, we say that B has one A (B has one A).
MANY_MANY:
This is equivalent to the many-to-many relationship in a relational database. Because most relational databases do not directly support many-to-many relationships, a separate association table is usually needed to decompose the many-to-many relationship into two one-to-one relationships
Many relationships. If we understand it in AR terms, we can think that the MANY_MANY relationship consists of BELONGS_TO and HAS_MANY.
Declaring relationships in AR is achieved by overriding the relations() method in the parent class CActiveRecord. This method returns an array containing the relationship definition. Each set of key values in the array represents an association:
'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)
This
The VarName inis the name of this association; RelationType specifies the type of this association. There are four constants representing the four types of association
Types: self::BELONGS_TO, self::HAS_ONE, self::HAS_MANY and self::MANY_MANY;
ClassName is the class name of the AR class to which this relationship is associated; ForeignKey specifies which foreign key this relationship is connected through. Additional
at the backOptions can add some additional settings, which will be introduced later.
The following code demonstrates how to define the association between User and Post.
class Post extends CActiveRecord {
public function relations() {
return array(
'author'=>array(
self::BELONGS_TO,
'User',
'authorID'
),
'categories'=>array(
self::MANY_MANY,
'Category',
'PostCategory(postID, categoryID)'
),
);
}
}
class User extends CActiveRecord {
public function relations() {
return array(
'posts'=>array(
self::HAS_MANY,
'Post',
'authorID'
),
'profile'=>array(
self::HAS_ONE,
'Profile',
'ownerID'
),
);
}
}
said
Ming: Sometimes the foreign key may consist of two or more fields, where multiple field names can be separated by commas or spaces,
Write them here together. For many-to-many relationships, the associated table must be noted in the foreign key, such as categories
in the Post classIn the association, the foreign key needs to be written as PostCategory(postID, categoryID).
When declaring an association in an AR class, each association will be added to the AR class as an attribute, and the attribute name is the name of the association. When performing a related query, these attributes will be set to instances of the associated AR classes. For example, when querying to obtain a Post instance, its $author attribute is an instance of the User class representing the Post author.
2. Related query
Enter
The simplest way to perform a related query is to access a related attribute of a related AR object. If this property has not been accessed before, a correlation query will be started, connecting through the primary key of the current AR object
Related tables to obtain the values of associated objects, and then save these data in the properties of the objects. This method is called "lazy loading", that is, only when a certain attribute is accessed, will it actually be loaded into the database
Get some related data. The following example describes the lazy loading process:
// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post's author: a relational query will be performed here
$author=$post->author;
In different association situations, if no results are found, the returned values are also different: BELONGS_TO and HAS_ONE associations return null when there are no results; HAS_MANY and MANY_MANY return an empty array when there are no results.
The lazy loading method is very convenient to use, but it is not efficient in some cases. For example, if we want to obtain the author information of N posts, using the delayed method will execute N connection queries. At this point we should use the so-called eager loading method.
The eager loading method retrieves the main AR instance and its related AR instances. This is done using the with() method plus the find or findAll method
Done. For example,
$posts=Post::model()->with('author')->findAll();
Up
The code above will return an array of Post instances. Unlike the lazy loading method, the author property in each Post instance has been associated
before we access this property.User instance population. Instead of executing a join query for each post, the eager loading method fetches all posts and their authors in a single join query!
We can specify multiple association names in the with() method. For example, the following code will retrieve posts along with their authors and categories:
$posts=Post::model()->with('author','categories')->findAll();
We can also use nested eager loading. Instead of using a list of association names, we pass the association names to the with() method in a hierarchical manner, as follows,
$posts=Post::model()->with(
'author.profile',
'author.posts',
'categories')->findAll();
The above code will retrieve all posts along with their authors and categories. It will also pull out each author’s profile and posts.
Eager loading can also be performed by specifying the CDbCriteria::with attribute, as follows:
$criteria=new CDbCriteria;
$criteria->with=array(
'author.profile',
'author.posts',
'categories',
);
$posts=Post::model()->findAll($criteria);
or
$posts=Post::model()->findAll(array(
'with'=>array(
'author.profile',
'author.posts',
'categories',
)
);
3. Related query options
Earlier we mentioned that additional parameters can be specified in the association declaration. These options, specified as name-value pairs, are used to customize the correlation query. They are outlined below:
select: List of fields queried for the associated AR class. The default is '*', meaning all fields. Query field names can be disambiguated using alias expressions (for example: COUNT(??.name) AS nameCount).
condition: WHERE sub-statement. Default is empty. Note that columns should be referenced using aliases (for example: ??.id=10).
params: Parameters bound to the SQL statement. Should be an array () consisting of name-value pairs.
on: ON substatement. The condition specified here will be appended to the join condition using the and operator. Field names in this option should be disambiguated. This option does not apply to MANY_MANY associations.
order: ORDER BY sub-statement. Default is empty. Note that columns should be referenced using aliases (for example: ??.age DESC).
with: A list of child associated objects that should be loaded together with this object. Note that improper use may form an infinite association loop.
joinType: The join type of this association. The default is LEFT OUTER JOIN.
aliasToken: Column prefix placeholder. The default is "??.".
alias: alias of the associated data table. The default is null, which means that the table alias and the association name are the same.
together: Whether the associated data table is forced to be connected with the main table and other tables. This option is only meaningful for HAS_MANY and MANY_MANY associations. If this option is set to false, ...(the original text is wrong here!). The default is empty. Field names in this option are disambiguated.
having: HAVING sub-statement. The default is empty. Note that columns are referenced using aliases.
index: The returned array index type. Determines whether the returned array is a keyword-indexed array or a numeric-indexed array. Without setting this option, the array will be indexed numerically. This option only makes sense for HAS_MANY and MANY_MANY
In addition, the following options are available in lazy loading for specific associations:
group: GROUP BY clause. Default is empty. Note that columns should be referenced using aliases (for example: ??.age). This option applies only to HAS_MANY and MANY_MANY associations.
having: HAVING clause. Default is empty. Note that columns should be referenced using aliases (for example: ??.age). This option applies only to HAS_MANY and MANY_MANY associations.
limit: Limit the number of rows queried. This option cannot be used for BELONGS_TO associations.
offset: offset. This option cannot be used for BELONGS_TO associations.
Next we change the posts association declaration in User by using some of the options above:
class User extends CActiveRecord
{
public function relations()
{
return array(
'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
'order'=>'posts.create_time DESC',
'with'=>'categories'),
'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
);
}
}
Now if we access $author->posts, we will get the user's posts sorted in descending order by publication time. Each post instance is also loaded with its category.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
