Model search with not exists with related tables

[dgtal]

Hello, i have a GridView widget with the available fields to insert in a data sheet.

A field can be on many sheets, and i have to find all the fields that are not on a specific sheet.

The working query is the following:

SELECT * FROM `ana_fields` AS af

WHERE NOT EXISTS(

    SELECT *

    FROM fields_on_ana_type AS foat

    WHERE af.id = foat.campo

    AND foat.ana_sheet = 8

)

How can i translate that in the Search Model in YII2?

The second where condition can be simply [‘ana_sheet’=>8]

But the first one? (af.id = foat.campo)

$query->andFilterWhere(

                ['not exists',(new Query())->select('id')->from('fields_on_ana_type')->where([<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />??])]

            );

[alirz23]

did you try buildExistsCondition?

http://www.yiiframework.com/doc-2.0/yii-db-querybuilder.html#buildExistsCondition()-detail

$subQuery = (new Query())->select('id')->from('fields_on_ana_type')->where([<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/huh.gif' class='bbc_emoticon' alt='???' />??]);


$query->buildExistsCondition('NOT EXISTS', $subQuery);

[dgtal]

Ok, i solved using a text where condition

$query->andFilterWhere(

            ['not exists',(new Query())->select('id')->from('fields_on_ana_type foat')->where('foat.campo = ana_field.id AND foat.ana_type = :nca',['nca'=>$this->nca])]


        );