GEOFFREY MWANGI GEOFFREY MWANGI - 3 months ago 24
PHP Question

Comparing two active records in yii2

I have two models in yii2 which are related . I would like to compare them such that using an id i get the id values in a not in b

That is
I have a pr model and pr items model and i would like to get the id values in pr model not in pritems model

I have tried

$pr = Tblpr::find()->all(); //This returns all pr model items

$pritems = Tblpritems::find()->all() //this returns all pritems

$pr and $pritems are related by id such that id in pritems is a foreign key referencing pr


How can i get the ids in pr but not in pritems.

Answer

There are several way

You can try with findBySql (using a subquery )

$sql = 'SELECT * FROM tblpr as a 
        where a.id not in (select b.id 
                           Tblpritems as b)';
$model = Tblpr::findBySql($sql)->all();  

or using left join

$sql = 'SELECT * FROM tblpr 
        left join Tblpritems on tblpr.id = Tblpritems. id 
        where wTblpritems.id  is null';
$model = Tblpr::findBySql($sql)->all(); 

Or you can use activeQuery

$query = Tblpr::find();
$query->leftJoinWith('tblpritems', 'tblpr.id = tblpritems.id');
$query->andWhere(['pritems.id' => null]);
$model = $query->all();

for dataProviding you can

$provider = new ActiveDataProvider([
   'query' => $sql,
   'pagination' => [
       'pageSize' => 20,
   ],
]);
Comments