Model search criteria on related record

Hi there,

I am setting up an admin backend based on the standard MVC structure generated by gii.

The default view generated by gii associated with the "admin" action has a very useful table with an AJAX filtering built in.

My Article db table has a category_id column which is related to the category table where I can retrieve the category name.

I have customized the admin table of my Article model so it shows the category name instead of the category id, but the search field disappeared from the first row. I guess I have to create the right CDbCriteria entry in order for it to process the query, but I don’t know how.

My Article model file looks like this:

	public function rules() {

		return array(

			[...]

			array('category_id, status, title, teaser, content, author_id, create_time, update_time', 'safe', 'on'=>'search'),

		);

	}

[...]

	public function relations() {

		return array(

			'Category' => array(self::BELONGS_TO, 'Category', 'category_id'),

			[...]

		);

	}


[...]

	public function search() {

		$criteria=new CDbCriteria;

		

		$cat = $cats[$this->article_type_id]; 

		

		$criteria->compare('Category.name', $this->Category->name, true); // THROWS AN EXCEPTION

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

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

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

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

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

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

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


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

			'criteria' => $criteria,

		));

	}

My view file looks like this:

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

	'id'=>'article-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'Category.name:text:'. Yii::t('app', 'Category'),

		'status', 

		'title',

		'author.full_name:text:'. Yii::t('app', 'Author'),

		array(

			'class'=>'CButtonColumn',

		),

	),

));

Actually, the Category.name method works in the view widget and shows the category name. But the filtering is not activated.

How can I attach my related Category model to the search function?

Thanks in advance!

gm

Hi,

This is what I would do.

// model

	....

	public $category_name;

	....

	public function rules() {

		return array(

			...

			array('category_id, status, .... category_name', 'safe', 'on'=>'search'),

		);

	}

	....

	public function search() {

		$criteria=new CDbCriteria;

		$criteria->with = array('Category');

		$criteria->compare('Category.name', $this->category_name, true);

		$criteria->compare('t.status', $this->status);  // use 't.' to disambiguate

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

		....

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

			'criteria' => $criteria,

			'sort' => array(

				'defaultOrder' => 't.title',

				'attributes' => array(

					'category_name' => array(

						'asc' => 'Category.name ASC',

						'desc' => 'Category.name DESC',

					),

					'*',

				),

			),

		));

	}

	....

The points are

[list1]

[*] Introduce an additional public variable(property/attribute), and include it in the rule for search.

[*] Specify the criteria with the relation, and use the introduced attribute for the comparison.

[*] In order to make it sortable in the grid, include it in ‘sort->attributes’ property of the data provider and specify the sorting orders.

[/list]

The last point is optional, but you would like to have it. Refer to the reference for CActiveDataProvider::sort and CSort::attributes.

The view might be like this:

// view

....

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

	'id'=>'article-grid',

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

	'filter'=>$model,

	'columns'=>array(

		array(

			'name' => 'category_name',

			'title' => 'Category',

			'value' => '$data->Category->name',

		),

		'status', 

		'title',

		'author.full_name:text:'. Yii::t('app', 'Author'),

		array(

			'class'=>'CButtonColumn',

		),

	),

));

I hope it would help you a bit.

When you’ve set up your relations properly, as you have, then you don’t need extra fields, just this:

    	$criteria->compare('category.name', $this->category_id);

Notice how category.name is compared with category_id - as strange as it looks.

Then, to have a filter, you need to pass a filter function to the grid:

            	array(

                	'name' => 'category_id',

                	'header' => 'Category',

                	'value' => '(($data->category)?$data->category->name:"")',

                	'filter' => $data->getCategoryFilter(),

                	'htmlOptions'=>array('width'=>'10'),

            	),

And the filter function:

	public function getCategoryFilter() {

    	$Criteria = new CDbCriteria();

    	$Criteria->select = "name";

    	$results = Category::model()->findAll($Criteria);

    	$category_list = array();

    	foreach ($results as $result) {

        	$category_list[$result->name] = $result->name;

    	}

    	return $category_list;

	}

P.S.

If you want to filter the category by a textField, i.e., if you want to search categories by partial match, you may do it like I’ve suggested in the first post.

But if you want to filter it by a dropDownList, then you may take jacmoe’s solution.

Or, you can also consider the following:

	public function search() {

		$criteria=new CDbCriteria;

		$criteria->compare('category_id', $this->category_id); // just as Gii has written

		....

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

			'criteria' => $criteria,

		));

	}

There’s nothing changed in the model, just a little tweak in the view is enough for a dropDownList filter:

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

	'id'=>'article-grid',

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

	'filter'=>$model,

	'columns'=>array(

		array(

			'name'=>'category_id',

			'title'=>'Category',

			'value'=>'$data->Category->name',

			'filter'=>'CHtml::listData(Category::model()->findAll(), 'id', 'name'),

		),

		'status', 

		'title',

		'author.full_name:text:'. Yii::t('app', 'Author'),

		array(

			'class'=>'CButtonColumn',

		),

	),

));

Thanks a lot for the suggestions! I understood that jacmoe’s solution requires softark’s ‘with’ clause on the model search criteria.

Thanks for the tip about the t. prefix too! Where can I read more about its use?

gm

I think the definitive guide is the best place.

Working with databases - Relational Active Record