How to write subquery in yii2 framework

How to use subquery in Yii

The first step is to create a subquery, which can be Created based on yii\db\Query or based on Model.

$subQuery = Order::find()
->where(['user_id' => $userId])
->andWhere(['status' => $status]);

You can also add sorting and paging, for example:

$subQuery->orderBy(['id' => SORT_ASC])
->offset($offset)
->limit($pageSize);

Then we can use this subquery in our main query, as long as it is a place where subqueries can be written in mysql , you can use this subquery directly.

$list = (new Query())->select($field)
->from(['order' => $subQuery]) // 在这里使用了子查询
->leftJoin(['goods' => OrderGoods::tableName()], 'order.id = goods.order_id')
->createCommand()
->queryAll();

Finally generated statement

SELECT
*
FROM
( SELECT
* 
FROM
`od_order` 
WHERE
( `user_id` = '1' ) 
ORDER BY
`id` ASC
LIMIT 10 OFFSET 1 
) `order`
LEFT JOIN `od_order_goods` `goods` ON `order`.id = goods.order_id

PHP Chinese website has a large number of free Yii introductory tutorials, everyone is welcome to learn!

The above is the detailed content of How to write subquery in yii2 framework. For more information, please follow other related articles on the PHP Chinese website!