Yii Framework Forum: Help sorting CGridView - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Help sorting CGridView sorting table columns is easy but relational columns is hard Rate Topic: -----

#1 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 31 August 2010 - 05:05 PM

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?
0

#2 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 01 September 2010 - 01:58 AM

bump
0

#3 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 01 September 2010 - 11:15 AM

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
0

#4 User is offline   xzhang 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 25
  • Joined: 09-July 09

Posted 01 September 2010 - 11:28 AM

View PostPatrick L, on 01 September 2010 - 11:15 AM, said:

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
0

#5 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 01 September 2010 - 11:51 AM

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.
0

#6 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 01 September 2010 - 11:52 AM

look this http://www.yiiframew...dpost__p__48109
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#7 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 01 September 2010 - 11:57 AM

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?
0

#8 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 01 September 2010 - 01:01 PM

View PostPatrick L, on 01 September 2010 - 11:57 AM, said:

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"
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#9 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 01 September 2010 - 02:35 PM

View PostHoracio Segura, on 01 September 2010 - 01:01 PM, said:

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?
0

#10 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 03 September 2010 - 05:46 AM

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
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#11 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 03 September 2010 - 10:14 AM

View PostHoracio Segura, on 03 September 2010 - 05:46 AM, said:

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?
0

#12 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 03 September 2010 - 10:31 AM

put the log of sql

(enable log trace first)
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#13 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 03 September 2010 - 10:44 AM

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


Posted Image

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

#14 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 03 September 2010 - 11:09 AM

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
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#15 User is offline   Patrick L 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 17
  • Joined: 31-August 10

Posted 03 September 2010 - 11:46 AM

View PostHoracio Segura, on 03 September 2010 - 11:09 AM, said:

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" ?
0

#16 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 06 September 2010 - 06:03 AM

look this http://www.yiiframew...nd-cdbcriteria/

use together

persevere and succeed!!!
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

#17 User is offline   gallego123 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 535
  • Joined: 11-August 09
  • Location:Argentina

Posted 07 September 2010 - 03:12 PM

look this (since 1.1.4)
http://www.yiiframew...together-detail
KISS - Keep It Simple Stupid
ASAP-As Soon As Possible
http://www.yiiframew...oc/cookbook/71/
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users