Help sorting CGridView

I have two tables:

tbl_song

id

title

tbl_song_history

id

song_id

play_date

I’ve created the basic Model and CRUD classes. I want to be able to sort the Song model with the columns:

id, title, playCount, and lastPlayed

The relationship one tbl_song can have many tbl_song_history records.

I’ve defined my Song.php




function relations() {     

    return array(          

      'playCount'=>array(self::STAT,'SongHistory','song_id'),     

    ); 

}



So in my admin.php view I simple have this:




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

        'id'=>'song-grid',

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

        'filter'=>$model,

        'columns'=>array(

                'id',

                'title',

                'playCount'

         )

         )

       );

?>



However my playCount column is not sortable nor searchable in my CGridView. Is this the right approach or should I do something different?

bump

I discovered that my join that produces non-existent records is causing the problem. There are some songs that have no history record so with the following relation:




                return array(

                        'history'=>array(self::HAS_ONE,'SongHistory','song_id',

                                'order'=>'play_date desc',

                        )

                );




My view of:




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

        'id'=>'song-grid',

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

        'filter'=>$model,

        'columns'=>array(

                'id',

                'title',


                array(

                        'name'=>'history.play_date',

                        'value'=>'$data->history->play_date',

                ),

                ....



Would produce an error of:




Trying to get property of non-object



I had to use this simpler syntax:




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

        'id'=>'song-grid',

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

        'filter'=>$model,

        'columns'=>array(

                'id',

                'title',

                'history.play_date:text:Last Play',

                ...



That would work however my sort does not work at all. I’ve tried the following in my Song::search()




                $sort = new CSort();

                $sort->attributes = array(

                        '*',

                        'history.play_date'=>array(

                                'asc'=>'history.play_date',

                                'desc'=>'history.play_date desc',

                        ),

                );




I am able to click on the ‘Play Date’ column but it does not sort at all. The ordering of the rows remains the same.

I’m using Yii 1.1.13

get rid of order by in your searching criteria, then it will work. otherwise the dataprovider will always return the same result order by same criteria

Do you mean remove the order by in my relations definition?




                return array(

                        'history'=>array(self::HAS_ONE,'SongHistory','song_id',

                                'select'=>'max(play_date) as play_date',

                                //'order'=>'play_date desc',

                        )

                );



That doesn’t work.

Also I noticed that when I click on the clickable ‘Play Date’ column I don’t see the up or down arrows like I do on the other columns.

look this http://www.yiiframework.com/forum/index.php?/topic/9714-sort-user-defined-column-in-cgridview/page__view__findpost__p__48109

Thanks Horacio. In the example you cited:





        public function search()

        {




  $sort = new CSort();

  $sort->attributes = array(


   'date'=>array(

      'asc'=>'t.date',

      'desc'=>'t.date desc',

    ),


   'name'=>array(

      'asc'=>'t.name',

      'desc'=>'t.name desc',

    ),


   'id_sector'=>array(

      'asc'=>'sector.descripcion',

      'desc'=>'sector.descripcion desc',

    ),

);




Where is t.date and sector.description defined? The aliases of ‘t’ and ‘sector’ are defined where exactly?

t is the alias defult of table

date is a field of the table

sector is a relation

all defined in the model

in your problem

"date" is your "title"

"sector" is your "history"

Horacio,

First of all thanks so much for your time on this!

I’m able to see the values of the play_date in each row and in some rows blank when songs have no play history. However I am still not able to sort. Here’s what I have

/views/song/admin.php




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

        'id'=>'song-grid',

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

        'filter'=>$model,

        'columns'=>array(

                'id',

                'title',

                'filename',

                'created',

                'created_by',

                'last_modified',

                'history.play_date:text:Last Play',

                ....

            



/models/Song.php




public function relations()

        {

                // NOTE: you may need to adjust the relation name and the related

                // class name for the relations automatically generated below.

                return array(

                        'history'=>array(self::HAS_ONE,'SongHistory','song_id',

                                'select'=>'max(play_date) as play_date',

                        ),

...





function search() {

...

                $sort = new CSort();

                $sort->attributes = array(

                        '*',

                        'history.play_date'=>array(

                                'asc'=>'history.play_date',

                                'desc'=>'history.play_date desc',

                        ),

                );


                return new CActiveDataProvider(get_class($this), array(

                        'criteria'=>$criteria,

                        'sort'=>$sort,

                ));




The sort and search is what I’m still not able to get working. What am I missing?

hi, sorry for delay

try




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

        'id'=>'song-grid',

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

        'filter'=>$model,

        'columns'=>array(

                'id',

                'title',

                'filename',

                'created',

                'created_by',

                'last_modified',

                //'history.play_date:text:Last Play',

                 array('name'=>'history.play_date','sortable'=>'true','value'=>'$data->history->play_date'),

                ....

            



have enabled the trace?

put here the sql generated

Using the following for my column definition in admin.php works




           'history.play_date:text:Last Play',



Using what you’ve mentioned:




                 array('name'=>'history.play_date','sortable'=>'true','value'=>'$data->history->play_date'),



Does not work because there are some rows which have no history record so trying to get the play_date column produces a error where it says, "Trying to get property of non-object"

So I figured out the following works:




                 array('name'=>'history.play_date','sortable'=>'true','value'=>'($data->history) ? $data->history->play_date : "never played"'),



That produces essentially the same result as:




           'history.play_date:text:Last Play',



but with a default value when there are no records found

As I’ve mentioned before I can get it to display correctly, paginate correctly with values but it just does not sort. When I hover over the play date column and click on the column header the sort does not toggle from ascending to descending. Is this a bug on not being able to sort on a relational column?

put the log of sql

(enable log trace first)

Here’s what it looks like when I have




 array('name'=>'history.play_date','sortable'=>'true','value'=>'($data->history) ? $data->history->play_date : "never played"'),



Here’s the stack trace when I have




 array('name'=>'history.play_date','sortable'=>'true','value'=>'$data->history->play_date'),



2010/09/03 08:43:57 [error] [php] Trying to get property of non-object (/opt/yii-1.1.3.r2247/framework/base/CComponent.php(616) : eval()'d code:1)

Stack trace:

#0 /opt/yii-1.1.3.r2247/framework/zii/widgets/grid/CGridColumn.php(135): CDataColumn->renderDataCellContent()

#1 /opt/yii-1.1.3.r2247/framework/zii/widgets/grid/CGridView.php(457): CDataColumn->renderDataCell()

#2 /opt/yii-1.1.3.r2247/framework/zii/widgets/grid/CGridView.php(430): CGridView->renderTableRow()

#3 /opt/yii-1.1.3.r2247/framework/zii/widgets/grid/CGridView.php(343): CGridView->renderTableBody()

#4 /opt/yii-1.1.3.r2247/framework/zii/widgets/CBaseListView.php(158): CGridView->renderItems()

#5 unknown(0): CGridView->renderSection()

#6 /opt/yii-1.1.3.r2247/framework/zii/widgets/CBaseListView.php(141): preg_replace_callback()

#7 /opt/yii-1.1.3.r2247/framework/zii/widgets/CBaseListView.php(127): CGridView->renderContent()

#8 /opt/yii-1.1.3.r2247/framework/web/CBaseController.php(166): CGridView->run()

#9 /home/patrick/websites/emodmail.selfip.org/m3/test/protected/views/song/admin.php(77): SongController->widget()

#10 /opt/yii-1.1.3.r2247/framework/web/CBaseController.php(119): require()

#11 /opt/yii-1.1.3.r2247/framework/web/CBaseController.php(88): SongController->renderInternal()

#12 /opt/yii-1.1.3.r2247/framework/web/CController.php(798): SongController->renderFile()

#13 /opt/yii-1.1.3.r2247/framework/web/CController.php(739): SongController->renderPartial()

#14 /home/patrick/websites/emodmail.selfip.org/m3/test/protected/controllers/SongController.php(186): SongController->render()

#15 /opt/yii-1.1.3.r2247/framework/web/actions/CInlineAction.php(32): SongController->actionAdmin()

#16 /opt/yii-1.1.3.r2247/framework/web/CController.php(300): CInlineAction->run()

#17 /opt/yii-1.1.3.r2247/framework/web/filters/CFilterChain.php(129): SongController->runAction()

#18 /opt/yii-1.1.3.r2247/framework/web/filters/CFilter.php(41): CFilterChain->run()

#19 /opt/yii-1.1.3.r2247/framework/web/CController.php(1049): CAccessControlFilter->filter()

#20 /opt/yii-1.1.3.r2247/framework/web/filters/CInlineFilter.php(59): SongController->filterAccessControl()

#21 /opt/yii-1.1.3.r2247/framework/web/filters/CFilterChain.php(126): CInlineFilter->filter()

#22 /opt/yii-1.1.3.r2247/framework/web/CController.php(283): CFilterChain->run()

#23 /opt/yii-1.1.3.r2247/framework/web/CController.php(257): SongController->runActionWithFilters()

#24 /opt/yii-1.1.3.r2247/framework/web/CWebApplication.php(324): SongController->run()

#25 /opt/yii-1.1.3.r2247/framework/web/CWebApplication.php(121): CWebApplication->runController()

#26 /opt/yii-1.1.3.r2247/framework/base/CApplication.php(135): CWebApplication->processRequest()

#27 /home/patrick/websites/emodmail.selfip.org/m3/test/index.php(13): CWebApplication->run()

REQUEST_URI=/m3/test/index.php?r=song/admin

in /opt/yii-1.1.3.r2247/framework/base/CComponent.php(616) : eval()'d code (1)

in /opt/yii-1.1.3.r2247/framework/zii/widgets/grid/CDataColumn.php (132)

in /opt/yii-1.1.3.r2247/framework/zii/widgets/grid/CGridColumn.php (135)

put




  error_reporting(E_ALL ^ E_NOTICE);



and

put this in config.php




		'log'=>array(

			'class'=>'CLogRouter',

			'routes'=>array(

				array(

					'class'=>'CFileLogRoute',

					'levels'=>'error, warning,trace',

				),

				// uncomment the following to show log messages on web pages

				

				array(

					'class'=>'CWebLogRoute',

				),


			),

		),




and see the sql generated

I added error_reporting(E_ALL ^ E_NOTICE); in SongController::actionIndex() and it now allows:


'value'=>'$data->history->play_date',

The SQL generated is:


 SELECT max(play_date) as play_date, `history`.`id` AS `t1_c0` FROM `song_history` `history`  WHERE (`history`.`song_id`=:ypl0)

that looks correct. But why is still not sorting? Is it because in my CSort I’ve defined it with “history.play_date” instead of just “play_date” ?

look this http://www.yiiframework.com/forum/index.php?/topic/10510-beginner-question-with-many-many-and-cdbcriteria/

use together

persevere and succeed!!!

look this (since 1.1.4)

http://www.yiiframework.com/doc/api/CDbCriteria#together-detail