Yii CGridview sorting with CSqlDataProvider

Question

I'm using CSqlDataProvider to construct CGridview I cannot use CActiveRecord because the result set is huge and throwing memory errors. The columns need to be sortable. How should I achieve this? Sample sql ``` $orders_query_raw = 'select o.order_id, o.customer_name, o.customer_email, o.customer_advertiser, o.payment_method, o.created, o.last_updated, o.currency, o.currency_value, o.status, o.blinking, s.name, ot.text order_total, o.customer_id, op.product_id, o.phonebooking from `order` o, `order_total` ot, `order_status` s , order_product op where o.order_id = op.order_id and o.status = s.order_status_id and ot.order_id = o.order_id and s.language_id = '1' and ot.class = 'ot_total' group by o.order_id' ``` sql dataprovider ``` $dataProvider = new CSqlDataProvider($orders_query_raw, array( 'totalItemCount'=>$count, // get from a count query 'pagination'=>array( 'pageSize'=>50, ), )); ``` And gridview ``` $this->widget('zii.widgets.grid.CGridView', array( 'dataProvider' => $dataProvid…

Accepted Answer

To enable sorting (by clicking on header of a column) in the grid-view with data provider as `CSqlDataProvider`, you'll need minimally 2 things:

- Have to define the `CSort` object for the data provider, with the `attributes` that would be sortable.

- Have to define the `name` of the column but only in case you are specifying the `columns` property of the grid-view, otherwise if the columns property is left blank, whatever attributes are mentioned in the CSort object will be sortable.

That said, the other answer should work in cases when the sql is simple, and comes from 1 table, but in your case, where the sql is a little complicated i.e data comes from multiple tables, the solution will change slightly.

In such cases you'll have to account for conflicting column names(if any), and proper specification of CSort's `attributes` array.

Examples:

No conflicting column names in any of the tables (same as the other answer):

```
$dataProvider=new CSqlDataProvider($sql, array(
    'totalItemCount'=>$count,
    'sort'=>array(
        'attributes'=>array(
            'order_id, order_total' // csv of sortable column names
        )
    )
));
```

Then in your grid:

```
array(
    'header'=>Yii::t('order', 'Order total'),
    'name'=>'order_total',// to make header clickable to sort
    'value'=>'strip_tags($data[\'order_total\'])',
    'type'=>'raw',
    'htmlOptions'=>array(
        'style'=>'text-align:right;',
    ),
),
```

Conflicting column names:

- First, give all conflicting names aliases in your sql.

Second, specify those aliases as the sortable `attributes` in the `CSort` object:

```
'attributes'=>array(
    'some_alias, some_other_alias'
)
```

Specify the `name` for the column in `columns`:

```
array(
    'header'=>'Foo',
    'name'=>'some_alias',
    'value'=>'$data[\'some_alias\']' // this is actually redundant in this
    // case, because the name will itself pick up the value, and we don't
    // need to specify value explicitly if we are not applying any function to it
)
```

Note that sorting by url calling is enabled by just specifying the sort object, no need to use `name`, unless you want click to sort headers.

Yii CGridview sorting with CSqlDataProvider

Solution

Problem