


This article mainly introduces the relevant information of multi-table related queries (join, joinwith) in Yii2. It is very good and has reference value. Friends in need can refer to it
Table structure
Now there are customer table, order table, book table, author table,
Customer table Customer (id customer_name)
Order table Order (id order_name customer_id book_id)
Book table(id book_name author_id)
Author table(id author_name)
Model definition
The following is For the definitions of these four models, only the relationships among them are written
Customer
class Customer extends \yii\db\ActiveRecord { // 这是获取客户的订单,由上面我们知道这个是一对多的关联,一个客户有多个订单 public function getOrders() { // 第一个参数为要关联的子表模型类名, // 第二个参数指定 通过子表的customer_id,关联主表的id字段 return $this->hasMany(Order::className(), ['customer_id' => 'id']); } }
Order
class Order extends \yii\db\ActiveRecord { // 获取订单所属用户 public function getCustomer() { //同样第一个参数指定关联的子表模型类名 // return $this->hasOne(Customer::className(), ['id' => 'customer_id']); } // 获取订单中所有图书 public function getBooks() { //同样第一个参数指定关联的子表模型类名 // return $this->hasMany(Book::className(), ['id' => 'book_id']); } }
Book
class Book extends \yii\db\ActiveRecord { // 获取图书的作者 public function getAuthor() { //同样第一个参数指定关联的子表模型类名 return $this->hasOne(Author::className(), ['id' => 'author_id']); } }
Author
class Autor extends \yii\db\ActiveRecord { }
hasMany, hasOne uses tables in
Yii2 There are two types of associations, which are used to specify the association between two models.
One-to-many: hasMany
One-to-one: hasOne
Return results: The return results of these two methods are yii\db\ ActiveQuery object
The first parameter: the class name of the associated model.
The second parameter: is an array, where the key is the attribute in the associated model and the value is the attribute in the current model.
Associated use
Now we get all the order information of a customer
// 获取一个客户信息 $customer = Customer::findOne(1); $orders = $customer->orders; // 通过在Customer中定义的关联方法(getOrders())来获取这个客户的所有的订单。
The above two lines of code will generate the following sql statement
SELECT * FROM customer WHERE id=1; SELECT * FROM order WHERE customer_id=1;
Associated result cache
If the customer's order changes, we will call again
$orders = $customer->orders;
When you get the order again, you will find that there is no change. The reason is that the database will only be queried when $customer->orders is executed for the first time, and the results will be cached, and sql will not be executed during subsequent queries.
So what if I want to execute sql again? You can execute
unset($customer->orders); $customer->orders;
and then you can get the data from the database.
Define multiple associations
Similarly, we can also define multiple associations in Customer.
If the total number of orders is returned greater than 100.
class Customer extends \yii\db\ActiveRecord { public function getBigOrders($threshold = 100) { return $this->hasMany(Order::className(), ['customer_id' => 'id']) ->where('subtotal > :threshold', [':threshold' => $threshold]) ->orderBy('id'); } }
The two associated access methods
As above, if you use
$customer->bigOrders
will get all orders greater than 100. If you want to return orders greater than 200, you can write like this
$orders = $customer->getBigOrders(200)->all();
As you can see from the above, there are two ways to access an association
If called as a function, an ActiveQuery object ($customer->getOrders()->all()) will be returned.
If called as an attribute, the model will be returned directly. The result of ($customer->orders)
with looks at the following code, which is to take a customer's order
// 执行sql语句: SELECT * FROM customer WHERE id=1 $customer = Customer::findOne(1); //执行sql:SELECT * FROM order WHERE customer_id=1 $orders1 = $customer->orders; //这个不会执行sql,直接使用上面的缓存结果 $orders2 = $customer->orders;
If now We need to take out 100 users, and then access each user's order. From the above understanding, we may write the following code
// 执行sql语句: SELECT * FROM customer LIMIT 100 $customers = Customer::find()->limit(100)->all(); foreach ($customers as $customer) { // 执行sql: SELECT * FROM order WHERE customer_id=... $orders = $customer->orders; // 处理订单。。。 }
However, if we really want If written like this, sql will be executed once in each loop of foreach to query the data in the database. Because each $customer object is different.
In order to solve the above problem, you can use yii\db\ActiveQuery::with().
The width parameter is the name of the relationship, which is the getOrders defined in the model, the orders and customer in getCustomer
// 先执行sql: SELECT * FROM customer LIMIT 100; // SELECT * FROM orders WHERE customer_id IN (1,2,...) $customers = Customer::find()->limit(100) ->with('orders')->all(); foreach ($customers as $customer) { // 在这个循环的时候就不会再执行sql了 $orders = $customer->orders; // ...handle $orders... }
If Select is used to specify the returned columns. Be sure to ensure that the returned columns contain the associated fields of the associated model, otherwise the Model
$orders = Order::find()->select(['id', 'amount'])->with('customer')->all(); // $orders[0]->customer 的结果将会是null // 因为上面的select中没有返回所关联的模型(customer)中的指定的关联字段。 // 如果加上customer_id,$orders[0]->customer就可以返回正确的结果 $orders = Order::find()->select(['id', 'amount', 'customer_id'])->with('customer')->all();
## of the associated table will not be returned.
#Add filter conditions to with
Query an order with more than 100 customers//首先执行sql: SELECT * FROM customer WHERE id=1 $customer = Customer::findOne(1); // 再执行查询订单的sql语句:SELECT * FROM order WHERE customer_id=1 AND subtotal>100 $orders = $customer->getOrders()->where('subtotal>100')->all();
Query 100 customers, each customer’s total order is greater than 100
// 下面的代码会执行sql语句: // SELECT * FROM customer LIMIT 100 // SELECT * FROM order WHERE customer_id IN (1,2,...) AND subtotal>100 $customers = Customer::find()->limit(100)->with([ 'orders' => function($query) { $query->andWhere('subtotal>100'); }, ])->all();The width parameter here is an array, and the key is associated Name, value is the callback function. That is to say, for the ActiveQuery returned by the orders association, execute $query->andWhere('subtotal>100');
Use joinWith to perform table processing Association
We all know that we can use join on to write associations between multiple tables. First look at the declaration of joinWit in yii2joinWith( $with, $eagerLoading = true, $joinType = 'LEFT JOIN' )$with The data type is a string or an array. If it is a string, it is associated with the one defined in the model. Name (can be a child association). If it is an array, the key is the association defined in the getXXX format in the model, and the value is the further callback operation for this association.
$joinType 联接类型,可用值为:LEFT JOIN、INNER JOIN,默认值为LEFT JOIN
// 订单表和客户表以Left join的方式关联。 // 查找所有订单,并以客户 ID 和订单 ID 排序 $orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all(); // 订单表和客户表以Inner join的方式关联 // 查找所有的订单和书 $orders = Order::find()->innerJoinWith('books')->all(); // 使用inner join 连接order中的 books关联和customer关联。 // 并对custmer关联再次进行回调过滤:找出24小时内注册客户包含书籍的订单 $orders = Order::find()->innerJoinWith([ 'books', 'customer' => function ($query) { $query->where('customer.created_at > ' . (time() - 24 * 3600)); } ])->all(); // 使用left join连接 books关联,books关联再用left join 连接 author关联 $orders = Order::find()->joinWith('books.author')->all();
在实现上,Yii 先执行满足JOIN查询条件的SQL语句,把结果填充到主模型中, 然后再为每个关联执行一条查询语句, 并填充相应的关联模型。
// Order和books关联 inner join ,但不获取books关联对应的数据 $orders = Order::find()->innerJoinWith('books', false)->all();
On条件
在定义关联的时候还可以指定on条件
class User extends ActiveRecord { public function getBooks() { return $this->hasMany(Item::className(), ['owner_id' => 'id'])->onCondition(['category_id' => 1]); } }
在joinWith中使用
//先查询主模型(User)的数据, SELECT user.* FROM user LEFT JOIN item ON item.owner_id=user.id AND category_id=1 // 然后再根据关联条件查询相关模型数据SELECT * FROM item WHERE owner_id IN (...) AND category_id=1 // 这两个在查询的过程中都使用了 on条件。 $users = User::find()->joinWith('books')->all();
如果没有使用join操作,即使用的是with 或者 直接以属性来访问关联。这个时候on条件会作为where 条件。
// SELECT * FROM user WHERE id=10 $user = User::findOne(10);
总结
首先需要在模型中定义好关联(如getOrders中的Orders为一个关联)
然后在with或者joinWith中使用在模型中定义的关联。
其中在使用关联的时候还可以指定回调方法。
再有就是可以给关联、with、joinWith指定where或者on条件
这一部分其实非常多,也有点乱,有些功能没说说完,如三个表关联、逆关联等。
最基本的操作也就大体是这些。如果还有哪个地方想了解的,可以回帖交流。
以上就是本文的全部内容,希望对大家的学习有所帮助,更多相关内容请关注PHP中文网!
相关推荐:
The above is the detailed content of About using join and joinwith for multi-table association query in Yii2. For more information, please follow other related articles on the PHP Chinese website!

JOIN的含义就如英文单词“join”一样,连接两张表,大致分为内连接,外连接,右连接,左连接,自然连接。先创建两个表,下面用于示例CREATETABLEt_blog(idINTPRIMARYKEYAUTO_INCREMENT,titleVARCHAR(50),typeIdINT);SELECT*FROMt_blog;+----+-------+--------+|id|title|typeId|+----+-------+--------+|1|aaa|1||2|bbb|2||3|ccc|3|

Join的类型leftjoin,以左表为驱动表,以左表作为结果集基础,连接右表的数据补齐到结果集中rightjoin,以右表为驱动表,以右表作为结果集基础,连接左表的数据补齐到结果集中innerjoin,结果集取两个表的交集fulljoin,结果集取两个表的并集mysql没有fulljoin,union取代union与unionall的区别为,union会去重crossjoin笛卡尔积如果不使用where条件则结果集为两个关联表行的乘积与,的区别为,crossjoin建立结果集时会根据on条件过

join查询和多次查询比较MySQL多表关联查询效率高点还是多次单表查询效率高?在数据量不够大的时候,用join没有问题,但是一般都会拉到service层上去做第一:单机数据库计算资源很贵,数据库同时要服务写和读,都需要消耗CPU,为了能让数据库的吞吐变得更高,而业务又不在乎那几百微妙到毫秒级的延时差距,业务会把更多计算放到service层做,毕竟计算资源很好水平扩展,数据库很难啊,所以大多数业务会把纯计算操作放到service层做,而将数据库当成一种带事务能力的kv系统来使用,这是一种重业务,

简介A的独有+AB的公有B的独有+AB的公有AB的公有A的独有B的独有A的独有+B的独有+AB的公有A的独有+B的独有练习建表部门表DROPTABLEIFEXISTS`dept`;CREATETABLE`dept`(`dept_id`int(11)NOTNULLAUTO_INCREMENT,`dept_name`varchar(30)DEFAULTNULL,`dept_number`int(11)DEFAULTNULL,PRIMARYKEY(`dept_id`))ENGINE=InnoDBAUT

利用MySQL的JOIN函数进行表的连接操作在MySQL中,JOIN是一种非常常用的操作,它允许我们将两个或多个表根据它们之间的关联字段进行连接。这样可以方便地从多个表中查询和获取相关数据,提高查询效率和灵活性。本文将使用代码示例演示如何利用MySQL的JOIN函数进行表的连接操作。先创建两个示例表:students和scores。students表包含学生

SimpleNested-LoopJoin我们来看一下当进行join操作时,mysql是如何工作的。常见的join方式有哪些?如图,当我们进行连接操作时,左边的表是驱动表,右边的表是被驱动表SimpleNested-LoopJoin这种连接操作是从驱动表中取出一条记录然后逐条匹配被驱动表的记录,如果条件匹配则将结果返回。然后接着取驱动表的下一条记录进行匹配,直到驱动表的数据全都匹配完毕因为每次从驱动表取数据比较耗时,所以MySQL并没有采用这种算法来进行连接操作BlockNested-LoopJ

在大多数的Web应用中,数据库操作是最基本也是最重要的一环。而MySQL作为目前最常用的关系型数据库管理系统,在承载了无数网站和应用的同时,也面临着越来越大规模的数据处理和查询访问压力。在这种背景下,性能优化成为了MySQL数据库管理的一个重要环节,而JOIN操作是其中的一个关键点。JOIN连接是MySQL中最常用的数据查询语句之一。在

PHP是一种常用的服务器端脚本语言,它拥有许多有用的函数,其中之一就是join()函数。它可以将一个数组转换为一个字符串,本文将详细介绍如何使用PHPjoin()函数将数组转换为字符串。一、什么是PHPjoin()函数?join()函数也称为implode()函数,是PHP中将数组转换为字符串的方法之一。它的作用是将数组中的元素连接起来形成一个字符串,可


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

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function

Dreamweaver Mac version
Visual web development tools

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

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft
