About the analysis of YII related queries

This article mainly introduces the relevant information of YII correlation query. Friends who need it can refer to it

1. Configuration of multi-table correlation

Before we use AR to perform related queries, we need to let AR know how one AR class is related to another.

The relationship between two AR classes is directly related through the relationship between the data tables represented by the AR classes. From a database perspective, there are three types of relationships between tables A and B: one-to-many (such as tbl_user and tbl_post), one-to-one (such as tbl_user and tbl_profile) and many-to-many Many (many-to-many such as tbl_category and tbl_post). In AR, there are four relationships:

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

HAS_MANY (there are multiple): If the relationship between tables A and B is one-to-many, then A has multiple Bs (for example, User has multiple Posts);

HAS_ONE (there is one) : This is a special case of HAS_MANY. A can have at most one B (for example, User can have at most one Profile);

MANY_MANY: This corresponds to the many-to-many relationship in the database. Since most DBMS do not directly support many-to-many relationships, a relationship table is needed to split the many-to-many relationship into a one-to-many relationship. In our example data structure, tbl_post_category is used for this purpose. In AR terms, we can interpret MANY_MANY as the combination of BELONGS_TO and HAS_MANY. For example, Post belongs to many (belongs to many) Category, and Category has many (has many). The relationship defined in Post.

AR needs to override the relations() method in CActiveRecord. This method returns an array of relationship configurations. Each array element represents a single relationship in the following format.

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

VarName is the name of the relationship; RelationType specifies the relationship type, which 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 relationship.

Some points that need to be clarified:

(1), what does VarName refer to? See Example 2 below for details.

(2), RelationType. There are 4 types in total, namely

self::HAS_MANY, self::BELONGS_TO, self::MANY_MANY, self::HAS_ONE.

(3), ClassName. That is, another associated ../model/classname.php.

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

(5), additional conditions

ER Diagram

Example 1, one-to-many and many-to-one 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 one) relationship, and is associated with User through Post's author_id.

The author_id in Post is a foreign key and is associated with User.

Note: 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 with Post belongs to HAS_MANY ( one-to-many) relationship. And associated with Post through Post's author_id.

Example 2, many-to-many 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)'),

Since the two are in a many-to-many relationship, Users must be used instead of User; FailParts must be used , instead of FailPart.

The Users and FailParts here are the previous VarName.

Example 3, one-to-one relationship

is relatively simple and will be omitted for now.

2, about VarName.

For class A.php, 'VarName'=>array('RelationType', 'B', 'ForeignKey', ...additional options)
VarName is basically the same as B. But not necessarily exactly the same. At this time, you can access B and its attribute values ​​​​through VarName in A's views/A/xx.php.

If it is one-to-one: A->VarName
If it is many-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;//The essence is to split it into one-to-many and many-to-one

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

2. The use of multi-table associations

Often in controllers

1, delay Loading

(1) Many to one

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

Comment: The essence here is one to one.

(2) One-to-many

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

(3) Many-to-many

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

From the perspective of the $repairInfo instance, the association must be

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

But from the perspective of the $failParts instance, the association becomes For

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

As has been pointed out before, there is no need to configure both sides, only the required side can be set.

Stupid method that has been used before:

/*方法一：使用表关系（多对多）*/ 
$fails = $repairInfo->FailParts;//在$repairInfo中使用 
/*方法二：使用原始方法*/ 
$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 loading——with

(1) One-to-many

(2) Many-to-many

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

例子：

User.php

//查询一个机房$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); 
} 
//规则中配置 
'Idcs' => array(self::MANY_MANY, 'Idc', 'idc_user(user_id, idc_id)'),

批注：没有with('Idcs')，执行后的结果也一样。只不过不再是eager loading。

三、带参数的关联配置

常见的条件有

1，condition 按某个表的某个字段加过滤条件

例如：

//在User的model里定义，如下关联关系 
'doingOutsources' => array(self::MANY_MANY, 'Outsource', 'outsource_user(user_id, outsource_id)', 
'condition' => "doingOutsources.status_id IN(" . Status::ASSIGNED . "," . Status::STARTED ."," . Status::REJECTED .")"),

//结论：condition是array里指定model的一个字段。

显然，doingOutsources是真实数据表Outsource的别名，所以在condition中可以使用doingOutsources.status_id，当然也可以使用Outsource.status_id。另本表名user的默认别名是t。

2，order 按某个表的某个字段升序或降序

//在RepairInfo的model里定义，如下关联关系 
'WorkSheet' => array(self::HAS_MANY, 'WorkSheet', 'repair_info_id', order => 'created_at desc'), 
//调用 
$worksheets = $repair_info->WorkSheet; //此时$worksheets是按降序排列

//结论：order是array里指定model的一个字段。

with
joinType
select
params
on
alias
together
group
having
index

还有用于lazy loading的
limit 只取5个或10个
offset
through
官方手册
'posts'=>array(self::HAS_MANY， 'post', 'author_id', 'order'=>'posts.create_time DESC', 'with'=>'categories'),

四、静态查询（仅用于HAS_MANY和MANY_MANY）

关键字：self:STAT

1，基本用法。例如，

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，静态查询也支持上面的各种条件查询
如

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

其他查询还包括

condition 使用较多

order
select
defaultValue
params
group
having

3，静态查询的加载方式

可以使用lazy loading方式
$post->commentCount.
也可以使用eager loading方式
$posts = Post::model()->with('commentCount','categoryCount')->findAll();
注with中字符串一定是别名。

两者的性能比较：

如果需要取所有post的所有comment，前者需要2N+1次查询，而后者只有一次。两者的选择视情况而定。

以上就是本文的全部内容，希望对大家的学习有所帮助，更多相关内容请关注PHP中文网！

相关推荐：

Yii中表单用法实例

Yii2如何实现同时搜索多个字段

如何实现Yii2框架中使用PHPExcel导出Excel文件

The above is the detailed content of About the analysis of YII related queries. For more information, please follow other related articles on the PHP Chinese website!