Reputation: 4283
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
Upvotes: 3
Views: 7131
Reputation: 17478
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:
CSort
object for the data provider, with the attributes
that would be sortable.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:
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.
Upvotes: 6
Reputation: 15981
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
));
Upvotes: 1