Where clause for 1:M

Yii 2.0 General Discussions
1

I have 2 tables :

Product Option Group

  • id (data : 1)
  • opt_name (data : Cook Level)
  • active_flag (data : 0)

Product Option List

  • id (data : 1,2,3,4)
  • optgrp_id (data : 1,1,1,1)
  • list_name (data : 25,50,75,100)
  • active_flag (data : 0,1,0,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 :slight_smile:

2

In code you posted only this function can return a list,

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

And that function does not take care of the active_flag.

Maybe see https://www.yiiframework.com/doc/guide/2.0/en/db-active-record

3 
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]) <== Isn't this line take care of the active flag?
                    ->one()) !== null) {
        return $model;
    }

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

Thank mathis for your reply :slight_smile:

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

I got my expected result by doing minor changes on the above function.
Thank you mathis