CODEWITHSUNDEEP
phpyii2yii2-advanced-app
Geoff
Geoff

Reputation: 6629

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.

Upvotes: 1

Views: 716

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133360

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,
   ],
]);

Upvotes: 1

SiZE
SiZE

Reputation: 2267

Just join relatated table and check it ids for null:

$query = Tblpr::find();
$query->innerJoinWith('tblpritems', false);
$query->andWhere(['pritemsId' => null]);
$prWithOutItems = $query->all();

It's better to extend it to TblprQuery and use like Tblpr::find()->withoutItems()->all().

Upvotes: 0

Related Questions