Cgridview Filter - Many To Many Relation

Hi everyone

I am kinda new to yii and I’m having problem with CGridView filtering.

I have a CGridView with list of problems. Each problem can have 1 or more skills and there are many skills as well so relation between Problems and Skills is many to many.

In my view a managed to display all skills associated with each problem but now I don’t know how to filter them (filtering from dropdown where if I select skill only problems with this skill would be in table)

This is my CGridView:




<?php 

	echo CHtml::link('Create problem',array('/site/createProblem'));

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

		'id'=>'problem-grid',

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

		'filter'=>$model,

		'cssFile'=>Yii::app()->request->baseUrl. '/themes/'. Yii::app()->theme->name.'/css/table.css',

		'columns'=>array(

			'id',

			'title',

			array(

				'name'=>'id_subject',

				'filter'=>CHtml::listData(Subject::model()->findAll(), 'id','abbreviation'),

				'value'=>'$data->subject->abbreviation',

				),

			array(

				'name'=>'skill',

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

				'value'=>'$data->getRelatedSkills()',

				),

			array(

				'name'=>'category',

				'value'=>'$data->category',

				),

			array(

				'class'=>'CButtonColumn',

				'deleteConfirmation'=>"js:'Do you really want to delete this problem?'",

				//'deleteButtonImageUrl'=>null,

				'deleteButtonUrl'=>'Yii::app()->createUrl("site/deleteProblem", array("id"=>$data->id))',

				'template'=>'{solve}{delete}',

				'buttons'=>array

				(

				'solve' => array

					(

						'label'=>'Solve',

						'url'=>'Yii::app()->createUrl("site/solveProblem", array("id"=>$data->id))',

					),

				'delete' => array

					(

						'visible'=>'$data->subject->currentUserSubject->id_role < "3"',

					)

				),

				),

			),

	));

?>



Just to note everything works fine except skill filter (and it doesn’t even throw exception it just won’t filter)

Here is my Problem model (I included only functions important to this case)




class Problems extends CActiveRecord

{

	public $skill;

	

	public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('title, description, id_subject', 'required'),

			array('id_subject, max_score', 'numerical', 'integerOnly'=>true),

			array('title', 'length', 'max'=>50),

			array('category', 'safe'),

			// The following rule is used by search().

			// Please remove those attributes that should not be searched.

			array('id, title, description, id_subject, category, max_score', 'safe', 'on'=>'search'),

		);

	}


	/**

	 * @return array relational rules.

	 */

	public function relations()

	{

		return array(

			'skills' => array(self::MANY_MANY, 'Skill', 'problem_skill(id_problem, id_skill)'),

			'subject' => array(self::BELONGS_TO, 'Subject', 'id_subject'),

			'solutions' => array(self::HAS_MANY, 'Solutions', 'id_problem'),

		);

	}

	

	public function getRelatedSkills()

	{

		$out=CHtml::listData($this->skills,'id','name');

	

		return implode(', ', $out);

	}


	public function search()

	{

		$criteria=new CDbCriteria;

		

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

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

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

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

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

		

		$criteria->with = array('skills', 'subject','subject.currentUserSubject' => array('alias'=>'currentUserSubject'));

		$criteria->together = true;

		

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


                //this is probably wrong

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


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

}



I think there is something wrong with criterias. Any ideas what’s wrong and how to fix it?

Check this out :

http://www.yiiframework.com/forum/index.php/topic/44838-filter-and-sort-a-column-fed-by-a-many-many-relation/

I struggled on same problem. However you have to know that I work with postgresql which is more SQL compliant. And the differences with MySQL can have effects on result. So have a look and test, if you work with postgresql it should work, if you work wit MySQL I don’t know… But I encourage you to also try AND to read these wiki :

http://www.yiiframework.com/wiki/385/displaying-sorting-and-filtering-hasmany-manymany-relations-in-cgridview/

http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation

I work with MySQL but I’ll check it out…

OK, I managed to solve it. As it turns out, I forgot to include variable skill to the search rule… fail :rolleyes: .

Hi,supernugy

I have same problem.

can you share your resolution for this problem?

Of course. It’s been a long time but I looked into it and it seems I only changed the problem view (search in Problem model is the same)




<?php }

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

		'id'=>'problem-grid',

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

		'filter'=>$model,

		'template' => '{pager}{items}{pager}',

		'cssFile'=>Yii::app()->request->baseUrl. '/themes/'. Yii::app()->theme->name.'/css/table.css',

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

		'columns'=>array(

			array(

				'name'=>'title',

				'filterHtmlOptions'=>array('id'=>"title_filter"),

				'value'=>'$data->title',

				'filter'=>CHtml::tag('div', array('class'=>'input_background'),  CHtml::activeTextField($model, 'title')),

				),

			array(

				'name'=>'id_subject',

				'filter' => CHtml::tag('div',array('class'=>'select_row_table'),

			                 CHtml::tag('label',array('class'=>'select_label'),CHtml::activeDropDownList($model, 'id_subject', 

						CHtml::listData(Subject::model()->with('currentUserSubject')->findAll(), 'id','abbreviation'), array('prompt' => '')))),

				'value'=>'$data->subject->abbreviation',

				),

			array(

				'name'=>'skill',

				'header' => Yii::t('MainTrans', 'Skills'),

				'filter' => CHtml::tag('div',array('class'=>'select_row_table'),

						CHtml::tag('label',array('class'=>'select_label'),CHtml::activeDropDownList($model, 'skill',

							CHtml::listData(Skill::model()->findAll(), 'id','name'), array('prompt' => '')))),

				'value'=>'$data->getRelatedSkills()',

				'htmlOptions'=>array('style'=>'max-width: 230px;'),

				),

			array(

				'name'=>'category',

				'filterHtmlOptions'=>array('id'=>"category_filter"),

				'value'=>'$data->category',

				'filter'=>CHtml::tag('div', array('class'=>'input_background'), CHtml::activeTextField($model, 'category')),

				),

			array(

				'name'=>'max_score',

				'filterHtmlOptions'=>array('id'=>"max_score_filter"),

				'value'=>'$data->max_score',

				'htmlOptions'=>array('style'=>'width: 20px;'),

				'filter'=>CHtml::tag('div', array('class'=>'input_background'), CHtml::activeTextField($model, 'max_score')),

				),

			array(

				'class'=>'CButtonColumn',

				'template'=>'{view} {solve} {View solutions}',

				'buttons'=>array

					(

					'view' => array

						  (

							'label'=>Yii::t('MainTrans', 'View'),

							'imageUrl'=>Yii::app()->request->baseUrl.'/themes/'.Yii::app()->theme->name.'/images/search.png',

				    		        'url'=>'Yii::app()->createUrl("problem/viewProblemDetails", array("id"=>$data->id))',

				)

			),							        

			),

		),

	));?>



Thank you for your quick reply.

The view in my project is ok, my problem is search.

Well here is my Problem model. Search is pretty much the same but I changed other things so take a look at it.




class Problems extends CActiveRecord

{

        public $skill;

        

        public function rules()

        {

                // NOTE: you should only define rules for those attributes that

                // will receive user inputs.

                return array(

                        array('title, description, id_subject', 'required'),

                        array('id_subject, max_score', 'numerical', 'integerOnly'=>true),

                        array('title', 'length', 'max'=>50),

                        array('category', 'safe'),

                        // The following rule is used by search().

                        // Please remove those attributes that should not be searched.

                        array('id, title, description, id_subject, category, max_score, skill', 'safe', 'on'=>'search'),

                );

        }


        /**

         * @return array relational rules.

         */

        public function relations()

        {

                return array(

                        'skills' => array(self::MANY_MANY, 'Skill', 'problem_skill(id_problem, id_skill)'),

                        'subject' => array(self::BELONGS_TO, 'Subject', 'id_subject'),

                        'solutions' => array(self::HAS_MANY, 'Solutions', 'id_problem'),

                );

        }

        

        public function getRelatedSkills()

        {

                $criteria=new CDbCriteria;

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

		$criteria->together = true;

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

		

		$model = Skill::model()->findAll($criteria);

		$out=CHtml::listData($model,'id','name');

	

		return implode(', ', $out);

        }


        public function search()

        {

                $criteria=new CDbCriteria;

		

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

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

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

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

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

		

		$criteria->with = array('skills', 'subject','subject.currentUserSubject' => array('alias'=>'currentUserSubject'));

		$criteria->together = true;

		

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

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


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

				'pagination'=>array(

					'pageSize'=>100,

				),

				'sort'=>array(

					'defaultOrder'=>array(

					'title'=>CSort::SORT_ASC

					)

				),

		));

        }

}



Just a thought: Check out the blog demo. There is a many-to-many relation with the Post->Tag relation. Maybe something in there might help.