Detailed explanation of YII related query
This article mainly introduces the relevant information of YII related query. Friends in need can refer to it. I hope to be helpful.
1. Configuration of multi-table correlation
Before we use AR to perform correlation queries, we need to let AR know what an 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)
Some points that need to be clarified:
1) models/Post.php
class Post extends CActiveRecord
{
......
public function relations()
{
return array(
'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
);
}
}(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'),
);
}
}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)'),
Example 3, one-to-one relationship
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 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 is often used in In controllers
1, delayed loading(1)many-to-one
$post = Post::model()->findByPk (10);
$author = $post->author;
Note: 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 relationship must be
'FailParts' => array(self::MANY_MANY, 'FailParts', 'repair_mapping(repair_info_id,fail_parts_id)'),
From the perspective of the $failParts instance angle, the association becomes
'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次查询，而后者只有一次。两者的选择视情况而定。
相关推荐：
The above is the detailed content of Detailed explanation of YII related query. For more information, please follow other related articles on the PHP Chinese website!
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
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
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 obtaining administrator rights in Win11
Mar 08, 2024 pm 03:06 PM
Windows operating system is one of the most popular operating systems in the world, and its new version Win11 has attracted much attention. In the Win11 system, obtaining administrator rights is an important operation. Administrator rights allow users to perform more operations and settings on the system. This article will introduce in detail how to obtain administrator permissions in Win11 system and how to effectively manage permissions. In the Win11 system, administrator rights are divided into two types: local administrator and domain administrator. A local administrator has full administrative rights to the local computer
12306 How to check historical ticket purchase records How to check historical ticket purchase records
Mar 28, 2024 pm 03:11 PM
Download the latest version of 12306 ticket booking app. It is a travel ticket purchasing software that everyone is very satisfied with. It is very convenient to go wherever you want. There are many ticket sources provided in the software. You only need to pass real-name authentication to purchase tickets online. All users You can easily buy travel tickets and air tickets and enjoy different discounts. You can also start booking reservations in advance to grab tickets. You can book hotels or special car transfers. With it, you can go where you want to go and buy tickets with one click. Traveling is simpler and more convenient, making everyone's travel experience more comfortable. Now the editor details it online Provides 12306 users with a way to view historical ticket purchase records. 1. Open Railway 12306, click My in the lower right corner, and click My Order 2. Click Paid on the order page. 3. On the paid page
How to check your academic qualifications on Xuexin.com
Mar 28, 2024 pm 04:31 PM
How to check my academic qualifications on Xuexin.com? You can check your academic qualifications on Xuexin.com, but many users don’t know how to check their academic qualifications on Xuexin.com. Next, the editor brings you a graphic tutorial on how to check your academic qualifications on Xuexin.com. Interested users come and take a look! Xuexin.com usage tutorial: How to check your academic qualifications on Xuexin.com 1. Xuexin.com entrance: https://www.chsi.com.cn/ 2. Website query: Step 1: Click on the Xuexin.com address above to enter the homepage Click [Education Query]; Step 2: On the latest webpage, click [Query] as shown by the arrow in the figure below; Step 3: Then click [Login Academic Credit File] on the new page; Step 4: On the login page Enter the information and click [Login];
Detailed explanation of division operation in Oracle SQL
Mar 10, 2024 am 09:51 AM
Detailed explanation of division operation in OracleSQL In OracleSQL, division operation is a common and important mathematical operation, used to calculate the result of dividing two numbers. Division is often used in database queries, so understanding the division operation and its usage in OracleSQL is one of the essential skills for database developers. This article will discuss the relevant knowledge of division operations in OracleSQL in detail and provide specific code examples for readers' reference. 1. Division operation in OracleSQL
Detailed explanation of the role and usage of PHP modulo operator
Mar 19, 2024 pm 04:33 PM
The modulo operator (%) in PHP is used to obtain the remainder of the division of two numbers. In this article, we will discuss the role and usage of the modulo operator in detail, and provide specific code examples to help readers better understand. 1. The role of the modulo operator In mathematics, when we divide an integer by another integer, we get a quotient and a remainder. For example, when we divide 10 by 3, the quotient is 3 and the remainder is 1. The modulo operator is used to obtain this remainder. 2. Usage of the modulo operator In PHP, use the % symbol to represent the modulus
Comparison of similarities and differences between MySQL and PL/SQL
Mar 16, 2024 am 11:15 AM
MySQL and PL/SQL are two different database management systems, representing the characteristics of relational databases and procedural languages respectively. This article will compare the similarities and differences between MySQL and PL/SQL, with specific code examples to illustrate. MySQL is a popular relational database management system that uses Structured Query Language (SQL) to manage and operate databases. PL/SQL is a procedural language unique to Oracle database and is used to write database objects such as stored procedures, triggers and functions. same
How to check the activation date on Apple mobile phone
Mar 08, 2024 pm 04:07 PM
If you want to check the activation date using an Apple mobile phone, the best way is to check it through the serial number in the mobile phone. You can also check it by visiting Apple's official website, connecting it to a computer, and downloading third-party software to check it. How to check the activation date of Apple mobile phone Answer: Serial number query, Apple official website query, computer query, third-party software query 1. The best way for users is to know the serial number of their mobile phone. You can see the serial number by opening Settings, General, About This Machine. . 2. Using the serial number, you can not only know the activation date of your mobile phone, but also check the mobile phone version, mobile phone origin, mobile phone factory date, etc. 3. Users visit Apple's official website to find technical support, find the service and repair column at the bottom of the page, and check the iPhone activation information there. 4. User
Discuz database location query skills sharing
Mar 10, 2024 pm 01:36 PM
Forum is one of the most common website forms on the Internet. It provides users with a platform to share information, exchange and discuss. Discuz is a commonly used forum program, and I believe many webmasters are already very familiar with it. During the development and management of the Discuz forum, it is often necessary to query the data in the database for analysis or processing. In this article, we will share some tips for querying the location of the Discuz database and provide specific code examples. First, we need to understand the database structure of Discuz
