Yii-database operations-Multi-Table Association

Directory
I. configuration of Multi-Table Association
Ii. Use of Multi-Table Association
3. Parameter-based association Configuration
Iv. Static query (query quantity)

 

I. configuration of Multi-Table Association
Note: first, multi-table join is configured in the relations of models/XX. php. It is also a mutual configuration, but there is a difference.

Format:
'Varname' => array ('relationtype', 'classname', 'foreignkey',... additional options)

Several points to be clarified:
(1) What does varname mean? See example 2 below.
(2), relationtype. There are four types: Self: has_detail, self: belongs_to, self: many_detail, self: has_one.

(3), classname. That is, another./model/class name associated with. php.
(4), foreignkey. Who is the foreign key?
(5) Additional Conditions

Important: There are four common relationtypes.
Has_one
Has_many
Belongs_to
Many_to_many

1. Basic Configuration

Example 1: one-to-many relationship (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'),'categories'=>array(self::MANY_MANY, 'Category', 'tbl_post_category(post_id, category_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_users, 'user', 'fail _ parts_user (fail_parts_id, user_id )'),

In User. php
'Failparts' => array (SELF: many_ts, '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 one to multiple: A-> varname-> name
If it is many to many: $ users = As-> varname

foreach($users as $u){ $_tmp_users[] = $u -> getName; } userStr = implode(', ', $_tmp_users); 

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; // used 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 all users of a data center $ idc_id

function 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 the rule

'Idcs' => array(self::MANY_MANY, 'Idc', 'idc_user(user_id, idc_id)'),

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
For example:

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

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.

Common conditions are:
Condition adds filter conditions based on a field in a table
Order is in ascending or descending order by a field in a table
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.