0

I am working with Yii framework 2.0, I have one database table that looks like the following.

id   active   key
 1   0        xx
 2   1        xx
 3   0        zzz
 4   0        wwww
 5   1        wwww   
 6   1        qqqqq

I would like to get the record where 'active' is 1 and 'key' is the same as 'key' of the record where 'active' is 0. The result that I want is

 id   active   key
  2   1        xx
  5   1        wwww

It might not be easy to understand the question, so I put following code sample to support the question. This codes gives me the result I want.

$allModel = Model::find()->where(['active' => 0])->all();

$arrModelActive1 = [];

foreach($allModel as $model) {
     $modelActive1 = Model::find()->where['active' => 1, 'key' => $model->key]->all();

     $arrModelActive1[] = $modelActive1;
}

return $arrModelActive1;

I could approach this problem with the above code sample, but the problem is that I execute the query inside of the foreach loop which might decrease the performance. So I am looking for a join or an eager loading solution which Yii 2.0 might provide which could operate with the same table or so-called self-join operation.

Community
  • 1
  • 1
O Connor
  • 4,236
  • 15
  • 50
  • 91

1 Answers1

2

Example: You have relation as public function getTest() { return $this->hasMany(self::className(), ['key' => 'key']) ->where('active = :active', [':active' => 1]); }

and after that you must select items with active field == 0

$model = Model::find(['active' => 0])->all(); var_dump($model->test);
Fortran
  • 593
  • 4
  • 14