详解YII关联查询_PHP
一、多表关联的配置
在我们使用 AR 执行关联查询之前,我们需要让 AR 知道一个 AR 类是怎样关联到另一个的。
两个 AR 类之间的关系直接通过 AR 类所代表的数据表之间的关系相关联。 从数据库的角度来说,表 A 和 B 之间有三种关系:一对多(one-to-many,例如 tbl_user 和 tbl_post),一对一( one-to-one 例如 tbl_user 和 tbl_profile)和 多对多(many-to-many 例如 tbl_category 和 tbl_post)。 在 AR 中,有四种关系:
BELONGS_TO(属于): 如果表 A 和 B 之间的关系是一对多,则 表 B 属于 表 A (例如 Post 属于 User);
HAS_MANY(有多个): 如果表 A 和 B 之间的关系是一对多,则 A 有多个 B (例如 User 有多个 Post);
HAS_ONE(有一个): 这是 HAS_MANY 的一个特例,A 最多有一个 B (例如 User 最多有一个 Profile);
MANY_MANY: 这个对应于数据库中的 多对多 关系。 由于多数 DBMS 不直接支持 多对多 关系,因此需要有一个关联表将 多对多 关系分割为 一对多 关系。 在我们的示例数据结构中,tbl_post_category 就是用于此目的的。在 AR 术语中,我们可以解释MANY_MANY 为 BELONGS_TO 和 HAS_MANY 的组合。 例如,Post 属于多个(belongs to many) Category ,Category 有多个(has many) Post.
AR 中定义关系需要覆盖 CActiveRecord 中的 relations() 方法。此方法返回一个关系配置数组。每个数组元素通过如下格式表示一个单一的关系。
'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)
其中 VarName 是关系的名字;RelationType 指定关系类型,可以是一下四个常量之一: self::BELONGS_TO, self::HAS_ONE,self::HAS_MANY and self::MANY_MANY;ClassName 是此 AR 类所关联的 AR 类的名字; ForeignKey 指定关系中使用的外键(一个或多个)。
需要弄清楚的几点:
(1),VarName指什么? 详见下面例2。
(2),RelationType。一共有4种,分别为
self::HAS_MANY, self::BELONGS_TO, self::MANY_MANY, self::HAS_ONE。
(3),ClassName。即关联的另一个../model/类名.php。
(4),ForeignKey。谁是谁的外键?
(5),附加条件
ER Diagram
例1,一对多与多对一关系(post和user之间的关系)
1)models/Post.php
class Post extends CActiveRecord { ...... public function relations() { return array( 'author'=>array(self::BELONGS_TO, 'User', 'author_id'), ); } }
其中Post与User的关系是BELONGS_TO(多对一)关系,并通过Post的author_id与User关联。
Post中author_id是外键,关联到User中。
注:此处的VarName是author,一个对象。
(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'), ); } }
对于User,与Post的关系是属于HAS_MANY(一对多)关系。并通过Post的author_id与Post关联。
例2,多对多关系
在FailParts.php中
'Users' => array(self::MANY_MANY, 'User', 'fail_parts_user(fail_parts_id, user_id)'),
在User.php中
'FailParts' => array(self::MANY_MANY, 'FailParts', 'fail_parts_user(user_id, fail_parts_id)'),
由于两者是多对多关系,所以要用Users,而不是User;要用FailParts,而不是FailPart。
此处的Users和FailParts,即为前面的VarName。
例3,一对一关系
比较简单,暂略。
2,关于VarName。
对于类A.php,'VarName'=>array('RelationType', 'B', 'ForeignKey', ...additional options)
其中VarName与B基本相同。但未必完全一样。此时就可以在A的views/A/xx.php中通过VarName来访问B及其属性值了。
如果是一对一:A->VarName
如果是多对一:author_name = $post->Author->name;
如果是一对多:$posts = $author->Post;
如果是多对多:$posts = $author->Post;//本质是拆成一对多和多对一
foreach($posts as $u){ $_tmp_titles[] = $u -> title; } titleStr = implode(', ', $_tmp_titles);
二、多表关联的使用
常常在controllers里
1,延时加载
(1)多对一
$post = Post::model()->findByPk(10);
$author = $post->author;
批注:此处本质是一对一。
(2)一对多
$user = User::model()->findByPk(10);
$posts = $user->posts;
(3)多对多
需要重点注意:两个id有先后关系。
站在$repairInfo实例的角度,关联关系必须是
'FailParts' => array(self::MANY_MANY, 'FailParts', 'repair_mapping(repair_info_id,fail_parts_id)'),
而站在$failParts实例的角度,则关联关系变为
'RepairInfos' => array(self::MANY_MANY, 'RepairInfo', 'repair_mapping(fail_parts_id, repair_info_id)'),
而前面也已经指出,不需要双方都配置,只需需要的一方设置即可。
之前曾使用过的笨方法:
/*方法一:使用表关系(多对多)*/ $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,主动加载——with
(1)一对多
(2)多对多
$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次查询,而后者只有一次。两者的选择视情况而定。

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



Detailed explanation of MyBatis one-to-many query configuration: To solve common associated query problems, specific code examples are required. In actual development work, we often encounter situations where we need to query a master entity object and its associated multiple slave entity objects. In MyBatis, one-to-many query is a common database association query. With correct configuration, the query, display and operation of associated objects can be easily realized. This article will introduce the configuration method of one-to-many query in MyBatis, and how to solve some common related query problems. It will also

Multi-table related query skills in PHP Related queries are an important part of database queries, especially when you need to display data in multiple related database tables. In PHP applications, multi-table related queries are often used when using databases such as MySQL. The meaning of multi-table association is to compare data in one table with data in another or multiple tables, and connect those rows that meet the requirements in the result. When performing multi-table correlation queries, you need to consider the relationship between tables and use appropriate correlation methods. The following introduces several types of

With the continuous development of cloud computing technology, data backup has become something that every enterprise must do. In this context, it is particularly important to develop a highly available cloud backup system. The PHP framework Yii is a powerful framework that can help developers quickly build high-performance web applications. The following will introduce how to use the Yii framework to develop a highly available cloud backup system. Designing the database model In the Yii framework, the database model is a very important part. Because the data backup system requires a lot of tables and relationships

In the current information age, big data, artificial intelligence, cloud computing and other technologies have become the focus of major enterprises. Among these technologies, graphics card rendering technology, as a high-performance graphics processing technology, has received more and more attention. Graphics card rendering technology is widely used in game development, film and television special effects, engineering modeling and other fields. For developers, choosing a framework that suits their projects is a very important decision. Among current languages, PHP is a very dynamic language. Some excellent PHP frameworks such as Yii2, Ph

As the Internet continues to develop, the demand for web application development is also getting higher and higher. For developers, developing applications requires a stable, efficient, and powerful framework, which can improve development efficiency. Yii is a leading high-performance PHP framework that provides rich features and good performance. Yii3 is the next generation version of the Yii framework, which further optimizes performance and code quality based on Yii2. In this article, we will introduce how to use Yii3 framework to develop PHP applications.

The Yii framework is an open source PHP Web application framework that provides numerous tools and components to simplify the process of Web application development, of which data query is one of the important components. In the Yii framework, we can use SQL-like syntax to access the database to query and manipulate data efficiently. The query builder of the Yii framework mainly includes the following types: ActiveRecord query, QueryBuilder query, command query and original SQL query

As the demand for web applications continues to grow, developers have more and more choices in choosing development frameworks. Symfony and Yii2 are two popular PHP frameworks. They both have powerful functions and performance, but when faced with the need to develop large-scale web applications, which framework is more suitable? Next we will conduct a comparative analysis of Symphony and Yii2 to help you make a better choice. Basic Overview Symphony is an open source web application framework written in PHP and is built on

Correlation queries join tables to combine data from multiple tables through SQL queries. In Golang, use the sql.DB.Query() function to specify the query string and parameters. Different join types can be used, such as INNERJOIN, LEFTJOIN, RIGHTJOIN, and FULLOUTERJOIN, depending on the desired result set.
