Yii CGridview 使用 CSqlDataProvider 排序 [英] Yii CGridview sorting with CSqlDataProvider

问题描述

我使用 CSqlDataProvider 来构建 CGridview 我不能使用 CActiveRecord 因为结果集很大并且抛出内存错误.列需要可排序.我应该如何实现这一目标?

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?

示例 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 = new CSqlDataProvider($orders_query_raw, array(
        'totalItemCount'=>$count, // get from a count query
        'pagination'=>array(
            'pageSize'=>50,
        ),
    ));

和网格视图

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

谢谢

推荐答案

在网格视图中启用排序(通过单击列标题)，数据提供程序为 CSqlDataProvider，你至少需要两件事:

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. 必须为数据提供者定义 CSort 对象，使用 属性可以排序.
2. 必须定义列的name但前提是您指定了网格视图的 columns 属性，否则如果 columns 属性留空，则 CSort 对象中提到的任何属性都将是可排序的.

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.

也就是说，另一个答案应该适用于 sql 简单且来自 1 个表的情况，但在您的情况下，sql 有点复杂，即数据来自多个表，解决方案会略有变化.

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.

在这种情况下，您必须考虑冲突的列名(如果有)，以及 CSort 的 attributes 数组的正确规范.

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

示例:

• 任何表中都没有冲突的列名(与其他答案相同):

• 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
        )
    )
));

然后在您的网格中:

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. 首先，在您的 sql 中为所有冲突的名称指定别名.

2. 其次，在CSort对象中将这些别名指定为可排序的attributes:

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

为columns中的列指定name:

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
)

请注意，通过指定排序对象即可启用按 url 调用排序，无需使用 name，除非您希望 单击对标题进行排序.

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 使用 CSqlDataProvider 排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！