Yii2 query condition with exists

Question

I have a simple Person tree with parent_id.

I wont to build a (Yii2) query to find all children of a given Person, that are parent of someone else (a.k.a not leaves).

The output SQL should looks like this:

select * from person t 
where exists (select 1 from person p2 where t.id = p2.parent_id);

But cant find the right way to build this with the query builder, there is a method ->exists(), but not much documentation/examples about it.

Solution 1

Not sure if i understood correctly, but do you look something like this.

$subQuery = (new \yii\db\Query)
                ->select([new \yii\db\Expression('1')])
                ->from('person p2')
                ->where('t.id = p2.parent_id');
$query = (new \yii\db\Query())
                ->select('*')
                ->from('person t')
                ->where(['exists', $subQuery]);
$command = $query->createCommand();
print_r ($command->sql);

Generates sql like:

SELECT * FROM `person` `t` WHERE EXISTS (SELECT 1 FROM `person` `p2` WHERE t.id = p2.parent_id)

Solution 2

You should try something like :

$tableName = Person::tableName();
$subQuery = (new Query())->select('*')->from($tableName . ' t2')->where('t1.id=t2.parent_id');
$persons = Person::find()->from($tableName . ' t1')->where(['exists', $subQuery])->all();

http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html#where

Comments

[d.raev]

thanks but, the question is specific for Yii2, and its query builder. I will add a note to make it more clear.