Yii add filter to a virtual attribute in CGridView and make it sortable

Question

I have the following Models :

User with columns {id,user_name,password,user_type}

Admin with columns {id,user_id,full_name,.....etc}

Editor with columns {id, user_id,full_name,...etc}

and the relations are User : 'admin' => array(self::HAS_ONE, 'Admin', 'user_id'),'editor' => array(self::HAS_ONE, 'Editor', 'user_id'),

Admin : 'user' => array(self::BELONGS_TO, 'User', 'user_id'),

Editor : 'user' => array(self::BELONGS_TO, 'User', 'user_id'),

Now i had setup a virtual attribute fullName in User Model as below

public function getFullName()

{

    if($this->user_type=='admin')

        return $this->admin->full_name;

    else if($this->user_type=='editor')

        return $this->editor->full_name;


}

I can show the virtual attribute , fullName , in a gridview , But how do i add a filter to the attribute and make it sortable in the gridview?

UPADTE 1:

I updated the models search() function as per the answer by @Jon as shown below

    public function search()
        {


            $criteria=new CDbCriteria;
            $criteria->select=array('*','COALESCE( editor.full_name,admin.first_name, \'\') AS calculatedName');
            $criteria->with=array('editor','admin');
            $criteria->compare('calculatedName',$this->calculatedName,true);
            $criteria->compare('email',$this->email,true);
            $criteria->compare('user_type',$this->user_type);

            return new CActiveDataProvider($this, array(
                'criteria'=>$criteria,


));
    }

The names of both admins and editors are shown correctly in the gridview. But when i do a search through the filter the following exception occurs,

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'calculatedName' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `user` `t`  LEFT OUTER JOIN `editor` `editor` ON (`editor`.`user_id`=`t`.`id`)  LEFT OUTER JOIN `admin` `admin` ON (`admin`.`user_id`=`t`.`id`)  WHERE (calculatedName LIKE :ycp0) (C:\xampplite\htdocs\yii\framework\db\CDbCommand.php:528)</p><pre>#0 C:\xampplite\htdocs\yii\framework\db\CDbCommand.php(425):

How can I get rid of this?

UPDATE 2: My mistake. It works fine when i changed the line

$criteria->compare('calculatedName',$this->calculatedName,true);

to

$criteria->compare('COALESCE( editor.full_name,admin.first_name, \'\')',$this->calculatedName,true);

and btw thanx @Jon.

Gadid

Answer 1

What you are trying to do here is effectively add a calculated column to the result set. Imagine that in the SQL query used to fetch the results you will be joining both the Admin and Editor tables, so Admin.full_name and Editor.full_name are the two columns that will be involved in calculating the desired value.

Since at least one Admin.full_name and Editor.full_name is always going to be NULL, the formula to calculate the final value would be

COALESCE(Admin.full_name, Editor.full_name, '')

Now that you have the calculated formula, you need to take these steps:

1. Add a read-write column to your model to receive the calculated column
2. Create a CDbCriteria that joins the two tables and includes the calculated column
3. Create a CSort that describes how the calculated column should affect the record order
4. Create a CActiveDataProvider that uses these criteria and sort options
5. Feed the data provider to your CGridView

So, first add a public property to the model:

public $calculatedName;

And then:

$criteria = new CDbCriteria(array(
    'select' => array(
        '*',
        'COALESCE(Admin.full_name, Editor.full_name, \'\') AS calculatedName',
    ),
    'with'   => array('Admin', 'Editor'),
    // other options here
));

$sort = new CSort;
$sort->attributes = array(
    'calculatedName' => array(
        'asc'  => 'COALESCE(Admin.full_name, Editor.full_name, \'\')',
        'desc' => 'COALESCE(Admin.full_name, Editor.full_name, \'\') DESC',
    ),
    // other sort order definitions here
);

$dataProvider = new CActiveDataProvider('User', array(
    'criteria' => $criteria,
    'sort'     => $sort,
));

And finally, use $dataProvider to populate your grid; use calculatedName as the column name.

Apologies if I got some detail wrong, as I did not actually run this.

Update: It turns out that Yii doesn't like it if you specify CDbCriteria.select as a string and that string contains any commas not used to separate columns (such as the commas used to separate the arguments to COALESCE). Thankfully CDbCriteria also allows passing in the columns as an array, which gets around this problem. I updated the code above to match.

For anyone who's curious, the offending code is this.

Complexion

I don't know I'm doing anything wrong but i keep getting this error CDbException:Active record "User" is trying to select an invalid column "COALESCE( admin.full_name". Note, the column must exist in the table or be an expression with alias.. – Gadid

@dInGd0nG: You are doing nothing wrong, the answer I posted just ran afoul of one of Yii's little quirks. See update. – Complexion

okey. The update works well. But i cant use $criteria->compare('fullName',$this->calculatedName,true); as when i do a search in the CGridView filter the following Exception occures <p>CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'calculatedName' in 'where clause'. The SQL statement executed was: SELECT COUNT(DISTINCT t.id) FROM user t LEFT OUTER JOIN editor editor ON (editor.user_id=t.id) LEFT OUTER JOIN admin admin ON (admin.user_id=t.id) WHERE (calculatedName LIKE :ycp0) – Gadid

@dInGd0nG: It would be helpful if you posted the relevant code as an update to the question. Anyway I think that to filter on the column you need to add it manually, e.g. $criteria->condition = "COALESCE (...) LIKE '%$var%'", substituting an appropriate quoted expression for $var. Calculated columns sure are a lot of trouble. – Complexion