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.
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:
CDbCriteria
that joins the two tables and includes the calculated columnCSort
that describes how the calculated column should affect the record orderCActiveDataProvider
that uses these criteria and sort optionsCGridView
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.