need help CDbCriteria + CGridView

Hi,

I need some help - I’m new in Yii…

I’ve tried create a CDbCriteria, because i’ve would like to got back special data result from DB. So i’ve made a new method in my model.

My problem is that the result of the query is two AS (alias) record, not a normal field in the DB!

I’ve solved this problem - added with two public variable to the Class.

But afther that i cant sort an filter the result in view (CGridView). The title of the grid ain’t anchor, so i can’t click on it.

Addictionally the “filter” doen’t work :(

Can anyone give me a hint - how to solve my big problem?

By the way - Is there any much normal case to use CDbCriteria and CGridView, when i have to take a special query (Eg. a lots of alias field)???

Thanks advance!!




MODEL:

        ...

	public $searchYear;

	public $searchAmount;

	...

	public function practice()

	{

		$criteria = new CDbCriteria;

		

		$criteria->compare('myDate',$this->myDate,true); // normal DB field

		$criteria->compare('myAmount',$this->myAmount);  // normal DB field

		$criteria->compare('searchYear',$this->searchYear); // JUST AN ALIAS

		$criteria->compare('searchAmount',$this->searchAmount);  // JUST AN ALIAS

		

		$criteria->select = array(

			'DATE_FORMAT(myDate,\'%Y\') as searchYear, sum(myAmount) AS searchAmount'

		);

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

		

	}


++++

CONTROLLER:


	public function actionMypractice()

	{

		$modelSearch = new Practice('practice');

		$modelSearch->unsetAttributes();

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

			$modelSearch->attributes=$_GET['Practice'];

			

		

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

			'modelSearch'=>$modelSearch,

		));				

	}

++++

VIEW:


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

	'id'=>'practice-grid',

	'dataProvider'=>$modelSearch->practice(),

	'filter'=>$modelSearch,

	

	'columns' => array(

		'searchYear',

		'searchAmount',

	),

		

));



I’m not sure but try the following:

Declare your alias variables (=>virtual attributes) as safe normally for the search sceanario. I see that you changed the scenario to practice so you have to declare it as safe for your individual scenario.

For the (initial?) sorting of your grid you have to use CSort which is able to handle virtual attributes.

I found the following similar topic to yours: How to have a sortable virtual attribute in a grid

Thanks Kokomo,

You helped me a lot with CSort!

Now i can sort my “virtual grid” but the filter doesn’t work yet.

I’ve tried almost everything, but i can’t figured out how could i use it with my virtual variables.

The GET goes, but the filter doesn’t work - nothing happened…

Hi, I’m not sure if this is relevant to your problem. I was tearing my hair out trying to make my grid view dropdownlists start_year and start_month to search for a database field start_date. Finally, search() filters the correct results when selecting year and month. Hope this solution helps someone.

Model:




public function search()

{

...

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

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

            $criteria->compare('start_date_month', $this->start_date_month);  // alias


            $criteria->select=array('start_date', 'DATE_FORMAT(start_date, "%Y") as start_date_year', 'DATE_FORMAT(start_date, "%m") as start_date_month', '*');


            if(empty($_REQUEST['Controller']['start_date_year']))  

            {

                $criteria->condition='t.start_date != :start_date';         

                $criteria->params=array(':start_date'=>'""');


                if(!empty($_REQUEST['Controller']['start_date_month']))  

                {

                    $criteria->condition = 'MONTH(t.start_date) between :month_start and :month_end';

                    $criteria->params=array(':month_start'=>$_REQUEST['Controller']['start_date_month'], ':month_end'=>$_REQUEST['Controller']['start_date_month']);

                }

            }

            else

            {

                $month_start=1; //Jan

                $month_end=12; //Dec

                if(empty($_REQUEST['Controller']['start_date_month']))  

                {

                    $criteria->condition = 't.start_date != "" or t.start_date IS NOT NULL';

                }

                else

                {

                    $month_start=$_REQUEST['Controller']['start_date_month'];

                    $month_end=$_REQUEST['Controller']['start_date_month'];

                }

                $criteria->condition = 't.start_date >= :start_date_first and t.start_date <= :start_date_last';    

                $criteria->params=array(':start_date_first'=>$this->getDBDateFirstSecond($_REQUEST['Controller']['start_date_year'], $month_start), ':start_date_last'=>$this->getDBDateLastSecond($_REQUEST['Controller']['start_date_year'], $month_end));

            }


...

}



getDBDateFirstSecond() returns date as eg. 2012-04-01 00:00:00

getDBDateLastSecond() returns date as eg. 2012-04-31 23:59:59

Grid View:





$criteria = new CDbCriteria;

$criteria->select=array('DATE_FORMAT(start_date, "%Y") as start_date_year', 'DATE_FORMAT(start_date, "%m") as start_date_month', '*');


...


                array('name'=>'start_date_year', 'header'=>Yii::t('app', $model->getAttributeLabel('start_date_year')),

		'filter' =>

		    CHtml::listData(

		        Controller::model()->resetScope()->findAll(

		        $criteria ) , 'start_date_year', 'start_date_year' ),

		        'htmlOptions'=>array('width'=>'50px'), 'type'=>'raw'),

		array('name'=>'start_date_month', 'header'=>Yii::t('app', $model->getAttributeLabel('start_date_month')),

		'filter' =>

		    CHtml::listData(

		        Controller::model()->resetScope()->findAll(

		        $criteria ) , 'start_date_month', 'start_date_month' ),

		        'htmlOptions'=>array('width'=>'50px'), 'type'=>'raw'),


...