A detailed example of Yii Association query _php

One, multiple table associated configuration

Before we use AR to execute the associated query, we need to let ar know how an AR class is associated to another.

The relationship between the two AR classes is directly related to the relationship between the data tables represented by the AR class. From a database perspective, there are three relationships between tables A and 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 Many-to-many (many-to- Many such as Tbl_category and Tbl_post). In AR, there are four kinds of relationships:

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

Has_many (multiple): If the relationship between table A and B is One-to-many, then A has multiple B (for example, User has multiple Post);

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

Many_many: This corresponds to a many-to-many relationship in the database. Since most DBMS does not directly support many-to-many relationships, it is necessary to have a relational table that divides the many-to-many relationship into one-to-many relationships. 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, a post belongs to more than one (belongs to many) Category and Category has multiple (has many) post.

The definition of relationships in AR requires overwriting the relations () method in Cactiverecord. This method returns a relational configuration array. Each array element represents a single relationship through the following format.

Where VarName is the name of the relationship; Relationtype Specifies the 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 the foreign key (one or more) used in the specified 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 and many-to-many relationships (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-many) and is associated with the user through post author_id.

Author_id in post is a foreign key, which 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 user, the relationship to post is a has_many (one-to-many) relationship. and is associated with the post through the author_id of the post.

Case 2, Many-to-many relationship

In the failparts.php

In the user.php

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

The users and failparts here are the front 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 and B are basically the same. But not exactly the same. B and its property values can be accessed by varname in the views/a/xx.php of a.

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

foreach ($posts as $u) { 
$_tmp_titles[] = $u-> title; 
 

Use of multiple table associations

Often in the controllers.

1, Delay loading

(1) Multiple to one

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

Annotation: The essence of this is one-to-one.

(2) a One-to-many

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

(3) Many to many

Need to focus attention: Two IDs have a sequential relationship.

From the perspective of the $repairinfo instance, the associated relationship must be

While standing in the $failparts instance's perspective, the association relationship becomes

It has also been pointed out that there is no need for both sides to be configured, just need to set the party.

A stupid method used before:

/* Method One: Use table relationship (Many-to-many)/ 
$fails = $repairInfo->failparts;//in $repairinfo use/ 
* Method Two: 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); 

2, active loading--with

(1) A One-to-many
(2) Many to many

Example:

user.php

Query a computer 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); 
} 
Configuration in Rules 

Annotation: No with (' IDCs '), and the result is the same after execution. It's just not eager loading.

Three, with the parameter of the association configuration

The common conditions are

1,condition a field in a table by adding a filter condition

For example:

Defined in user model, the following relate to 
' 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 of the specified model in 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 as well. The default alias for another table name user is T.

2,order Che or descending by a word of a table

In the model of repairinfo, it is defined as the following correlation 
' worksheet ' => array (self::has_many, ' worksheet ', ' repair_info_id ', order => ' Created_at desc '), 
//Call 

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

With
JoinType
Select
Params
On
Alias
Together
Group
Having
Index

and for lazy loading.
Limit only take 5 or 10
Offset
Through
Official Handbook
' Posts ' =>array (Self::has_many, ' post ', ' author_id ', ' Order ' => ' posts.create_time DESC ', ' and ' => ' Categories '),

Iv. static queries (for Has_many and Many_many only)

Keywords: 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) ', 

Other queries also include

Condition use more

Order
Select
DefaultValue
Params
Group
Having

3, static query loading mode

You can use the lazy loading method
$post->commentcount.
You can also use the eager loading method
$posts = Post::model ()->with (' Commentcount ', ' Categorycount ')->findall ();
The string in the with must be an alias.

Performance comparisons for both:

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