Error On Filter Column In Cgridview With Activerecord Relations

Yii 1.1 General Discussion
1

Hello friends,

I have some problem with CGridView and CActiveRecord with relations… >:(

I have a table (Categoria) with the following columns:




 * @property integer $categoriaId

 * @property string $descrizione

 * @property integer $categoriaOwnerId

 * @property string $createTime

 * ...

 * ...

categoriaId is the PK, descrizione is the category description, and categoriaOwnerId is FK on the same Categoria table, because it is the id of the parent category (to manage the hierarchical structure of categories in the same auto-refered table).

These relations in the model Categoria.php are:


	public function relations()

	{

		return array(

			...,

			'categoriaOwner' => array(self::BELONGS_TO, 'Categoria', 'categoriaOwnerId'),

			'categorias' => array(self::HAS_MANY, 'Categoria', 'categoriaOwnerId'),

                        ....,

		);

	}

Now, I need to expose in CGridView, for each row (category), in addition to the id and description of the current category, also the id and description of the parent category.

So I added this property on the Categoria.php model:


public $descrizioneOwner;

And, in the same model I changed the search() method to manage criteria for categoriaOwner relation. The complete new search() method is following:


	public function search()

	{

		$criteria=new CDbCriteria;


		$criteria->compare('categoriaId',$this->categoriaId);

		$criteria->compare('categoriaOwnerId',$this->categoriaOwnerId);

		$criteria->compare('descrizione',$this->descrizione,true);

		$criteria->compare('createTime',$this->createTime,true);

		$criteria->compare('updateTime',$this->updateTime,true);

		$criteria->compare('createUser',$this->createUser,true);

		$criteria->compare('updateUser',$this->updateUser,true);


		$criteria->with=array('categoriaOwner');

		$criteria->together=false;

		$criteria->compare('categoriaOwner.descrizione', $this->descrizioneOwner, true);

 

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

Then, in the admin.php view I changed the CGridView properties to manage new categoriaOwner relation:


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

	'id'=>'categoria-grid',

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

	'filter'=>$model,

	'enableSorting'=>true,

	'columns'=>array(

		'categoriaId',

		'descrizione',

		'categoriaOwnerId',

		array(

			'value'=>'($data->categoriaOwner == null ? "" : $data->categoriaOwner->descrizione)',

			'name'=>'descrizioneOwner', //<-- this must be defined so the ajax filter to show up

			'sortable'=>true,

		),

		'...', //other columns...

		'...',

		array(

			'class'=>'CButtonColumn',

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

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

		),

	),

)); ?>

With these variants I am in effect able to expose in the datagrid also the description column of the parent category (the descrizioneOwner column), but…

the problem is that the filter and sort functionality do not work or go on error! :angry: In detail:

  1. only the filter on the new column descrizioneOwner works properly;

2. the filter applied on any other column it causes an sql error that I highlight in attached image file, and that speaking of ambiguous column names in the where clause >:( ;

3. the sort functionality on the new column descrizioneOwner don’t work (the link is not active on the corresponding heading label);

Where did I go wrong?

Can you help me to solve the problems 2 and 3?

1000 Thanks in advance.

2

Hi all,

is there anyone who can help me on this issue?

Thanks.

3

Check this article for some ideas - http://www.yiiframework.com/wiki/281/searching-and-sorting-by-related-model-in-cgridview

4

Hi Maurizio,

Thank you for your reply, I applied the instructions of the your linked article.

Now: the sort problem (# 3) is solved :rolleyes: , but unfortunately remains the filter problem (# 2). :angry:

Now, the new situation is the following:

  1. only the filter on the new column descrizioneOwner (from relation categoriaOwner.descrizione) works properly;

2. the filter applied on any other CGridView column it causes an sql error that I highlight in attached image file in the first post, and that speaking of ambiguous column names (?!?! )in the where clause >:( ;

Can you think of any other solution?

Thank you.

5

You have to disambiguate the column name by adding "t." for the main table.


	public function search()

	{

		$criteria=new CDbCriteria;


		$criteria->compare('t.categoriaId',$this->categoriaId);

		...


		$criteria->with=array('categoriaOwner');

		// $criteria->together=false;

		$criteria->compare('categoriaOwner.descrizione', $this->descrizioneOwner, true);

 

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}
6

Thank you very much, now also all the filter works fine in CgridView.

Solved!

Good. :rolleyes: