On the analysis of Yii Association query

This article mainly introduces the related data of Yii Association query, the need of friends can refer to the following

Configuration of multiple table associations

Before we perform an associative query using AR, we need to let ar know how an AR class relates to another.

The relationship between the two AR classes is directly associated with the relationship between the data tables represented by the AR class. From a database point of view, there are three relationships between tables A and B: one-to-many (one-to-many, such as Tbl_user and Tbl_post), a-to-one (one-to-one such as Tbl_user and Tbl_profile) and many-to-many (many-to- Many such as Tbl_category and Tbl_post). In AR, there are four kinds of relationships:

Belongs_to (TO): if the relationship between tables A and B is one-to-many, table B belongs to table A (for example, Post belongs to User);

Has_many (with multiple): if the relationship between tables A and B is one-to-many, then A has more than B (for example, User has multiple Post);

Has_one (There is one): This is a special case of Has_many, A has a maximum of a B (for example, User has a maximum profile);

Many_many: This corresponds to a many-to-many relationship in the database. Since most DBMS do not directly support many-to-many relationships, it is necessary to have an associated table that splits a many-to-many relationship into a one-to-many relationship. In our sample data structure, Tbl_post_category is used for this purpose. In AR terminology, we can explain many_many as a combination of belongs_to and has_many. For example, the post belongs to multiple (belongs to many) category, and the category has multiple (has many) post.

The relationship defined in AR needs to overwrite the relations () method in Cactiverecord. This method returns an array of relational configurations. Each array element represents a single relationship through the following format.

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

Where VarName is the name of the relationship; Relationtype specifies a relationship type, which can be one of 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 keys (one or more) used in the relationship.

A few things to figure out:

(1), what does varname mean? See Example 2 below.

(2), Relationtype. A total of 4 species, respectively,

Self::has_many, Self::belongs_to, Self::many_many, Self::has_one.

(3), ClassName. That is, the associated other: /model/class name. php.

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

(5), additional conditions

ER Diagram

Example 1, one-to-many vs. multi-pair relationship (the 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 belongs_to (many-to-one) and is associated with the user via Post's author_id.

The author_id in post is a foreign key, which is associated to 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, ' P Ost ', ' author_id '), ' profile ' =>array (self::has_one, ' profile ', ' owner_id '),); } }

For user, the relationship to post is a has_many (one-to-many) relationship. and is associated with the post via the author_id of the post.

Example 2, many-to-many relationships

In the failparts.php

' Users ' = = Array (self::many_many, ' User ', ' Fail_parts_user (fail_parts_id, user_id) '),

In the user.php

' failparts ' = = Array (self::many_many, ' failparts ', ' Fail_parts_user (user_id, fail_parts_id) '),

Since the two are many-to-many relationships, use the users instead of the user; Use Failparts instead of Failpart.

Here are the users and Failparts, which is the previous varname.

Example 3, one-to-one relationship

Relatively simple, temporary.

2, about varname.

For class a.php, ' VarName ' =>array (' relationtype ', ' B ', ' ForeignKey ', ... additional options)
Where VarName is basically the same as B. But it may not be exactly the same. You can now access B and its property values through VarName in the views/a/xx.php of a.

If it's a one-to: A->varname
If it is many-to-one: Author_name = $post->author->name;
If it is a one-to-many: $posts = $author->post;
If it is many-to-many: $posts = $author->post;//essence is split into one-to-many and many-to-a

foreach ($posts as $u) {$_tmp_titles[] = title $u,} titlestr = Implode (', ', $_tmp_titles);

Second, the use of Multi-table Association

Often in the controllers.

1, Delay loading

(1) Many-to-one

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

Annotations: The essence here is one-on.

(2) A pair of multiple

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

(3) Many-to-many

It is important to note that two IDs have a succession relationship.

At the point of view of the $repairinfo instance, the association relationship must be

' failparts ' = = Array (self::many_many, ' failparts ', ' repair_mapping (repair_info_id,fail_parts_id) '),

While standing at the angle of the $failparts instance, the association relationship becomes

' Repairinfos ' = = Array (self::many_many, ' repairinfo ', ' repair_mapping (fail_parts_id, repair_info_id) '),

As already pointed out earlier, do not need to be configured on both sides, only need to set the party.

A stupid method you've used before:

/* Method One: Use table relationships (many-to-many)/$fails = $repairInfo->failparts;//Use/* method in $repairinfo: 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, Active load--with

(1) A pair of multiple
(2) Many-to-many

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

Example:

user.php

Query a room $idc_id all user 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); }//Rule configuration ' Idcs ' = = Array (self::many_many, ' Idc ', ' Idc_user (user_id, idc_id) '),

Annotations: Without with (' Idcs '), the result is the same after execution. But it is no longer eager loading.

Three, with parameters of the associated configuration

The common conditions are

1,condition Filter by a field in a table

For example:

Defined in the user's model, the following correlation relationship ' doingoutsources ' = = Array (self::many_many, ' outsource ', ' Outsource_user (user_id, outsource_id) ', ' condition ' = ' doingoutsources.status_id in ('. status::assigned. "," . status::started. ",". status::rejected. ")"),

Conclusion: Condition is a field in the specified model in the array.

Obviously, Doingoutsources is the alias of the real data table outsource, so doingoutsources.status_id can be used in condition, and outsource.status_id can be used, of course. Another default alias for the table name user is T.

2,order by a word in a table orderby or descending order

Defined in the Repairinfo model, the following relationship ' WorkSheet ' = = Array (self::has_many, ' WorkSheet ', ' repair_info_id ', order = ') Created_at desc '),//Call $worksheets = $repair _info->worksheet; At this point the $worksheets is sorted in descending order

Conclusion: Order is a field in the specified model in the array.

With
JoinType
Select
Params
On
Alias
Together
Group
Having
Index

And for the lazy loading.
Limit only takes 5 or 10 of
Offset
Through
Official Handbook
' Posts ' =>array (Self::has_many, ' post ', ' author_id ', ' order ' = ' posts.create_time DESC ', ' with ' = ') Categories '),

Iv. static queries (only for Has_many and Many_many)

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 query also supports the above various conditions query
Such as

' Doingoutsourcecount ' = = Array (self::stat, ' outsource ', ' Outsource_user (user_id, outsource_id) ', ' condition ' = = "Outsource.status_id in (". status::assigned. "," . status::started. ",". status::rejected. ")"),

Other queries also include

Condition use more

Order
Select
DefaultValue
Params
Group
Having

3, how static queries are loaded

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 with the string must be an alias.

Performance comparison between the two:

If you need to take all the comment of the post, the former needs to 2n+1 the query, the latter only one time. The choice of both depends on the situation.

The above is the whole content of this article, I hope that everyone's learning has helped, more relevant content please pay attention to topic.alibabacloud.com!