Using CActiveDataProvider in CGridView with supporting filtering

trejder · December 17, 2010, 3:51pm

I was using standard (gii generated) $model->search() as dataProvider to feed CGridView and of course filtering grid view worked like a charm. Then I decided to change it to CActiveDataProvider:


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>array

            	(

                    	'select'=>'ID, STAT, DATR, BCODE, PATIENT, PESEL, UDATE'

            	),

            	'pagination'=>array('pageSize'=>20),

    	));

and it (of course) stopped working - meaning, that filter filling from search form works fine, but records are not being filtered properly.

Can you advice me on what line should I add to criteria array of CActiveDataProvider, so it would support filtering?

jacmoe · December 17, 2010, 3:56pm

You’re probably missing all the compare statements.


$criterial->compare('fieldname', $this->field);

The ‘search’ function is actually just a convenience function which returns a CActiveDataProvider:


	public function search()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;


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

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

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

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

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

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

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

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

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

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

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


		return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

		));

	}

trejder · December 17, 2010, 10:03pm

I know, what I’m doing wrong (or to be precisely - what I’m not doing), but I can’t figure out an easy (quick) way to get all the filters’ fields’ values to put them into CDbCriteria and then to CActiveDataProvider.

trejder · December 28, 2010, 2:42pm

It is obvious for most Yii developers, but even so I’ll paste working example, what maybe will save someone else some time searching for it.

With some help from jacmoe I was able to change below code:


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>array

            	(

                    	'select'=>'ID, STAT, DATR, BCODE, PATIENT, PESEL, UDATE'

            	),

            	'pagination'=>array('pageSize'=>20),

    	));

Into:


    	$criteria = new CDbCriteria;

    	$criteria->compare('DATR', $model->DATR, true);

    	$criteria->compare('BCODE', $model->BCODE, true);

    	$criteria->compare('PATIENT', $model->PATIENT, true);

    	$criteria->compare('PESEL', $model->PESEL, true);

    	$criteria->compare('UDATE', $model->UDATE, true);

    	$criteria->select = 'ID, STAT, DATR, BCODE, PATIENT, PESEL, UDATE';

    	$criteria->order = 'DATR DESC';


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	'pagination'=>array('pageSize'=>20),

    	));

And now it works fine - i.e. CGridview fed with CActiveDataProvider modified like in above example now supports filtering with build-in filters.

jshot · January 3, 2011, 7:09am

i wanna ask

what is $model in your CActiveDataProvider?
i have added $criteria->with=array(‘relation_to_tableB’);

so,how i could compare with any column on tableB?

maybe like :

[PHP]

public $columnB_one; //must i create public variable?

//in search function :

$criteria->compare(‘relation_to_tableB.columnB’, $this->columnB_one, true);

[/PHP]

thanks for feedback

trejder · January 3, 2011, 8:29am

Since this is MVC (model, view, controller) design pattern, you have to have model (Yii representation of data in your table) declared in a model file, then you retrieve this model (data from table) in your controller and put it in your view to be displayed to user. Read more about it official documentation in chapters about MVC design pattern and about Active Record design pattern (especially parts about defining Model and reading data).

The code you are asking for is in my view file. The variable $model, you are asking for is inserted to that view file with a code in my controller. Like this (since this is default, index action, I use actionIndex method of my controller file):


/**

* Manages all models.

*/

public function actionIndex()

{

    	$this->layout = '//layouts/column1';


    	$model = new Zlecenia('search');


    	$model->unsetAttributes();  //Clear any default values

    	if(isset($_GET['Zlecenia'])) $model->attributes = $_GET['Zlecenia'];


    	$this->render('index', array('model'=>$model));

}

where line:


$this->render('index', array('model'=>$model));

tells Yii to render view called index and inserts to it $model variable, you’re asking about.

And where line:


$model = new Zlecenia('search');

Refers to my model called Zlecenia (in search scenario). I won’t publish it’s code here, because it is too long. You can use Gii Tool that ships with Yii (read more about it in documentation, about ActiveRecord design pattern) to generate a model file or you can write it yourself from scratch following second chapter of documentation I cited above.

BTW: Since my controller is named zlecenia (ZleceniaController class defined in ZleceniaController.php file) and my action for it is index (defined as actionIndex method in ZleceniaController.php file) you can access this page by calling http://your_applicat…ss/index.php?r=zlecenia/index, which we name route and which tells Yii that it should execute index action of zlecenia controller. Read more in first chapter I cited above (about MVC design pattern).

Tip: None of about presented code was written by me! It was all generated by gii Tool that ships with Yii. If you haven’t used it before, I strongly advice you to start with Creating First Yii Application chapter of the official documentation, where all this stuff is presented. It might sound difficult at first sight. But if you read it thoroughly two or three times, comparing with other chapters in a very well documented guide, you will get the point.

jshot · January 3, 2011, 2:07pm

thanks for explanation, yeah i know alot about that

does CActiveDataProvider has :




return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,

			...

		));


//you write :

return new CActiveDataProvider($model, array(

			'criteria'=>$criteria,

			...

		));

does your code : $model = get_class($this) ?

trejder · January 3, 2011, 2:21pm

No! Or… yes! But… read on… :]

In your model definition class there should be a method public function search(). There you (or Gii, if it auto-generate this file for you) declare a default search behaviour. And there you will find declaration you cited - i.e. CActiveDataProvider(get_class($this). If you use only this searching method (criteria), then in a view file you simply put:


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

But if you want to do some more extended search or more deeply modify results presented in CGridView, then you do, what I do.

You create $criteria = new CDbCriteria;, where you declare details about what should be retrieved from DB (and how - including sorting, pagination, etc.). Then you put this criteria to CActiveDataProvider, and in this situation you are using my method of calling, that is:


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	'pagination'=>array('pageSize'=>20),

            	'sort'=>array('defaultOrder'=>array('DATR'=>true)),

    	));

where $model is model inserted to that view by a controller, taken from a model declaration.

And if you use this approach then you have to tell your CGridView that your data provider is:


'dataProvider'=>$dataProvider,

But this is only a convention. I’m pretty sure that if you would change CActiveDataProvider(get_class($this) with CActiveDataProvider(‘ModelName’ in your ModelName declaration class (ModelName.php in models folder) then everything should work the same way.

In other words, you can say that $model = get_class($this). Because get_class($this) used in Model declaration class only retrieves a model class name, while $model used in a view file is an actual model, that is a data retrieved (by a controller) from database.

At least, that is how I understand this, but I might be missing something.

jshot · January 3, 2011, 2:53pm

Trejder:


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	'pagination'=>array('pageSize'=>20),

            	'sort'=>array('defaultOrder'=>array('DATR'=>true)),

    	));


'dataProvider'=>$dataProvider,

wow very interesting explanation!

cause i had confused how modified customize criteria in CGridView

so, if your method, i must type in controller :


    	

$model = new model_name;// or $model = model_name::model() ??

//what different [b]new[/b] keyword and with using scope parameter?


$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	'pagination'=>array('pageSize'=>20),

            	'sort'=>array('defaultOrder'=>array('DATR'=>true)),

    	));

trejder · January 3, 2011, 3:12pm

You retrieve model (data) in the controller with the code I’ve already pasted you (first line):


public function actionIndex()

{

    	$model = new Zlecenia('search'); //Retrieve model


    	$model->unsetAttributes();  //Clear any default values

    	if(isset($_GET['Zlecenia'])) $model->attributes = $_GET['Zlecenia'];


    	$this->render('index', array('model'=>$model)); //render view with model data

}

Then you put it to a view (last line, rendering a view). You should avoid using any data provider in a controller. Simply send your model to a view and do whatever you want with this model - i.e. declare $dataProvider = new CActiveDataProvider($model - or do something similar

EDIT: But, why you are trying to do this yourself? Isn’t easier (much easier) to use Gii Tool to first generate model for you and then generate CRUD operations basing on that model? This way you’ll have all the code ready to be used or to extend with your own functionality.

jshot · January 3, 2011, 3:20pm

Trejder:

You retrieve model (data) in the controller with the code I’ve already pasted you (first line):
public function actionIndex()

{

    	$model = new Zlecenia('search'); //Retrieve model


    	$model->unsetAttributes();  //Clear any default values

    	if(isset($_GET['Zlecenia'])) $model->attributes = $_GET['Zlecenia'];


    	$this->render('index', array('model'=>$model)); //render view with model data

}
Then you put it to a view (last line, rendering a view). You should avoid using any data provider in a controller. Simply send your model to a view and do whatever you want with this model - i.e. declare $dataProvider = new CActiveDataProvider($model - or do something similar

EDIT: But, why you are trying to do this yourself? Isn’t easier (much easier) to use Gii Tool to first generate model for you and then generate CRUD operations basing on that model? This way you’ll have all the code ready to be used or to extend with your own functionality.

oh, but why we must $model->unsetAttributes(); //Clear any default values

so, if we have some criteria, we must put into search (or customizeable search) function? avoid put in controller, isnt it?

trejder · January 3, 2011, 3:36pm

I have no bloody idea! :] This part of code was generated by Gii for me and I am glad it is working, not asking questions, why! ;] You must wait for someone more experienced in Yii to answer this question to you.

You have an example search defined in your model. You can either change it or you can define your own search in a view. It depends, what you want to achieve. If you want to change your searching that will be used in all or most of searches, then it is best idea to do it in model in search() method declaration. But if you are changing only one, particular search in one, particular view, then in is better to introduce your own, customized search directly in a view, just like I showed you in above examples.

jshot · January 3, 2011, 3:56pm

sorry, the bold text mean custom search function in model(not in view)?

like public function search_customize() ?

cause if i use in controller:




$model=new model_name('search');

$dataProvider = new CActiveDataProvider($model, array

	(

		'criteria'=>array

		(

			'select'=>'custome_select_new_column'

		),

));

$this->render('thatis_view',array('model'=>$dataProvider));

and the view :




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

	'id'=>'data-grid',

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

	'filter'=> $model,

	'columns'=>array(

	...

and give me exception Property "CActiveDataProvider.search" is not defined.

Does if i use CActiveDataProvider in view, the criteria in search() model_name will be replace? or just merge criteria?

tri · January 3, 2011, 4:23pm

I’ll try to explain my view of how the generated code works:

A new CActiveRecord instance called $model is created in the controller action. It may be populated with posted search data. This model instance is passed as is to the filter property of CGridview. It is also the instance used for accessing the search() method of the corresponding model class (from the view). This search method returns a CActiveDataProvider instance which is passed to the dataprovider property of CGridView. This is the actual dataset with filtering, sort and pagination applied.

Regarding the unsetAttributes() call:

Since new model instances will receive the default values specified in db schema, the attributes will need to be cleared for unfiltered data to be returned.

/Tommy

yiippy · January 3, 2011, 5:34pm

How to make search work with 3 tables(3 model)?. I have 3 tables user, profil and school. user can have many school. I try make search function in the user model. what i want to achieve is to get the last school by year of graduate’s year. for example:




i just want to pick up the last school (with school_id:3)

+-----------------------------------------------+

school_id|user_id|school_name|year_of_graduation|

1        |2      |schoolA    | 2000|

2        |2      |schoolB    | 2003|

3        |2      |schoolC    | 2006|

+-----------------------------------------------+




// model User.php

public $fullName;

public $schoolName;


public function search()

{

	  $criteria = new CDbCriteria;

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

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

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

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

	  $criteria->join = 'LEFT JOIN profil p ON t.user_id=p.user_id LEFT JOIN school s ON t.user_id=s.user_id';

	  $criteria->with = array('profil_relation', 'school_relation');

	  $criteria->compare('p.fullname', $this->fullName, true);

	  $criteria->order = 'je.th_lulus DESC';

	  $criteria->compare('s.school_name', $this->schoolName, true);

	  $criteria->addCondition('s.th_lulus IN (SELECT MAX(s.th_lulus) AS th_lulus FROM school WHERE s.user_id=t.user_id GROUP BY t.user_id)');

	  return new CActiveDataProvider('User', array(

		  'criteria' => $criteria,

	  ));

}


I've tried it on phpmyadmin and it work.


SELECT DISTINCT

u.user_id, 

u.username,

u.display_name,

u.email,


p.fullname,

p.address,

p.sex,

p.marital_status,

p.date_ofbirth,


s.school_name,

s.graduate_year

FROM USER u LEFT JOIN profil p ON u.user_id=p.user_id

LEFT JOIN school s ON u.user_id=p.user_id

WHERE 

u.username LIKE '%agus%' AND

u.email LIKE '%@yahoo.com%' AND

p.sex='Female' AND

p.marital_status = 'Single' AND

((YEAR(NOW())-YEAR(p.date_ofbirth)) BETWEEN 19 AND 24) AND

(s.school_name LIKE '%gadjah%' OR s.school_name LIKE '%gajah%' OR s.school_name LIKE '%ugm%') AND

s.graduate_year IN(SELECT MAX(s.graduate_year) FROM school WHERE s.user_id=p.user_id)

LIMIT 100

(sorry for my bad english:))

I hope know what i mean. thanks in advance

jogasa21 · February 20, 2012, 9:48am

Trejder:

It is obvious for most Yii developers, but even so I’ll paste working example, what maybe will save someone else some time searching for it.

With some help from jacmoe I was able to change below code:


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>array

            	(

                    	'select'=>'ID, STAT, DATR, BCODE, PATIENT, PESEL, UDATE'

            	),

            	'pagination'=>array('pageSize'=>20),

    	));

Into:


    	$criteria = new CDbCriteria;

    	$criteria->compare('DATR', $model->DATR, true);

    	$criteria->compare('BCODE', $model->BCODE, true);

    	$criteria->compare('PATIENT', $model->PATIENT, true);

    	$criteria->compare('PESEL', $model->PESEL, true);

    	$criteria->compare('UDATE', $model->UDATE, true);

    	$criteria->select = 'ID, STAT, DATR, BCODE, PATIENT, PESEL, UDATE';

    	$criteria->order = 'DATR DESC';


    	$dataProvider = new CActiveDataProvider($model, array

    	(

            	'criteria'=>$criteria,

            	'pagination'=>array('pageSize'=>20),

    	));

And now it works fine - i.e. CGridview fed with CActiveDataProvider modified like in above example now supports filtering with build-in filters.

Thanks a lot Trejder for sharing your solution. It was like a medicine for my headache.