Sort Custom Column

Hi everybody !

I know similar questions has been already posted but I can’t find my solution…

My goal is to have a sortable custom column. (build from a function in my model.)

The column is correctly filled, the header is clickable but I have an sql exception when I try to sort.

here is my code :

So I have a CGridView in my view :




$dataprovider = $parametre->searchByUser($user->Id_User,'validateur');


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

		'id'=>'ValidIndice-grid',

		'dataProvider'=>$dataprovider,

		'rowCssClassExpression'=>'$data->color',

		'selectableRows'=>1,

		'selectionChanged'=>'function(id){ location.href = "'.$this->createUrl('Parametre/view').'/id/"+$.fn.yiiGridView.getSelection(id);}', //array('idEvent'=>$evenement->Id_Evenements),

		'htmlOptions'=>array('class'=>'grid-view mgrid_table'),


		'columns'=>array(


				array(

						'name'=>'Libelle',

						'header'=>'Libellé du paramètre',

						'value'=>'$data->Libelle'

				),

				array(						

						'header'=>'Nouvelle donnée?',

						'name'=>'NewValue',

						'value'=>'$data->NewValue',					

			

		                ),

));

?>



In my model :





public  function searchByUser($idUser,$role)

	{

		$criteria=new CDbCriteria;

		$criteria->alias = 'Parametre';

		

		$criteria->join='LEFT JOIN Droit ON Droit.Id_Parametre  = Parametre.Id_Parametre';


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

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


	         ... Other criteria ...	


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


		$sort = new CSort();

		$sort->attributes = array(

				'NewValue'=>array(

						'asc'=>'NewValue ASC',

						'desc'=>'NewValue DESC',

				),

				'*', // this adds all of the other columns as sortable

		);

		

		return new CActiveDataProvider($this, array(

				'criteria'=>$criteria,

				'sort'=>$sort,

		));

	}



and in the same model My function to see if I have new value :




	public function getNewValue() //mean => hasNewValue()

	{	

		$condition = 'Id_Parametre=:IdParam AND boolValide != 1';

		$params =  array(':IdParam'=>$this->Id_Parametre);

		if(Valeur::model()->exists($condition,$params)!=null)

		{

			return true;

		}	

		else 

		{

			return false;

		}

	}



Whith this, my CGridView is correctly filled, and the header of my "newValue" column is clickable.

But when I click on it I have an sql exception because he don’t find the column ‘newValue’.

It’s Logic, this column doesn’t exist because it is a fonction (getNewValue) …

what’s wrong? thanks in advance !

here is the error if it is needed :

Bonjour Vincent

‘NewValue’ is not a column in your table, so you can not use ‘compare’ on it in the ‘search()’ method.

So inside the ‘search()’ method, you have to test if ‘newValue’ is set and if it is, adap the search to a search with values in the SQL table.

For instance, I have a table with a field ‘device_identifier’ which is a complex serial number. I represent it to the user in a more readable way and I call this virtual attribute ‘textualIdentifier’. So when ‘textualIdentifier’ is set during the search, I add the appropriate SQL comparison:




		if("{$this->textualIdentifier}"!=='') {

			$criteria->compare($this->tableAlias.'.device_identifier',$this->textualIdentifier,true);

		}

		//


		$sort=array(

		        'defaultOrder'=>'device_identifier DESC',

		        'attributes'=>array(

		                'textualIdentifier' => array(

		                        'asc' => 'device_identifier',

		                        'desc' =>'device_identifier DESC',

		                ),

		                '*',

		        ),

		);

		... 



You can see that in ‘$sort’, the sort order for ‘textualIdentifier’ is set as a sort on ‘device_identifier’.

For info, I have the following getter and setter for ‘textualIdentifer’ which is also declared as a ‘safe’ attribute.




	/*

	 * Internal shadow property for textualIdentifier in case of search scenario.

	 */

	private $_textualIdentifier;

	/**

	 * Setter for the textualIdentifier usefull in search scenario

	 */

	public function setTextualIdentifier($value){

	    if($this->getScenario()!=='search') {

		    $this->device_identifier=$this->convertToInternalIdentifier($value, $this->device_type_id);

		    $this->_textualIdentifier=$value;

		} else {

			$this->_textualIdentifier=$value;

		}

	}

	/**

	 * Provides the identifier in a Human Readable form.

	 *

	 * @return $identifier

	 */

	public function getTextualIdentifier(){

		if($this->getScenario()!=='search') {

			return self::convertToTextualIdentifier($this->device_identifier,$this->device_type_id);

		} else {

			return $this->_textualIdentifier;

		}

	}



You can see that this code makes a difference between the ‘search’ scenario and other scenarios. I can also use this field to set the serial number from the human readable format because in the ‘setter’ I make sure that the ‘device_identifier’ field is updated when the search scenario is not active.