Yii 向 CGridView 中的虚拟属性添加过滤器并使其可排序 [英] Yii add filter to a virtual attribute in CGridView and make it sortable

问题描述

我有以下模型:

User 列 {id,user_name,password,user_type}

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

Admin 列 {id,user_id,full_name,....etc}

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

Editor 列 {id, user_id,full_name,...etc}

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

关系是用户 : 'admin' =>数组(self::HAS_ONE, 'Admin', 'user_id'),'editor' =>数组(self::HAS_ONE, 'Editor', 'user_id'),

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

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

现在我在 User 模型中设置了一个虚拟属性 fullName 如下

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;


}

我可以在 gridview 中显示虚拟属性 fullName ，但是如何向属性添加过滤器并使其在 gridview 中可排序?

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?

更新 1:

我根据@Jon 的回答更新了模型 search() 函数，如下所示

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,


));
    }

管理员和编辑者的名字都在 gridview 中正确显示.但是当我通过过滤器进行搜索时，会发生以下异常，

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):

我怎样才能摆脱这个?

更新2:我的错.当我改变线路时它工作正常

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

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

到

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

顺便说一句谢谢@Jon.

and btw thanx @Jon.

推荐答案

您在这里尝试做的是有效地将计算列添加到结果集中.想象一下，在用于获取结果的 SQL 查询中，您将同时加入 Admin 和 Editor 表，因此 Admin.full_name 和 >Editor.full_name 是计算所需值时将涉及的两列.

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.

因为至少有一个 Admin.full_name 和 Editor.full_name 总是 NULL，所以计算最终值的公式是是

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. 向模型添加读写列以接收计算列
2. 创建一个 CDbCriteria 连接两个表并包含计算列
3. 创建一个 CSort 来描述计算列应该如何影响记录顺序
4. 创建一个使用这些条件和排序选项的 CActiveDataProvider
5. 将数据提供者提供给您的 CGridView

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;

然后:

$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,
));

最后，使用 $dataProvider 来填充你的网格；使用 calculatedName 作为列名.

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.

更新: 事实证明，如果您将 CDbCriteria.select 指定为字符串并且该字符串包含任何未用于分隔列的逗号(例如用于分隔 COALESCE 参数的逗号).值得庆幸的是，CDbCriteria 还允许将列作为数组传入，从而解决了这个问题.我更新了上面的代码以匹配.

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.

这篇关于Yii 向 CGridView 中的虚拟属性添加过滤器并使其可排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！