Yii Framework official guide series 26-database use: Relational ActiveRecord

We have learned how to use ActiveRecord (AR) to obtain data from a single data table. This section describes how to use AR to connect multiple related data tables and retrieve the joined dataset. To use the link...

We have learned how to use Active Record (AR) to obtain data from a single data table. This section describes how to use AR to connect multiple related data tables and retrieve the joined dataset.

To use relational AR, we recommend that you define the primary key-foreign key constraint in the table to be associated. These constraints can help ensure the consistency and integrity of related data.

For simplicity, we use the data structure in the object-relationship (ER) diagram shown below to demonstrate the example in this section.

Information: external key constraints are supported differently in different DBMS. SQLite <3.6.19 does not support foreign key constraints, but you can still declare constraints when creating tables.

1. declare a link

Before using AR to perform join queries, we need to let AR know how an AR class is associated with another one.

The relationship between two AR classes is directly related to the relationship between the data tables represented by the AR class. From the database perspective, there are three relationships between table A and Table B: one-to-many (one-to-many, such as tbl_user and tbl_post ), one-to-one (one-to-one such as tbl_user and tbl_profile) and multiple-to-many (such as tbl_category and tbl_post ). There are four relationships in AR:

BELONGS_TO (): if the relationship between table A and Table B is one-to-many, Table B belongs to table A (for example, Post belongs to User); HAS_MANY (multiple tables exist ): if the relationship between table A and Table B is one-to-many, table A has multiple B (for example, User has multiple posts); HAS_ONE (one): This is A special case of HAS_MANY, A can have at most one B (for example, A User can have at most one Profile); MANY_MANY: this corresponds to the many-to-many relationship in the database. Because most DBMS does not directly support multiple-to-multiple relationships, an association table is required to split multiple-to-multiple relationships into one-to-multiple relationships. In our sample data structure, tbl_post_category is used for this purpose. In AR terminology, many_to is a combination of BELONGS_TO and has_to. For example, a Post belongs to multiple (belongs to publish) Category, and a Category has multiple (has Publish) Post.

The relations () method in CActiveRecord must be overwritten to define the link in AR. This method returns an array of link configurations. Each array element represents a single relationship in the following format.

'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)

VarName indicates the name of the link. RelationType indicates the Link type. it can be one of the following four constants:Self: BELONGS_TO, self: HAS_ONE, self: has_tables and self: many_tables; ClassName is the name of the AR class associated with this AR class; ForeignKey specifies the foreign key (one or more) used in the link ). Additional options can be specified at the end of each link (detailed later ).

The following code defines the relationship between the User and the Post class:

class Post extends CActiveRecord{    ......    public function relations()    {        return array(            'author'=>array(self::BELONGS_TO, 'User', 'author_id'),            'categories'=>array(self::MANY_MANY, 'Category',            'tbl_post_category(post_id, category_id)'),        );    }}class User extends CActiveRecord{    ......    public function relations()    {        return array(            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),        );    }}

Information: foreign keys may be composite and contain two or more columns. In this case, we should separate these foreign key names with spaces or commas. For the MANY_MANY relational type, the name of the joined table must also be specified in the foreign key. For example, the categories relationship in Post is specified by the foreign key tbl_post_category (post_id, category_id.

The link definition in the AR class is that each link implicitly adds an attribute to the class. After an associated query is executed, the corresponding attributes will be filled with the associated AR instance. For example, if $ author represents a User AR instance, we can use $ author-> posts to access its associated Post instance.

2. execute Association query

The simplest way to execute an association query is to read the association attributes in an AR instance. If this attribute has not been accessed before, an association query is initialized. it associates two tables and uses the primary key of the current AR instance to filter. The query result is saved to the attribute as an associated AR class instance. This is the legendary lazy loading method. for example, the association query is executed only when the associated object is accessed for the first time. The following example demonstrates how to use this method:

// Get the post with ID 10 $ Post = post: model ()-> findByPk (10); // get the Post author (author ): an association query is executed here. $ Author = $ post-> author;

Information: if no related instance exists in the link, the corresponding attribute is null or an empty array. The result of the relationship between BELONGS_TO and HAS_ONE is null, and the result of has_detail and many_detail is an empty array. Note: The HAS_MANY and MANY_MANY relationships return an array of objects. you need to traverse these results before accessing any attributes. Otherwise, you may receive the "Trying to get property of non-object (attempting to access non-object attributes)" error.

Lazy loading is convenient to use, but it is not efficient in some cases. If we want to obtain the authors of N posts, using this lazy load will lead to the execution of N join queries. In this case, we should use the eager loading method instead.

The eager loading method obtains the associated AR instance while obtaining the primary AR instance. This is done by using the with method when using the find or findAll method in AR. For example:

$posts=Post::model()->with('author')->findAll();

The above code returns an array of Post instances. Unlike the lazy loading method, it is populated by the associated User instance before we access the author attribute in each Post instance. Eager loading returns all the posts and their authors through an association query, instead of executing an association query for each post.

We can specify multiple link names in the with () method, and the eager loading will retrieve them all at once. For example, the following code retrieves a post along with its author and category.

$posts=Post::model()->with('author','categories')->findAll();

We can also implement nested eager loading. As shown below, we pass a hierarchical relational name expression to the with () method, instead of a list of relational names:

$posts=Post::model()->with(    'author.profile',    'author.posts',    'categories')->findAll();

The preceding example retrieves all posts, their authors, and their categories. It also retrieves the introduction (author. profile) and Post (author. posts) of each author ).

Starting from version 1.1.0, you can specify the CDbCriteria: with attribute to perform eager loading, as shown below:

$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. relational query options

We mentioned that you can specify additional options when declaring a link. These options are used to customize relational queries. Summarized as follows:

Select: List of columns to select (select) in the associated AR class. The default value is '*', that is, all columns are selected. The column name in this option should be clear. Condition: WHERE condition. The default value is null. The column name in this option should be clear. Params: the parameter to bind to the generated SQL statement. The value should be assigned as an array by name-value. This option is valid from version 1.0.3. On: The ON statement. The conditions specified here will be appended to the join condition through the AND operator. The column name in this option should be clear. This option is not applied to the MANY_MANY relation. This option is valid from version 1.0.2. Order: the order by statement. The default value is null. The column name in this option should be clear. With: some column-related objects that should be loaded with this object. note that improper use of this option may result in infinite link loops. joinType: join type of the link. the default value is left outer join. alias: alias of the table associated with the link. this option is valid from yii version 1.0.1. the default value is null, indicating that the table alias and link name are the same. together: whether the table associated with the link should be forcibly connected to the master table and other tables. this option only makes sense for the relationship HAS_MANY and MANY_MANY. if this option is set to false, the table associated with the has_tables or many_tables relationship is connected to the master table in an isolated SQL query, which improves the overall query performance, because this will return a small amount of duplicate data. if this option is set to true, the associated table is always connected to the primary table in an SQL query, even if the primary table is paginated. if this option is not set, the associated table is connected to the primary table in an SQL query only when the primary table is not a page break. for more details, see "link query performance ". this option is supported from version 1.0.3. join: additional JOIN clause. the default value is null. this option is supported from version 1.1.3. group: group by clause. the default value is null. the use of column names in this option should be unambiguous. having: HAVING clause. the default value is null. the use of column names in this option should be unambiguous. note: This option is supported from version 1.0.1. index: The column name is used to store the key value of the relational object array. if this option is not set for the table, the relational object array uses an integer index starting from 0. this option can only be used to set the relationship types of HAS_MANY and MANY_MANY. yii Framework supports this option after version 1.0.7

In addition, the following options are valid for specific relationships in lazy loading:

Limit: The number of rows to be queried. this option cannot be used for the BELONGS_TO link. offset: The start row to be queried. this option cannot be used for the BELONGS_TO link.

Below we add some of the above options to modify the posts link declaration in the User:

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 you access $ author-> posts, you will get the author's posts sorted based on the creation time, and each post instance will load its category.

4. column name ambiguity

When two or more tables are connected to the same column name, you need to exclude ambiguity. this can be achieved by adding the table alias prefix to the column name.

In the link AR query, the primary table alias is t by default, and the link table alias is the corresponding link name by default. for example, in the following statement, the aliases of Post and Comment are t and comments:

$posts=Post::model()->with('comments')->findAll();

Now, assume that both Post and Comment have a column indicating the creation time, and we want to put posts and its corresponding comments together for query. the sorting method is first the posts creation time, the creation time of comments. We need to remove column name ambiguity as follows:

$posts=Post::model()->with('comments')->findAll(array(    'order'=>'t.create_time, comments.create_time'));

Note: column ambiguity changes from version 1.1.0. in version 1.0.x, Yii automatically generates table aliases for each relational table by default, and we must use the prefix ??. To reference the automatically generated alias. In addition, in version 1.0.x, the primary table alias is the table name.

5. Dynamic Link query options

From version 1.0.2, we can use the dynamic link query option in the with () and with options. dynamic options overwrite the options specified in the existing relations () method. for example, in the above User model, if we want to use the eager loading method to add posts to each author in ascending order (the default order in the link definition is descending), we can follow the following method:

User::model()->with(array(    'posts'=>array('order'=>'posts.create_time ASC'),    'profile',))->findAll();

Starting from version 1.0.5, the dynamic query option can be used for link query in lazy loading mode. we can call a method with the same link name and pass in the dynamic query option as the parameter. for example, the following code returns the user's posts whose status is 1:

$user=User::model()->findByPk(1);$posts=$user->posts(array('condition'=>'status=1'));

6. link query performance

As we described above, the eager loading approach is mainly used in the scenario when we need to access your related objects. it generates a big complex SQL statement by joining all needed tables. A big SQL statement is preferrable in your cases since it simplifies filtering based on a column in a related table. it may not be efficient in some cases, however.

Consider an example where we need to find the latest posts together with their comments. assuming each post has 10 comments, using a single big SQL statement, we will bring back a lot of redundant post data since each post will be repeated for every comment it has. now let's try another approach: we first query for the latest posts, and then query for their comments. in this new approach, we need to execute two SQL statements. the benefit is that there is no redundancy in the query results.

So which approach is more efficient? There is no absolute answer. executing a single big SQL statement may be more efficient because it causes less overhead in DBMS for yparsing and executing the SQL statements. on the other hand, using the single SQL statement, we end up with more redundant data and thus need more time to read and process them.

For this reason, Yii provides the together query option so that we choose between the two approaches as needed. by default, Yii adopts the first approach, I. e ., generating a single SQL statement to perform eager loading. we can set the together option to be false in the relation declarations so that some of the tables are joined in separate SQL statements. for example, in order to use the second approach to query for the latest posts with their comments, we can declare the comments relation in Post class as follows,

public function relations(){    return array(        'comments' => array(self::HAS_MANY, 'Comment', 'post_id', 'together'=>false),    );}

We can also dynamically set this option when we perform the eager loading:

$posts = Post::model()->with(array('comments'=>array('together'=>false)))->findAll();

Note: In version 1.0.x, the default behavior is that Yii will generate and execute N + 1 SQL statements if there are N has_tables or MANY_MANY relations. each has_tables or MANY_MANY relation has its own SQL statement. by calling the together () method after with (), we can enforce only a single SQL statement is generated and executed. for example,

$posts=Post::model()->with(   'author.profile',    'author.posts',    'categories')->together()->findAll();

7. Statistics Query

Note: Statistical query has been supported since version 1.0.4.

Besides the relational query described above, Yii also supports the so-called statistical query (or aggregational query ). it refers to retrieving the aggregational information about the related objects, such as the number of comments for each post, the average rating for each product, etc. statistical query can only be stored Med for objects related in HAS_MANY (e.g. a post has release comments) or many_release (e.g. a post belongs to your categories and a category has your posts ).

Wide Ming statistical query is very similar to wide Ming relation query as we described before. We first need to declare the statistical query in the relations () method of CActiveRecord like we do with relational query.

class Post extends CActiveRecord{    public function relations()    {        return array(            'commentCount'=>array(self::STAT, 'Comment', 'post_id'),            'categoryCount'=>array(self::STAT, 'Category', 'post_category(post_id, category_id)'),        );    }}

In the above, we declare two statistical queries: commentCount calculates the number of comments belonging to a post, and categoryCount calculates the number of categories that a post belongs. note that the relationship between Post and Comment is has_detail, while the relationship between Post and Category is many_detail (with the joining table post_category ). as we can see, the declaration is very similar to those relations we described in earlier subsections. the only difference is that the relation type is STAT here.

With the above declaration, we can retrieve the number of comments for a post using the expression $ post-> commentCount. when we access this property for the first time, a SQL statement will be executed implicitly to retrieve the corresponding result. as we already know, this is the so-called lazy loading approach. we can also use the eager loading approach if we need to determine the comment count for multiple posts:

$posts=Post::model()->with('commentCount', 'categoryCount')->findAll();

The above statement will execute three SQLs to bring back all posts together with their comment counts and category counts. using the lazy loading approach, we wocould end up with 2 * N + 1 SQL queries if there are N posts.

By default, a statistical query will calculate the COUNT expression (and thus the comment count and category count in the above example ). we can customize it by specifying additional options when we declare it in relations (). the available options are summarized as below.

select: the statistical expression. Defaults to COUNT(*), meaning the count of child objects.defaultValue: the value to be assigned to those records that do not receive a statistical query result. For example, if a post does not have any comments, its commentCount would receive this value. The default value for this option is 0.condition: the WHERE clause. It defaults to empty.params: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs.order: the ORDER BY clause. It defaults to empty.group: the GROUP BY clause. It defaults to empty.having: the HAVING clause. It defaults to empty.

8. use the name range for link query

Note: The support for named scopes has been available since version 1.0.5.

Relational query can also be saved Med in combination with named scopes. it comes in two forms. in the first form, named scopes are applied to the main model. in the second form, named scopes are applied to the related models.

The following code shows how to apply named scopes to the main model.

$posts=Post::model()->published()->recently()->with('comments')->findAll();

This is very similar to non-relational queries. the only difference is that we have the with () call after the named-scope chain. this query wocould bring back recently published posts together with their comments.

And the following code shows how to apply named scopes to the related models.

$posts=Post::model()->with('comments:recently:approved')->findAll();

The above query will bring back all posts together with their approved comments. note that comments refers to the relation name, while recently and approved refer to two named scopes declared in the Comment model class. the relation name and the named scopes shocould be separated by colons.

Named scopes can also be specified in the with option of the relational rules declared in CActiveRecord: relations (). in the following example, if we access $ user-> posts, it wocould bring back all approved comments of the posts.

class User extends CActiveRecord{    public function relations()    {        return array(           'posts'=>array(self::HAS_MANY, 'Post', 'author_id',           'with'=>'comments:approved'),        );    }}

Note: Named scopes applied to related models must be specified in CActiveRecord: scopes. As a result, they cannot be parameterized.

The above is the Yii Framework official guide series 26-use the database: Relational Active Record content. For more information, please follow the PHP Chinese network (www.php1.cn )!