Detailed description of YII Association query

This article mainly introduces related information about YII Association query. For more information, see I. configuration of multi-table Association

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 (of): 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): 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 (with one): This is A special case of has_detail. A can have at most one B (for example, A User can have at most one Profile );

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_MANY and self: MANY_MANY; 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 ).

Several points to be clarified:

(1) What does VarName mean? See Example 2 below.

(2), RelationType. There are four types, respectively

Self: has_to, self: BELONGS_TO, self: MANY_MANY, self: HAS_ONE.

(3), ClassName. That is, another./model/class name associated with. php.

(4), ForeignKey. Who is the foreign key?

(5) additional conditions

ER digoal

Example 1: relationship between one-to-many and multiple-to-one (relationship between post and user)

1) models/Post. php

class Post extends CActiveRecord { ...... public function relations() { return array( 'author'=>array(self::BELONGS_TO, 'User', 'author_id'), ); } } 

The relationship between Post and User is the BELONGS_TO (many-to-one) relationship, and is associated with the User through the Post author_id.

In Post, the author_id is a foreign key and is associated with the User.

Note: The VarName here is author, an object.

(2) models/User. php

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'), ); } }

For users, the relationship with Post is HAS_MANY (one-to-many. And associate it with Post by using the Post author_id.

Example 2: Multi-to-multi-relationship

In FailParts. php

'Users' => array(self::MANY_MANY, 'User', 'fail_parts_user(fail_parts_id, user_id)'), 

In User. php

'FailParts' => array(self::MANY_MANY, 'FailParts', 'fail_parts_user(user_id, fail_parts_id)'), 

Because the two are many-to-many relationships, you must use Users instead of User; use FailParts instead of FailPart.

Here, Users and FailParts are the previous VarName.

Example 3: one-to-one relationship

Relatively simple.

2. about VarName.

For Class A. php, 'varname' => array ('relationtype', 'B', 'foreignkey',... additional options)
Here, VarName and B are basically the same. But not necessarily the same. In this case, you can use VarName in views/A/xx. php of A to access B and its attribute values.

For One to One: A-> VarName
For multiple-to-one: author_name = $ post-> Author-> name;
If it is one-to-many: $ posts = $ author-> Post;
If it is multi-to-many: $ posts = $ author-> Post; // In essence, it is split into one-to-many and multiple-to-one

foreach($posts as $u){ $_tmp_titles[] = $u -> title; } titleStr = implode(', ', $_tmp_titles); 

II. use of multi-table Association

Often in controllers

1. Delayed loading

(1) many-to-one

$ Post = Post: model ()-> findByPk (10 );
$ Author = $ post-> author;

Annotation: this is essentially one-to-one.

(2) one-to-many

$ User = User: model ()-> findByPk (10 );
$ Posts = $ user-> posts;

(3) many-to-many

Note: Two IDs have a sequential relationship.

From the perspective of $ repairInfo, the association must be

'FailParts' => array(self::MANY_MANY, 'FailParts', 'repair_mapping(repair_info_id,fail_parts_id)'), 

From the perspective of $ failParts instances, the association relationship changes

'RepairInfos' => array(self::MANY_MANY, 'RepairInfo', 'repair_mapping(fail_parts_id, repair_info_id)'), 

As mentioned above, you do not need to configure both sides. you only need to set one side.

Previously used stupid methods:

/* Method 1: Use the table relationship (many-to-many) */$ fails = $ repairInfo-> FailParts; // use it in $ repairInfo/* Method 2: use the original method */$ id = $ repairInfo-> id; $ maps = RepairMapping: model ()-> findAll ("repair_info_id = $ id "); $ f_ids = array (); foreach ($ maps as $ map) {array_push ($ f_ids, $ maps [0]-> fail_parts_id);} $ f_idsStr = implode (', ', $ f_ids); $ fails = FailParts: model ()-> findAll ("id IN ($ f_idsStr )");

2. actively load --

(1) one-to-many
(2) many-to-many

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

Example:

User. php

// Query the functions of all users in the data center $ idc_id getAdminedUsersByIdc ($ idc_id) {$ c = new CDbCriteria (); $ c-> join = "JOIN idc_user on t. id = idc_user.user_id "; $ c-> condition =" idc_user.idc_id = $ idc_id "; return User: model ()-> with ('idcs ') -> findAll ($ c);} // Configure 'idcs '=> array (self: many_region, 'idcs', 'idc _ user (user_id, idc_id) in the rule) '),

Annotation: with ('idcs ') is absent, and the execution result is the same. But it is no longer the eager loading.

3. parameter-based association configuration

Common conditions are:

1. condition adds filter conditions based on a field in a table

For example:

// Defined in the User model, the following Association relation 'doingoutsources '=> array (self: many_sources, 'outsource', 'outsource _ user (user_id, outsource_id )', 'condition' => "doingOutsources. status_id IN (". status: ASSIGNED. ",". status: STARTED. ",". status: REJECTED. ")"),

// Conclusion: condition is a field of the model specified in array.

Obviously, doingOutsources is the alias of the real data table Outsource. Therefore, doingOutsources. status_id can be used in condition, and Outsource. status_id can also be used. In addition, the default alias of the table name user is t.

2. order is in ascending or descending order of a field in a table.

// Defined in the RepairInfo model, the following Association relation 'worksheet '=> array (self: has_sheet, 'worksheet', 'repair _ info_id ', order => 'created _ at desc'), // call $ worksheets = $ repair_info-> WorkSheet; // $ worksheets is sorted in descending order.

// Conclusion: order is a field of the model specified in array.

With
JoinType
Select
Params
On
Alias
Together
Group
Having
Index

Also used for lazy loading
Only 5 or 10 limit instances are allowed.
Offset
Through
Official Manual
'Posts' => array (self: has_attributes, 'post', 'author _ id', 'order' => 'posts. create_time DESC ',' with '=> 'Category '),

4. static query (for has_tables and many_tables only)

Keyword: self: STAT

1. basic usage. For example,

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)'); ); } } 

2. static queries also support the preceding conditional queries
For example

'doingOutsourceCount' => array(self::STAT, 'Outsource', 'outsource_user(user_id, outsource_id)', 'condition' => "outsource.status_id IN(" . Status::ASSIGNED . "," . Status::STARTED ."," . Status::REJECTED .")"), 

Other queries include

Many conditions are used.

Order
Select
DefaultValue
Params
Group
Having

3. static query loading method

You can use the lazy loading method.
$ Post-> commentCount.
You can also use the eager loading method.
$ Posts = Post: model ()-> with ('commentcount', 'categorycount')-> findAll ();
Note that the string in with must be an alias.

Performance comparison:

If you want to retrieve all comments of all the posts, the former requires 2N + 1 queries, while the latter only has one query. The selection of the two depends on the situation.