create filter on calculated column

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.

I cannot quite make out where you are calculating values, but if you’re doing so through aggregate functions in SQL, you cannot filter them in a [font=“Courier New”]WHERE[/font] clause. Use [font=“Courier New”]HAVING[/font] instead (see also: andHaving())

Hi Sourcerer - I am getting the value from the function getOtServices in my gridview, which I am referring to as calculated value.

Ah, I see. So ultimately you want to filter on the result of that [font=“Courier New”]GROUP_CONCAT[/font]? Would still have to go through [font=“Courier New”]HAVING[/font]. As I’m a bit tired already, could you please confirm the following method gives identical results to your current solution?

public function getOtServices() {

    return Yii::$app->db->createCommand('SELECT GROUP_CONCAT(im.instrument_name) AS instrument

                                        FROM instrument_master im 

                                        JOIN ot_instrument_entry oie ON oie.instrument_name = im.id

                                        JOIN ot_note otn ON otn.id = oie.ot_note_id

                                        WHERE otn.id = :id', [':id' => $this->id])->queryScalar();

}

Yes It does. It is giving the identical result.

Now How should I form my filter query?

It’s fairly simple: You need to reverse those joins and put them into your search query. Then add a condition like [font=“Courier New”]->andHaving([‘like’, ‘GROUP_CONCAT(im.instrument_name)’, $this->otServices])[/font] (perhaps you should check if [font=“Courier New”]$this->otServices[/font] is empty first)

Oh, and I think one of those [font="Courier New"]JOIN[/font]s is bogus:

public function getOtServices() {

    return Yii::$app->db->createCommand('SELECT GROUP_CONCAT(im.instrument_name) AS instrument

                                        FROM instrument_master im 

                                        JOIN ot_instrument_entry oie ON oie.instrument_name = im.id

                                        WHERE oie.ot_note_id = :id', [':id' => $this->id])->queryScalar();

}

Should give the same result.

Hi Sourcerer - I am getting the error like

Operator 'LIKE' requires two operands.

I have added the code exactly like you have suggested:

->andHaving(['like', 'GROUP_CONCAT(im.instrument_name)', $this->otServices])

It would help if you could paste the full error message. Anyway, I just realised [font="Courier New"]andHaving()[/font] is modifying the middle operand. This should work better:

->andHaving(['GROUP_CONCAT(im.instrument_name)' => ':otServices'], [':otServices' => $this->otServices])

Hi Sourcerer - with the updated code I am getting this error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables

does not match number of tokens

The SQL being executed was: SELECT COUNT(*) FROM `ot_note` 

LEFT JOIN `ipd_patient_entry` ON `ot_note`.`ipd_patient_id` = `ipd_patient_entry`.`id` 

HAVING GROUP_CONCAT(im.instrument_name)=':otServices'

This error is generated when accessing the grid-view page, without using any filter.

Here I think the Sql generated is not correct as there are three tables inter-related.

ot_instrument_entry - oie.ot_note_id = ot_note.id

instrument_master - im.id = oie.instrument_name

To achieve the same I think we need to create some kind of nested relation in the model.

Hi Sourcer Thanks for ur help. I will welcome eager to achieve the same with alternative approach of achieving the same.

But I Resolved it by creating a nested relation like:

public function getInstrumentMaster()

    {

    return $this

       ->hasMany(InstrumentMaster::className(), ['id' => 'instrument_name'])

       ->viaTable('ot_instrument_entry', ['ot_note_id' => 'id']);

    }

Then in my search.php

public $instrument_name;


->andFilterWhere(['like', 'instrument_master.instrument_name', $this->instrument_name])

This is working perfectly fine.