Yii 2.0 Using Subqueries

February 23, 2015 by Hirad Daneshvar

Tags: programming php sql web yii yii 2.x find query select fetching records where subquery

In this post I want to show you how to use subqueries in your conditions.

In this post I want to show you how to use subqueries in your conditions.

I assume you know what subqueries are, and for those of you who don't know what they are, I will talk about them in an entire post, later.

So, what we want to do here is to use IN in our condition. You know that when we are using IN in our conditions, we want to say the attribute should be one (or more) of the following values. Imagine we have a table called 'Node' and its primary key is nid. The following SQL syntax selects the record with the specific nid:

"SELECT * FROM Node WHERE nid = 2"

This in Yii 2.0 changes to:

"$node = \app\models\Node::find()->where(['nid' => 2])->one();"

But we want to have a query so that it fetches all nodes with specific nids. We have a problem though! We don't know what our nids are, and another SQL query wii determine what these nids are. Imagine we have a table called body, and two of its attributes are nid, which is a foreign key, and type. The following SQL query selects all nids from Body if the record's type is "node":

'SELECT nid FROM Body WHERE type = "node"'

This changes to the following code in Yii 2.0:

"\app\models\Body::find()->select('nid')->where(['type' => "node"])->all();"

The query above determines which nids we have to fetch from Node table. So with the use if in and a subquery we can fetch the records we want:

"SELECT * FROM Node WHERE nid IN (SELECT nid FROM Body WHERE type = "node")"

In the query above, at first our subquery returns the nids we want and then our main query works and fetches the records we need.

This query in Yii 2.0 will look like the this:

"\app\models\Node::find()->where(['in','node.nid',(new \yii\db\Query())->select('nid')->from('Body')->where(['type' => 'node']))])->all();"

So this is how we can use subqueries in Yii 2.0.

ATTENTION: The example above may not show how important it is to use subqueries in some of your queries. In our example we could easily fetch the records we want by using joinWith() and where(). But sometimes your queries are not this easy!