Yii2: create filter on calculated column

Question

I have create a function in my model ot_note.php like

public function getOtServices(){
$instrumentsq= Yii::$app->db->createCommand
("SELECT group_concat(im.instrument_name)as instrument
from instrument_master im,  ot_note otn, ot_instrument_entry oie where
otn.id=oie.ot_note_id  and oie.instrument_name=im.id and otn.id=$this->id");

 $instruments=$instrumentsq->queryScalar();
 return $instruments;
    }

Then in my index.php I have included the return value like:

[
  'label'=>'services',
   'attribute'=>'otServices',
   'value'=>'otServices'
],

in ot_note_search.php

public $otServices;
public function rules()
    {
        return [
            ['otServices'], 'safe'],            
        ];
    }



->andFilterWhere([ 'like', 'otServices' , $this->otServices ])

now the values in the column are shown correctly, but filter is not working and I am getting the sql error.

I need some help, how I should setup my filter on this calculated column.

Pavel Bariev Pavel Bariev · Accepted Answer · 2015-04-11T19:25:18

Your "getOtServices" method is being called in "andFilterWhere" instead of "otServices" attribute - see its base Component class "__get" method. And you are having mysql error here because your search model does not have "id" value which is used in the end of "getOtServices" sql query.

I would adjust to remove getOtServices method and add $query->addSelect(["group_concat(im.instrument_name)as instrument ... "]) - but there is not enough info for me about your search method. Anyway I believe this is the right direction for you to go.

Yii2: create filter on calculated column

1 Answers