Data Provider and Grid Sorting Problem

I’m new to Yii, but I truly LOVE it! I’m getting so much work done using it!

But I’ve gotten hung up on a simple thing. I can’t seem to sort a CGridView. I’ve studied the class reference and I’ve searched the forum and looked at every example I can find.

Maybe someone can point me in the right direction.

I created a simple book table with fields id, title, authorLastName, and authorFirstName. It contains 6 records. I then wrote a short program that reads the table one of three ways, depending upon the option:

1 - using a CArrayDataProvider

2 - using a CSqlDataProvider

3 - using a CActiveDataProvider

The data provider is displayed using CGridView.

When I use the CArrayDataProvider, the grid sorts perfectly on any column. But when I use the other options, the columns do not sort.

I’m including the code below, which is very straightforward. Why don’t my grids sort?

MODEL

public static function report($option) {


        $sort = new CSort();

        $sort->attributes = array(

            'id',

            'title',

            'authorLastName',

            'authorFirstName',

        );


        switch ($option) {

            case 'CArrayDataProvider':

                $sql = 'SELECT id, 

                    title,

                    authorLastName,

                    authorFirstName

                    FROM book 

                    ORDER BY id';

                $rawData=Yii::app()->db->createCommand($sql)->queryAll();

                $dataProvider=new CArrayDataProvider(

                    $rawData, 

                    array(

                        'sort' => $sort,

                    )

                );

                return $dataProvider;

                break;


            case 'CSqlDataProvider':

                $count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM book')->queryScalar();

                $sql = 'SELECT id, 

                    title,

                    authorLastName,

                    authorFirstName

                    FROM book 

                    ORDER BY id';

                $dataProvider=new CSqlDataProvider(

                    $sql, 

                    array(

                        'totalItemCount'=>$count,

                        'sort' => $sort,

                    )

                );

                return $dataProvider;

                break;


            case 'CActiveDataProvider':

                $dataProvider=new CActiveDataProvider(

                    'Book',

                    array(

                        'criteria'=>array(

                            'select'=>'id, title, authorLastName,

                                authorFirstName',

                            'order' => 'id',

                            ),

                        'sort' => $sort,

                    )

                );

                return $dataProvider;

                break;

        }

VIEW

<?php

echo '<h2>' . $option . '</h2>';


$this->widget('zii.widgets.grid.CGridView', array(

    'dataProvider'=>$dataProvider,

    ));

?>

CONTROLLER

    public function actionReport($option) {

        $model = new Book();

        $dataProvider = Book::report($option);

        $this->render('report', array(

            'dataProvider' => $dataProvider,

            'option' => $option,

            'model' => $model,

        ));

    }

Thanks you so much for your help!

Well it’s not really answering your question, but CGridView sorts very well with CActiveDataProvider. Just check the Model, the Controller & the Admin view automatically generated by Gii.

Hi Bob, welcome to the forum.

CSqlDataProvider and CActiveDataProvider will use "ORDER BY" sql clause for sorting and "OFFSET" and "LIMIT" for pagination when retrieving the output.

On the other hand CArrayDataProvider will do sorting and paginating later on the given array.

So exclude "ORDER BY", "OFFSET" and "LIMIT" from the sql or the criteria when you want to use CSqlDataProvider or CActiveDataProvider with CGridView or CListView, those sql clauses will be constructed by the providers using CSort and CPagination.

public static function report($option) {


        $sort = new CSort();

        $sort->defaultOrder = 'id'; // for initial order

        $sort->attributes = array(

            'id',

            'title',

            'authorLastName',

            'authorFirstName',

        );


        switch ($option) {

            case 'CArrayDataProvider':

                $sql = 'SELECT id, 

                    title,

                    authorLastName,

                    authorFirstName

                    FROM book';

//                  ORDER BY id';  // no need to remove, but is not necessary

                $rawData=Yii::app()->db->createCommand($sql)->queryAll();

                $dataProvider=new CArrayDataProvider(

                    $rawData, 

                    array(

                        'sort' => $sort,

                    )

                );

                return $dataProvider;

                break;


            case 'CSqlDataProvider':

                $count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM book')->queryScalar();

                $sql = 'SELECT id, 

                    title,

                    authorLastName,

                    authorFirstName

                    FROM book'; 

//                  ORDER BY id';  // have to remove

                $dataProvider=new CSqlDataProvider(

                    $sql, 

                    array(

                        'totalItemCount'=>$count,

                        'sort' => $sort,

                    )

                );

                return $dataProvider;

                break;


            case 'CActiveDataProvider':

                $dataProvider=new CActiveDataProvider(

                    'Book',

                    array(

                        'criteria'=>array(

                            'select'=>'id, title, authorLastName, authorFirstName',

//                          'order' => 'id',  // have to remove

                        ),

                        'sort' => $sort,

                    )

                );

                return $dataProvider;

                break;

        }