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' => $dataProvider,
    'id'=>'order-grid',
    'columns' => array(
        array(
            'header'=>'Order ID',
            'value'=>array($this, 'gridOrderId'),
            'type'=>'raw',
        ),
        array(
            'header'=>Yii::t('order', 'Customers'),
            'name'=>'customer.fullName',
            'value'=>'CHtml::link($data[\'customer_name\'], \'mailto:\'.$data[\'customer_email\'])',
            'type'=>'raw',
        ),
        array(
            'header'=>Yii::t('order', 'Order total'),
            'value'=>'strip_tags($data[\'order_total\'])',
            'type'=>'raw',
            'htmlOptions'=>array(
                'style'=>'text-align:right;',
            ),
        ),
        array(
            'header' => Yii::t('order', 'Date Purchased'),
            'name' => 'created',
        ),
        array(
            'header'=> Yii::t('order', 'Last modify date'),
            'value'=>array($this, 'gridLastModified'),
        ),
        array(
            'header' => Yii::t('order', 'Status changed by'),
            'value' => array($this, 'gridLastModifiedUserFirstName'),
        ),
        array(
            'header' => Yii::t('provider', 'Provider\'s code'),
            'value' => array($this, 'gridProviderCode'),
            'type' => 'raw',
            'htmlOptions'=>array(
                'class'=>'nobr',
            ),
        ),
        array(
            'header' => Yii::t('order', 'Follow up'),
            'value' => array($this, 'gridFollowUp'),
            'type' => 'raw',
        ),
        array(
            'header' => Yii::t('order', 'Order status'),
            'value' => '$data[\'name\']',
        ),
        array(
            'class'=>'CButtonColumn',
            'template'=>'{update}',
            'header'=>'Action',
            'buttons'=>array(
                'update'=>array(
                    'url'=>'Yii::app()->createUrl(\'order/update\', array(\'order_id\'=>$data[\'order_id\']))',
                ),
            ),
        ),
    ),
));

Thanks

Answer 1

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:

1. Have to define the CSort object for the data provider, with the attributes that would be sortable.
2. 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:

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

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

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

  3. 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.

Answer 2

Try as below

    $sort = new CSort();
    $sort->defaultOrder = 'order_id'; // for initial order
    $sort->attributes = array(
        'created'
    );
$dataProvider = new CSqlDataProvider($orders_query_raw, array(
    'totalItemCount'=>$count, // get from a count query
    'pagination'=>array(
        'pageSize'=>50,
    ),
    'sort'=>$sort
));