Hi
I’m trying to filter results for one of our reports.
<?php
...
public $financial_year;
public function rules()
{
return array(
array('...
financial_year',
'safe', 'on'=>'search'),
);
}
public function search2()
{
$criteria=new CDbCriteria;
$criteria->select= new CDbExpression("
CASE WHEN extract(month from date_created) >= 7
THEN extract(year from date_created) ||'-'|| extract(year from date_created)+1
ELSE extract(year from date_created)-1 ||'-'|| extract(year from date_created)
END financial_year,
gang_id,
count(id) as count");
if(!empty($this->financial_year))
{
$criteria->condition ="CASE WHEN extract(month from date_created) >= 7
THEN extract(year from date_created) ||'-'|| extract(year from date_created)+1
ELSE extract(year from date_created)-1 ||'-'|| extract(year from date_created)
END = '$this->financial_year' ";
}
$criteria->group = 'financial_year, gang_id';
$criteria->compare('gang_id',$this->gang_id);
return new CActiveDataProvider($this, array(
'criteria'=>$criteria,
'sort'=>array(
'defaultOrder'=>'financial_year DESC, gang_id'),
'pagination'=>array(
'pageSize'=>20,
),
));
}
...
?>
With the above, I am able to filter my result by gang_id but I can’t seem to for financial_year, can someone advise if there’s a better approach to perform these type of queries, since I’ll be using CGridView widget to filter I can’t use CSqlDataProvider.
Thanks!