Filter in money column with CgridView

Hello Everyone:

So, I’ve been wondering how can I filter a column with money values in a CgridView widget. Let’s say I have money values in my database and I have something like “68.0” but I’d like to format those values in my GridView so I put a “formatPrice” function in my Product model like this:




	public function formatPrice($price) {

		if ($price != "") {

			$money = number_format($price,2);

			$money = "$ ".$money;

		}

		else {

			$money = "";

		}

		

		return ($money);

	}	

}



I then create a column in my "admin" view for my cGridView like this:




		array(

			'type'		=> 	'text',

			'header'	=>	'Precio Unitario',

			'name'		=>	'pro_precio',

			'value'		=>	'$data->formatPrice($data->pro_precio)',

			'htmlOptions'=>	array('class'=>'column_money'),

		),		



So, now I have a problem with the filter for this customized column. I tried using the following code in my search function inside the model:




		$criteria->compare('pro_precio',$this->formatPrice($this->pro_precio));



The view will be empty always if I type a value in the filter box. I know I’m probably missing something really basic here but spare my life and help me please!! hehe

Greetings and thanks in advance.

The model instance you are calling search() on is the very model instance that holds the posted filter values. You rather should call some unFormatPrice() method for matching to the DB type.




  $criteria->compare('pro_precio',$this->unFormatPrice($this->pro_precio));



Greetings:

Thank you for your help tri. I did as you suggested, created an "unFormatPrice" function to remove the "$" and used the criteria like this:


$criteria->compare('pro_precio',$this->unFormatPrice($this->pro_precio));

Unfortunately, no success with that, the table is still empty when I put any value on that column’s filter.

Do you have any idea why this could be happening?

Depending on how you intend to use this filter you may consider to set partialMatch to true. Did you succeed with other filter columns? If in doubt, I think it’s a good idea to try out filtering an integer field first, before continuing with the money type (I’ve not tried something similar myself).

/Tommy

It works now! I just did some changes for criteria like this:


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

Also this works:


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

By adding “true” it now looks for any substring containing the value and the filter is correct. However, in the second case it wasn’t necessary to user “unFormatPrice”. I’m kind of lost here, in the compare function, the first value is the value in the column or the value in the database?

Thanks for your help again.

Er, hold on a second… !

Yii already has a currency formatter, hasn’t it?

Just use a filter on the original data, and use the currency formatter to present it.

Wouldn’t that do the job?

Link to Currency topics

You are right, but what I meant with "unFormat…" was to remove any formatting (as in the grid fields) the user might enter in the filter field. So I can see a possibility for conditioning the value before calling compare().

/Tommy

I get it now! :)

The dropdown select list would of course be an array of formatted and raw values. That solves the problem.

What you need to do is add a ‘filter’ property to your grid.

And code a function in your model/controller:


function getCurrencyFilter(){

foreach($stuffs as $stuff) {

$array[Yii::formatCurrency($stuff->raw_currency, USD)] = $stuff->raw_currency;

}

return $array;

}

And in the field of the grid:


'filter' => $this->currencyFilter(),

Here’s a link:

Relevant filter topic

Nice! I’ll try that.

Thank you so much jacmoe and tri for your answers.

P.S. I like Yii.

Greetings