Could I Sort A Column In Cgridview + Csqldataprovider?

Yii 1.1 General Discussion
1

I use CGridView + CActiveDataProvider to build some query page and I can click the column header to sort.

But I try to use some CGridView + CSqlDataProvider, the query function is ok, but I cannot sort by clicking the column header.

I try to search similar questions, first, I add sort attribute in CSqlDataProvider, it works, the data is sort by my condition, but I still cannot click the column to sort.




$dataProvider=new CSqlDataProvider($sql, array(

  'totalItemCount'=>$count,

  'keyField' => 'transdate',

  'sort'=>array(

    'defaultOrder'=>'transdate, accountname',

  ),

  'pagination'=>array(

  'pageSize'=>50,

  ),

));*/

Second, I found lots of post said I should assign “name” value. But it still doesn’t work.




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

	'id'=>'checking-account-grid',

	'dataProvider'=>$model->search(),

	//'filter'=>$model,

	'columns'=>array(

		array( 

            'header'=> 'Date',

            'name' => '$data[\'transdate\']',

            'value' => '$data[\'transdate\']',

            'type'  => 'raw',

	    'htmlOptions' => array('style' => 'white-space: nowrap;')

        ),

Is there any suggestion for it?

2

Try the following:




  'sort'=>array(

    'defaultOrder'=>'transdate, accountname',

    'attributes'=>array('transdate', 'accountname')

  ),
3

Thanks, but it still doesn’t work.

4

You have to change the name property as follow:

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

    'id'=>'checking-account-grid',


    'dataProvider'=>$model->search(),


    //'filter'=>$model,


    'columns'=>array(


            array( 


        'header'=> 'Date',


        'name' => 'transdate',


        'value' => '$data[\'transdate\']',


        'type'  => 'raw',


        'htmlOptions' => array('style' => 'white-space: nowrap;')


    ),
5

Thanks, but it still doesn’t work. And this is my SQL, is it correct? All functions are correct except sort.




$sql = 'SELECT t.transdate transdate, ... FROM table1';

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

$dataProvider=new CSqlDataProvider($sql, array(

			'totalItemCount'=>$count,

			'keyField' => 'transdate',

			'sort'=>array(

				'defaultOrder'=>'transdate, accountname',

				'attributes'=>array('transdate', 'accountname')

			),

			'pagination'=>array(

				'pageSize'=>50,

			),

		));
6

The code should look something like the following:




// CONTROLLER:

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


// The table1 must have transdate and accountname fields and transdate is the PK

$sql = 'SELECT * FROM table1';

$sqlDataProvider = new CSqlDataProvider($sql, array(

        'totalItemCount'=>$count,

        'keyField'=>'transdate',

        'sort'=>array(

                'defaultOrder'=>'transdate, accountname',

                'attributes'=>array('transdate', 'accountname')

        ),

        'pagination'=>array(

                'pageSize'=>50,

        ),

));


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

    'sqlDataProvider'=>$sqlDataProvider,

));




// VIEW (index.php): 

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

    'id'=>'data-grid',

    'dataProvider'=>$sqlDataProvider,

    'columns'=>array(

        array( 

            'header'=>'Date',

            'name'=>'transdate',

            'value'=>'$data["transdate"]'

        ),

        array( 

            'header'=> 'Account',

            'name'=>'accountname',

            'value'=>'$data["accountname"]'

        ),

     )

));
7

I put the SQL command in model’s search(), maybe it’s the root cause. I’ll try it, thanks.