How to find all ancestors without grandchildren?

Question

Yii

I know that the wording of the question sounds strange, but I couldn't help it.
there is something like this query that looks for all elders, parents and childs.

SELECT `elders`.* FROM `elders` LEFT JOIN `parents` ON `elders`.`col` = `parents`.`elders_col` LEFT JOIN `childs` ON `parents`.`col` = `childs`.`parents_col`

because I use yii2, then using the constructor looks like this:

$model = Elders::find()->joinWith('parents.childs')->asArray()->all();

I need to find all the elders who may have a relationship with parents, but parents should not have childs.
help to formulate such a request.

Answer 1

you just need to add to the current query WHERE `childs`.`parents_col` IS NULL
looks like this (sql):
yii2:

Elders::find()->joinWith('parents.childs')->where(['childs.parents_col' => null])->asArray()->all();