How to make subquery with ActiveQuery in Yii2

Question

Problem: I don't know how to use a subquery in a query in Yii2 (within activequery objects, obviously)

Suppose I have a small database:

country
id name
1  Netherlands
2  New Zealand
3  New Guinea

person
id countryid name
1  1         Mr. Hollander
2  2         Mr. New Zealander
3  2         Ms. New Zealander
4  3         Mr. New Guinean

Suppose I want to select all person names from the countries starting with new. A reqular query would be:

SELECT name FROM person WHERE countryid IN (
    SELECT id FROM country WHERE name LIKE 'new%'
)

In Yii2 the first part is easy:

$query = Person::find();

I Googled my problem, and came across this SO question. My problem would be solved with this code:

$searchParam = 'new';
$query = Person::find();
$subquery = Country::find()->select('id')->andWhere(['like', 'name', $searchParam])->all();
$query->andWhere(['in', 'countryid', $subquery])->all();

But that caused Yii2 to generate this error: Object of class app\models\Country could not be converted to string, which is obviously caused by the where() method, which expects an string or an array with 3 string entries, and the ActiveQuery object is no string.

So how should I write above method to create a subquery?

PS: I might also be messing up the usage of andWhere(). Even from the documentation, I'm not sure when to use andWhere() and when to use where().

Answer 1

Remove ->all() from the subquery. Because of this the query is executed and the results are returned. When you remove this the result will be an ActiveQuery and so it can be used as a subquery in the where part.

Answer 2

it should be something like this:

$searchParam = 'new';
$query = Person::find();
$subquery = Country::find()->select('id')->andWhere(['like', 'name', $searchParam])->createCommand()->getRawSql();
$query->select(['*','('.$subquery.') AS country']);
$query->andWhere(['in', 'countryid', $subquery])->all();

also add this in your Person model:

public $country;

you just set the subquerye as a select field with the alias and add add var in the model so you can call it like this:

$model->coutry

When $model var is the Person model