Fetch from two tables where primary keys only exist in one of both tables

Yii 2.0
1

Hello guys,

i have an issue , please pardon me as i wont be able to provide the exact table names.

Say i have two tables; table purple and table green

both tables are connected by code

Here is the relationship; in my purple table i did a relationship below

public function getGreen()
{
return $this->hasOne(Green::className(), [‘code’ => ‘code’]);
}

In my gridview i want only show rows where codes do not exist in both tables.

Please how do i do this.

So take for instance if it were normal sql my query will look like this

Select purple.* FROM purple LEFT JOIN green ON purple.code != green.code

2 
$purples = Purple::find()->leftJoin('green', 'purple.code != green.code')->all();

// or

$purples = Purple::findBySql('Select purple.* FROM purple LEFT JOIN green ON purple.code != green.code')->all();
3

Unfortunately its not giving me the desired result also my filters no longer the moment i implement this solution.

4

I think what you want to do in SQL is something like this:

select * from purple where (select count(*) from green where green.code = purple.code) = 0;

So, you could try:

$purples = Purple::findBySql('SELECT purple.* FROM purple where (SELECT COUNT(*) FROM green WHERE green.code = purple.code) = 0')->all();
5

Thanks for your response. This still didn’t work but wrote another sql which later worked. My challenge now is that my filter doesn’t work with the custom query.

6

Consider creating a virtual attribute in Purple model (or PurpleSearch model) which represents the result of custom query whether it has the counterpart in Green model.

class PurpleSarch extends Purple
{
...
    public $hasGreen;
...

    public function search($params)
    {
        ...
        if ($this->hasGreen === true) {
            $query->andWhere('(SELECT COUNT(*) FROM green WHERE green.code = purple.code) <> 0');
        } elseif ($this->hasGreen === false) {
            $query->andWhere('(SELECT COUNT(*) FROM green WHERE green.code = purple.code) = 0');
        }
        ...
    }
}