CODEWITHSUNDEEP
yii2
John
John

Reputation: 109

Yii2 - Where clause for 1:M relation

I have 2 tables :

Product Option Group

 id |  opt_name  | active_flag
------------------------------
  1 | Cook level |     0

Product Option List

 id | optgrp_id | list_name | active_flag
------------------------------------------
  1 |     1     |     25    |     0
  2 |     1     |     50    |     1
  3 |     1     |     75    |     0
  4 |     1     |    100    |     0

Product Option Group Model

public function getOptList()
{
    return $this->hasMany(ProdOptlist::className(),['optgrp_id'=>'id']);
}

Product Option List Model

public function getOptGrp()
{
    return $this->hasOne(ProdOptgrp::className(),['id'=>'optgrp_id']);
}

Product Option Group Controller

public function actionUpdate($id)
{
    $model = $this->findModel($id);
    if($model->load(Yii::$app->request->post()) && $model->validate())
    {
        ...
    }
    else
        return $this->render('update', ['model'=>$model]);
}

protected function findModel($id)
{
   if (($model = ProdOptgrp::find()
                    ->joinWith('optList')
                    ->where([ProdOptgrp::tableName().'.id'=>$id, 
                        ProdOptgrp::tableName().'.active_flag'=>0, 
                        ProdOptlist::tableName().'.active_flag'=>0])
                    ->one()) !== null) {
        return $model;
    }

    throw new NotFoundHttpException('The requested page does not exist.');
}

Update View

Expected output for print_r($model->optList) : 

{
    [id] => 1
    [optgrp_id] => 1
    [list_name] => 25
    [active_flag] => 0
},
{
    [id] => 3
    [optgrp_id] => 1
    [list_name] => 75
    [active_flag] => 0
},
{
    [id] => 4
    [optgrp_id] => 1
    [optList_name] => 100
    [active_flag] => 0
}

Actual output : 

{
    [id] => 1
    [optgrp_id] => 1
    [list_name] => 25
    [active_flag] => 0
},
{
    [id] => 2
    [optgrp_id] => 1
    [list_name] => 50
    [active_flag] => 1
},
{
    [id] => 3
    [optgrp_id] => 1
    [list_name] => 75
    [active_flag] => 0
},
{
    [id] => 4
    [optgrp_id] => 1
    [optList_name] => 100
    [active_flag] => 0
}

Yii2 debugger showing correct query but output still consist of all 4 elements. Kindly advice if there is any mistake, thank you in advance :)

Upvotes: 1

Views: 890

Answers (2)

Michal Hynčica
Michal Hynčica

Reputation: 6169

Even if you use the joinWith() the where() part of your $model = ProdOptgrp::find()... code only limits the result that is returned by the query executed by one() method call. It doesn't affect what is loaded for relations.

If you want to limit what is loaded for relations you can do it:

1) By modifing the existing relation

This is the solution you've come to. You add the where condition directly to the getOptList() method of your ProdOptgrp model. If you do it this way, the $model->optList will always return filtered relation. Depending on case that might be advantage or disadvantage.

2) By adding second relation

You can create another method in your ProdOptgrp model that will define the filtered relation while keeping the original getOptList() unfiltered. For example like this:

class ProdOptgrp extends \yii\db\ActiveRecord
{
    public function getOptList()
    {
        return $this->hasMany(ProdOptlist::className(),['optgrp_id'=>'id']);
    }

    public function getFilteredOptList()
    {
        return $this->getOptList()->where([            
            ProdOptlist::tableName() . '.active_flag' => 0
        ]);
    }
}

In this case the $model->optList will still contain unfiltered OptLists and the $model->filteredOptList will contain filtered. This solution is good when you need to use both at different spots of your application.

3) By using callback syntax in joinWith() or with()

In case you want to filter the relation only in one particular case you don't need to modify your model. You can modify the relation with callback that is called before loading the data.

$model = ProdOptgrp::find()
    ->joinWith([
        'optList' => function(\yii\db\ActiveQuery $query) {
            $query->where([
                ProdOptlist::tableName() . '.active_flag' => 0
            ]);
        }
    ])->where([
        ProdOptgrp::tableName().'.id'=>$id, 
        ProdOptgrp::tableName().'.active_flag'=>0,
        ProdOptlist::tableName() . '.active_flag'=>0, 
    ])->one();

The last condition in where() will cause the $model to be null when the active_flag is 0 in optgrp table but 1 in all related records in optlist table. I'm not sure if that is inteded behavior.

Upvotes: 3

Serghei Leonenco
Serghei Leonenco

Reputation: 3507

You can do it this way too:

//Controller

protected function findModel($id)
{
   if (($model = ProdOptgrp::findOne($id)) !== null && $model->isActive()) {
        return $model;
    }

    throw new NotFoundHttpException('The requested page does not exist.');
}

//Model 

/*
*Check if model is active
*/
public function isActive(){
    return ($this->active_flag == 0) ? true: false;
}

public function getOptList()
{
    return $this->hasMany(ProdOptlist::className(),['optgrp_id'=>'id'])->where([ProdOptgrp::tableName().'.active_flag'=>0]);
}

//View

foreach($model->optList as optItem): // you get filtered result

Upvotes: 1

Related Questions