CGridView sort columns

I’ve been poking around the forums and I can find a solution that I can get to work.

Here’s what my CGridView looks like when I call it:




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

	'dataProvider'=>$dataProvider,

	'columns'=>array(

		array(

			'class'=>'CLinkColumn',

			'header'=>'Print Job Id',

			'labelExpression'=>'$data->printjob_id',

			'urlExpression'=>'Yii::app()->createUrl("printjob/update",array("id"=>$data->printjob_id))',

		),

		array(

			'name'=>'Job Type',

			'value'=>'$data->job_type->job_type_name',

		),

		array(

			'class'=>'CLinkColumn',

			'header'=>'Printjob Title',

			'labelExpression'=>'$data->printjob_title',

			'urlExpression'=>'Yii::app()->createUrl("printjob/update",array("id"=>$data->printjob_id))',

		),

		array(

			'name'=>'Customer',

			'value'=>'$data->customer->customer_name',

		),

		array(

			'name'=>'School',

			'value'=>'$data->school->school_name',

		),

		'submitted_date',

		'requested_date',

	),

)); ?>



By default, there appears to be some sorting going on with submitted/requested date, but they really aren’t working right. How would I go about getting this to allow sorting on ALL colums here? There’s three different column types, the LinkColumns, name/value columns and standard fields.

Thanks,

Jaz

update/bump

The only success I’ve had with sorting the related, name/value columns has been by doing the following:




//by changing:

array(

  'name'=>'Customer',

  'value'=>'$data->customer->customer_name',

),

//to this:

array(

  'name'=>'customer_id',

  'value'=>'$data->customer->customer_name',

),



If I do the change above, it grabs the column label from the model attributeLabels. But the problem is that it now sorts by the actual id/key field and not the customer_name field, so it’s totally useless. How do I get it to use the related field for sorting?

Also, I’ve got the CLinkColumn fields that need to be sorted. In this example, they are not related fields, but I do have another situation that will call for this.

I’m making progress, so I wanted to share.

To sort a CGridView column of related values, the following works:

in the view




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

	'dataProvider'=>$dataProvider,

	'columns'=>array(

		array(

			'name'=>'customer',

			'value'=>'$data->customer->customer_name',

		),

		array(

			'name'=>'school',

			'value'=>'$data->school->school_name',

		),

		'submitted_date',

		'requested_date',

	),

)); ?>



in the controller, set up the dataProvider using CSort




public function actionIndex(){

  $sort = new CSort();

  $sort->attributes = array(

    'customer'=>array(

      'asc'=>'customer.customer_name',

      'desc'=>'customer.customer_name desc',

    ),

    'school'=>array(

      'asc'=>'school.school_name',

      'desc'=>'school.school_name desc',

    ),

    'submitted_date',

    'requested_date',

  );

  

  $dataProvider=new CActiveDataProvider('Printjob', array(

    'criteria'=>array(

      'alias'=>'pj',

      'join'=>'inner join customer on customer.customer_id=pj.customer_id inner join school on school.school_id=pj.school_id',

    ),

    'sort'=>$sort,

    'pagination'=>array(

      'pageSize'=>self::PAGE_SIZE,

    ),

  ));

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

    'dataProvider'=>$dataProvider,

  ));



My problem this whole time was that I was trying to use the CSort, but it kept failing or giving me an error about not recognizing the field “customer.customer_name”, so it finally occurred to me that it wasn’t being joined up properly. I tried adding the join clause to criteria, but since the field names were the same in both tables (probably an issue specific to me, but possibly others might run into this) I had to add the alias parameter so that in the joins I could properly refer to each table involved.

Now, if I could just get the CLinkColumns to be sortable I’d be all set.

Hi,

well, i haven’t had any problems sorting the grid with related records when simply using




'school.school_name:text:School',



instead of


array(

'name'=>'School',

'value'=>'$data->school->school_name',

),



regards

Yoshi, I haven’t tried the sort technique you show above, but I am not having a problem sorting the related columns anymore. The last bit of code I posted works for that. Though, I wanted to repost this piece of it. I improved it marginally, by simply using the ‘with’ criteria instead of writing out the join explicitly.




  $dataProvider=new CActiveDataProvider('Printjob', array(

    'criteria'=>array(

      'with'=>array('customer','school'),

    ),

    'sort'=>$sort,

    'pagination'=>array(

      'pageSize'=>self::PAGE_SIZE,

    ),

  ));



Also, I took a look at the blog demo and they had a CGridView with links in the columns that are also sortable. Interestingly, they are not using the CLinkColumn type, which I still cannot get to sort myself. The technique they used is something I tried, but without setting the ‘type’=‘raw’ parameter, it was coming out as text and not a link.

The link part of this column I’m showing can be done different ways. I used the way the CLinkColumn had been set up.




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

  'dataProvider'=>$dataProvider,

  'columns'=>array(

    /* columns */

    array(

      'name'=>'printjob_title',

      'type'=>'raw',

      'value'=>'CHtml::link(CHtml::encode($data->printjob_title),Yii::app()->createUrl("printjob/update",array("id"=>$data->printjob_id)))',

    ),

  ),

));



Jaz Manister, thanx for solution, it helpded me solve the same problem!. :mellow:

Removed post because I figured out my problem. I was using double quotes around the $data->thing->whatever field in the widget, rather than single quotes. This was attempting to evaluate the $data variable right away, of course, and leaving me with an empty column. Jaz Manister’s solution worked for me as well, once I did it right.

Hi, please help me.

I just can’t get it work :(

this is my Vanzari.php model’s relations:




public function relations()

	{

		return array(

			'produs'		=> array(self::BELONGS_TO, 'Produse', 'id_produs'),

			'valoriProdus'	=> array(self::BELONGS_TO, 'ProduseValori', 'id_produs'),

			'factura'		=> array(self::BELONGS_TO, 'Facturi', 'id_factura'),

		);

	}



this is my VanzariController.php actionIndex()




public function actionIndex()

	{

		$sort = new CSort();

		$sort->attributes = array(

			'factura'=>array(

				'asc'=>'factura.nume_factura',

				'desc'=>'factura.nume_factura desc',

			),

			'produs'=>array(

				'asc'=>'produs.nume_produs',

				'desc'=>'produs.nume_produs desc',

			),

			'*',

		);

		

		$dataProvider=new CActiveDataProvider('Vanzari',array(

			'criteria'=>array(

				'with'=>array('factura','produs'),

			),

			'sort'=>$sort,

			'pagination'=>array(

				'pageSize'=>self::PAGE_SIZE,

			),

			));


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

			'dataProvider'=>$dataProvider,

		));

	}



and this is my admin.php’s CGridView:




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

	'id'=>'vanzari-grid',

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

	'filter'=>$model,

	'columns'=>array(

		array(

			'type'=>'raw',

			'name'=>'produs.nume_produs',

			'value'=>'CHtml::link($data->produs->nume_produs, array("view","id"=>$data->id_vanzare))',

			//'value'=>'CHtml::link(CHtml::encode($data->produs->nume_produs),Yii::app()->createUrl("view",array("id"=>$data->id_vanzare)))',

		),

		'creare_produs',

		array(

			'name'=>'cantitate_vanzari',

			'htmlOptions'=>array('width'=>'60px'),

		),

		array(

			'class'=>'CButtonColumn',

			'template'=>'{update}',

			'updateButtonUrl' => 'Yii::app()->controller->createUrl("vanzari/update",array("id"=>$data->id_vanzare, "id_factura"=>$data->id_factura))',

		),

	),

)); ?>



In sort you declared virtual columns "factura" and "produs", to sort on them you need to use it as columns in CGridView

So instead of


 'name'=>'produs.nume_produs',

put


'name'=>'produs',

I also tried this way, it display the results but sort is disabled when mouse over and filter field exists but no matter what i fill in it spins the icon that shows it it "searching" and nothing happens.

Could anyone help me out, please ?

See:

http://www.yiiframework.com/forum/index.php?/topic/15704-filter-on-cgridview-with-cactivedataprovider-based-on-two-table/

Maybe fixed.

Someone should make a wiki out of this one.

I like this:

'criteria'=&gt;array(


  'with'=&gt;array('customer','school'),


),

Need add: ‘with’=>array(‘customer’,‘school’) to run sort with relation

Thanks Jaz Manister

Hello,

I’m needing a hel plz, how i can remove a sort by column in determined column in my grid?

sorry about my english, i don’t pratice much.

good bye

This post has seriously helped me out, cheers to everyone involved. There was one thing though that I have extended on this. I use Yii translation files and needed to obviously make these sortable also. Here is my code for those people trying to do the same. Below we have a solution for sorting relational fields, link fields, translated fields, also working with the ecolumns extension.

My Controller Code




public function actionIndex()

	{

		$sort = new CSort();

		$sort->attributes = array(

	    	Yii::t('app','model.accounts.name')=>array(

	      	'asc'=>'a.name',

	      	'desc'=>'a.name desc',

    		),

    		Yii::t('app','model.accounts.phone')=>array(

	      	'asc'=>'a.phone',

	      	'desc'=>'a.phone desc',

    		),

    		Yii::t('app','model.tickets.date_required')=>array(

	      	'asc'=>'t.date_required',

	      	'desc'=>'t.date_required desc',

    		),

    		Yii::t('app','model.tickets.department_id')=>array(

	      	'asc'=>'t.department_id',

	      	'desc'=>'t.department_id desc',

    		),

    		Yii::t('app','model.tickets.category_id')=>array(

	      	'asc'=>'t.category_id',

	      	'desc'=>'t.category_id desc',

    		),

    		Yii::t('app','model.tickets.status_id')=>array(

	      	'asc'=>'t.status_id',

	      	'desc'=>'t.status_id desc',

    		),


    		'id',

			'problem_description',

			'problem_solution',

			'internal_notes',

			'department_id',

			'subject',

	  	);

		

		$dataProvider=new CActiveDataProvider('Tickets', array(

			 'criteria'=>array(

		      'alias'=>'t',

		      'join'=>'inner join accounts a on a.id = t.account_id',

    		),

   			'sort'=>$sort,

    		'pagination'=>array(

      		'pageSize'=>10,

    		),

 		));


		if(isset($_GET['Tickets']))

			$model->attributes=$_GET['Tickets'];


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

			'model'=>$model,'dataProvider'=>$dataProvider

		));

	}



My View Code




<?php 


$dialog = $this->widget('ext.ecolumns.EColumnsDialog', array(

   'options'=>array(

        'title' => 'Layout settings',

        'autoOpen' => false,

        'show' =>  'fade',

        'hide' =>  'fade',

    ),

   'htmlOptions' => array('style' => 'display: none'), //disable flush of dialog content

   'ecolumns' => array(

        'gridId' => 'tickets-grid', //id of related grid

        'storage' => 'session',  //where to store settings: 'db', 'session', 'cookie'

        'model' => $dataProvider, //model is used to get attribute labels

    	'columns'=>array(

			'id',

			'subject',

			 array(

					      'name'=>Yii::t('app','model.accounts.name'),

					      'type'=>'raw',

					      'value'=>'CHtml::link(CHtml::encode(Custom::truncateText($data->rl_account->name, 30)),Yii::app()->createUrl("Accounts/view",array("id"=>$data->account_id)))',

			),

			

			array(

				'name'=>Yii::t('app','model.accounts.phone'),

				'value'=>'$data->rl_account->phone',

			),


			array(

				'name'=>Yii::t('app','model.tickets.date_required'),

				'value'=>'Custom::reformatDate($data->date_required)',

			),

			//'category_id',

			array(

				'name'=>Yii::t('app','model.tickets.department_id'),

				'value'=>'$data->rl_category->text',

			),

			array(

				'name'=>Yii::t('app','model.tickets.category_id'),

				'value'=>'$data->rl_category->text',

			),

			array(

				'name'=>Yii::t('app','model.tickets.status_id'),

				'value'=>'$data->rl_status->text',

			),





			/*

			'status_id',

			'problem_description',

			'problem_solution',

			'internal_notes',

			'department_id',

			'subject',

			*/

			array(

				'class'=>'CButtonColumn',

				'template' => '{view} {update}',

				'buttons'=>array(

	          	'view'=>array(

	          			'visible'=>'Users::isAllowedActionLink(array("Tickets", "view"))',

	        		),

	          	'update'=>array(

	          			'visible'=>'Users::isAllowedActionLink(array("Tickets", "update"))',

					),								                		

	        	),

			),

		),

	 )

	));





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

'id'=>'tickets-grid',

'dataProvider'=>$dataProvider,

'summaryCssClass'=>'right block clear',

'pagerCssClass'=>'right block clear',

'template'=>$dialog->link().'{items}{summary}{pager}',

'cssFile'=> Yii::app()->request->baseUrl . '/css/foundation.css',

'columns'=>$dialog->columns(),

)); 

?>