Sorting For Calculated Column Using Dataprovider

Yii 1.1 General Discussion
1

Hello there,

I have used gridview to display records from the database using dataprovider. While doing so, one of the column is calculated based on the value of the columns in the database.

For Instance:

In Gridview the function is called in value

array(

        'header' => 'H',


    'type' => 'html',


    'value' => array($this,'hourHighlight')

),

The function ‘hourHighlight’ returns the column value among the three columns from the database. For Instance: three columns in database A, B, C let’s say and this function returns the greatest value among these three.

So, if I am to sort this column ‘H’, how would I do it?

Thanks,

Ujjwal

2

So you are now getting "hourHighlight" in AR model (using getHourHighlight() method) after you have retrieved the source columns from DB.

You have to get it directly from db using some sql when you want to sort by it.




$public hourHighlight;  // not a function backed attribute, but a plain attribute

...

public function search()

{

...

    $criteria->select = array(

        "IF(a > b,

            IF(a > c, a, c),

            IF(b > c, b, c)

         ) AS hourHighlight",

        "*"

    );

...

}
3

I did exactly the same but then still no sorting option. The resultant is still like the previous one. How can I have sort enabled doing your way. Is there something else I need to do?

Any help would be appreciated!

Thanks.

4

When you use CActiveDateProvider, you have to configure the "sort" property of it in order to sort.




return new CActiveDataProvider( 'MyModel', array(

    'criteria'=>$criteria,

    'sort'=>array(

        'attributes'=>array(

            'hourHighlight'=>array(

                'asc'=>'hourHighlight',

                'desc'=>'hourHighlight DESC',

            ),

            '*',

        ),

    ),

));

And you have to define the "name" property of the coloumn of CGridView.




'columns' => array(

    ...

    array(

        'name' => 'hourHighlight',

        'header' => 'H',

        ...

    ),

    ...

),

The following wiki is for searching and sorting by a relation, but it’s also useful in understanding how to use a non-db-backed attribute in searching and sortting.

http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview/