Search() & Filter of a relations field through CGridView

Yii 1.1 General Discussion
21

Obrigado Leandro!

I worked on another solution for the text field issue:

i created a auxiliary field, in the model, as you can see




class CommunityUser extends CActiveRecord

{

	/**

	 * The followings are the available columns in table 'community_user':

	 * @var integer $user_id

	 * @var integer $community_id

	 * @var integer $is_suspended

	 * @var integer $can_invite_users

	 * @var integer $can_manage_objects

	 * @var integer $can_manage_templates

	 * @var integer $can_deactivate_community

	 * @var integer $can_manage_members

	 * @var integer $can_close_community

	 * @var integer $can_manage_requests

	 */

	

	public $aux_username;


....




	public function searchPerCommunity($communityId)

	{

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

		// should not be searched.


		$criteria=new CDbCriteria;

		

		$criteria->join = 'JOIN user as t2 ON t2.id = t.user_id';


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


		$criteria->compare('community_id',$communityId);


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


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


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


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


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


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


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


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

//		

//		$criteria->compare('user.name',$this->user_id,true);

//		

//		$criteria->compare('user.photo',$this->user_id);


		$criteria->addSearchCondition('user.username',$this->aux_username,true);

		

		$criteria->with = 'user';


		return new CActiveDataProvider('CommunityUser', array(

			'criteria'=>$criteria,

		));

	}

and the view




array(

        	'name'=>'user_id',

       		//'filter'=>CHtml::listData(CommunityUser::model()->with('user')->findAllByAttributes(array('community_id'=>$community->id)), 'user_id', 'user.username'),

       		'filter'=>CHtml::activeTextField($dataProvider,'aux_username'),

        	'value'=>'User::model()->findByPk($data->user_id)->username',

        ),

And it’s working!!!! Now, using this solution, i can make all the relations i want without worrying with cgridview and cdatacolumn issues. ;D

22

A solution to this problem:

in the model Users

normal relations


	public function relations()

	{

		return array(

			'org' => array(self::BELONGS_TO, 'Organisations', 'user_org'),

		);

	}

create new function RelSearch() to get relational data


  public function Relsearch()

	{

		$criteria=new CDbCriteria;

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

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

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

		$criteria->addSearchCondition('org.org_name',$this->user_org);

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

			'criteria'=>$criteria,

		));

	}

in the cgridview


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

	'id'=>'afstudeer-grid',

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

	'filter'=>$model,

	'enablePagination'=>false,

	'columns'=>array(

		'user_id',

		'user_name',

		array(

			  'name'=>'user_org',

			  'value'=>'$data->org->org_name',

			  'type'=>'text',

			  ),

		array(

			'class'=>'CButtonColumn',

		),

	),

));

So what I did was this:

defined relations as I normally would

made a new function RelSearch to include relational searches

replaced the normal compare statement with two statements:

 $criteria->with=array('TheNameOfTheRelation')


 $criteria->addSearchCondition('TheNameOfTheRelation.TheNameOfTheDisplayedField',$this->user_org)

replaced the dataprovider in de cgridwidget with my new function $model->RelSearch()

replaced the columndata with the relational data with


array(

	'name'=>'user_org',//the normal name of the displayed field

	'value'=>'$data->org->org_name', // the $data->TheNameOfTheRelation->TheNameOfTheDisplayedField

	'type'=>'text',

),

Good luck.

23

To get full CGridView capability with relational data, I prefer to create a MySQL view with desired columns from other tables. After generating the model for the MySQL view, provide a primaryKey() function that returns the name of a column that is a primaryKey() for a view column that is a primary key from one of the tables. Providing the primaryKey() function avoids errors from the CRUD generator. At this, point you have full CGridView sort, search, filter functionality. The drawback of this approach is that you have to know how to write SQL code to create the view (assuming you are using MySQL Workbench).

24

I had a lot of success with the solution I devised, until now.

I have a column/field from a relation that needs formatting, in this case conversion to roman numerals.

So 1 becomes I, 5 becomes V etc. To display the numbers correctly, I’m using a php-function, which works fine.

When a user is trying to filter by typing in V, cgridview returns an empty table, because there are no records with ‘V’ in the relational table.

Any suggestions?

The current function is too complex to be used directly in a mysql query, even more so because it has to be inverted.

I would have hoped to find out how cgridview could filter the values of the data, rather then ajax filtered data, but to no avail.

25

In your example above the search is done in the RelSearch function…

so there before adding a search condition you need to test and if needed convert the entered value (from V to 5)

26

Thanks for the quick reply, I will try your approach.

I’m also going to try something different that just occured to me, based on some further reading, I’ll let you know how it goes.

–update–

Due to the complex nature of the conversion and the limited number of entries, I decided to work with a dropdownlist.

However I would still like to know the answer to my original thought:

can I replace the filter-argument


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

	'id'=>'my-grid',

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

	'filter'=>$model,

with


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

	'id'=>'les-grid',

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

	'filter'=>{something that would filter on the displayed data in stead of the ajax call made to the model},
27

hello everybody,

i tried the solutions suggested, but haven’t found a way to perform the actual search.

this is my auto_campagna model relations function:


public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.


                return array(

                    'cod_marca_ic' => array(self::BELONGS_TO, 'marca', 'id'),

                );

		

	}

i hope it’s corrected: the auto_campagna.cod_marca_ic field refers to a foreign table called marca and the value is the same as marca.id.

then this is my view:


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

        'cssFile'=>'css/gridview.css',

	'id'=>'auto-campagna-grid',

	'dataProvider'=>$dataProvider,

	'filter'=>$model,

	'columns'=>array(

                array(

                    'class'=>'CDataColumn',

                    'name'=>'cod_marca_ic',

                    'value'=>'marca::model()->findByPK($data->cod_marca_ic)->nome',

                    'filter'=>CHtml::listData(marca::model()->findAll(),'id','nome'),

                ),

		'promozione',

		'prezzo',

		array(

			'class'=>'CButtonColumn',

                        'buttons'=>array(

				

			),

		),

	),

));

and this is my search() function:


public function search()

	{

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

		// should not be searched.


                $idCamp = $_GET['campId'];

            

		$criteria=new CDbCriteria;

                

		$criteria->compare('id_campagna',$idCamp);


                if($this->cod_marca_ic!=null){

                    $criteria->addSearchCondition('cod_marca_ic',$model->cod_marca_ic,true);

                    $criteria->with = 'cod_marca_ic';

                }

		return new CActiveDataProvider('auto_campagna', array(

			'criteria'=>$criteria,

		));

	}

… can you help me out? thanks a lot!

28

Hi there!

I tried your last code and works really great when I show only one field of the relation.

But if I show two (continuing with the model of the post, let’s say: username and name), what happens is that both select boxes seems to be associated and if I change one of them, the other one changes too.

Like if they were associated…




		array(

			'name'=>'user_id',

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

			'value'=>'User::Model()->FindByPk($data->user_id)->name',

		),

		array(

			'name'=>'user_id',

			'filter'=>CHtml::listData(User::model()->findAll(), 'id', 'username'),

			'value'=>'User::Model()->FindByPk($data->user_id)->username',

		),

Do you know what’s going on?

Thanks!

29

Thanks it’s help me, and it works for me

This is my-code :




		array (

			'name'=>'project_sts_id',

			'filter' => CHtml::listData(ProjectStatus::model()->findAll(), 'project_sts_id', 'project_sts_desc'),

			'value' => 'ProjectStatus::Model()->FindByPk($data->project_sts_id)->project_sts_desc',

		),
30

How to make it work if I have an other attribute in my "Roteiro" like "descricao2"?

I tryed this but not working…




		array(

			'name'=>'roteiroId',

			'filter'=>CHtml::listData(Roteiro::model()->findAll(), 'id', 'descricao2'),

			'value'=>'Roteiro::Model()->FindByPk($data->roteiroId)->descricao2',

		),
31

I posted herewith more details for my problem

32

Hi guys,

I am trying to do the same thing, but the filter is not working for some reason and i have no clues what’s happening.

My Model




class Agent extends CActiveRecord

{

    public $aux_first_name;


	public function relations()

	{

	// NOTE: you may need to adjust the relation name and the related

	// class name for the relations automatically generated below.

	   return array(

		'person' => array(self::BELONGS_TO, 'Person', 'person_id'), // Person model - People is the tbl name

	   );

	}

    public function Relsearch()

    {

        $criteria=new CDbCriteria;

	$criterai->join = 'JOIN People as p1 ON p1.agent_id = t.agent_id';

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

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

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

        $criteria->addSearchCondition('person.first_name', $this->aux_first_name, true);

	$criteria->with = 'person';

		

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

            'criteria'=>$criteria,

			'pagination'=>array(

                'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),

            ),

        ));

    }

My view




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

	'id'=>'agent-grid',

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

	'filter'=>$model,

	'enablePagination'=>false,

	'columns'=>array(

		'agent_id',

		'website',

		array(

			'header'=>'First Name',

			'filter'=>CHtml::activeTextField($model,'aux_first_name'),

			'value'=>'$data->person->first_name',

		),

        .......

I have the textfield filter on top first_name, but the filtering is not working. Is there something wrong with my relsearch function() ?

I also notice that textfield filter value resets to empty after the search finishes. The default Yii filter search text is still present in the textfield.

33

First thing I noticed is that you have two lines with $criteria->with (before and after addSearchCondition)… and second thing is about the JOIN people - why not create a relation for this…

34

I have my problem solved.

As mdomba suggested, I have relationship setup and remove the JOIN.

Also, What I missing is this setting ‘aux_first_name’, ‘aux_last_name’ to be ‘safe’ and ‘searchable’




public function rules()

{

   return array(

               array('aux_first_name,aux_last_name,...', 'safe', 'on'=>'search');

   );

And




 	public function search()

	{

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

		// should not be searched.

		

		$criteria=new CDbCriteria;


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

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

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

		

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

		$criteria->compare('person.first_name', $this->aux_first_name, true);

		$criteria->compare('person.last_name', $this->aux_last_name, true);

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

			'criteria'=>$criteria,

			'pagination'=>array(

                'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),

            ),

		));
35

I wish the yii forums had a like button. Thanks!

36

I try it:




array(

                        'name'=>'maLoaiGD',

                        'filter'=>CHtml::listData(LoaiGiaoDich::model()->findAll(), 'maLoaiGD', 'tenLoaiGD'),

                        'value'=>'LoaiGiaoDich::model()->findByPk($data["maLoaiGD"])->tenLoaiGD',

                ),

but it causes error: Trying to get property of non-object. (I don’t know how to use $data variable)

37

Hi sir, can you point me what i am doing wrong ? it cannt filter the data:

in my controller:




public function actionStudentDailyReport()

	{

		$model=new StudentAttendance('search2');

       		

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

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

			$model->attributes=$_GET['StudentAttendance'];


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

			'model'=>$model,

               		

		));

	}

in my model:




public $name;


 public function rules() {

    	return array( 	

        	array('name', 'safe', 'on' => 'search'),

    	);

	}


 public function relations() {

    	return array(

        	'student' => array(self::BELONGS_TO, 'StudentTbl', 'student_id'),

    	);

	}


public function attributeLabels() {

    	return array(

        	'name' => 'Name',

    	);

	}


 public function search2() {

   

    	$criteria = new CDbCriteria;


    	$criteria->together = true;

    	$criteria->with[] = 'student';

    	

 	if ($this->name) {

   		

        	$criteria->compare('student.student_name', $this->name, true);   		

   		

    	}

	

  	

    	return new CActiveDataProvider($this, array(

        	'criteria' => $criteria,

   		

    	));

	}

in my view:




 <?php

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

        	'id' => 'student-attendance-grid',

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

        	'filter' => $model,

        	'columns' => array(

 		

            	array(

                	'name' => 'name',

                	'value' => '$data->student->student_name',

                	'filter'=>CHtml::listData(StudentTbl::model()->findAll(), 'student_name', 'student_name'),

            	),

          	

            	array(

                	'class' => 'CButtonColumn',

            	),

        	),

    	));

    	?>
38

Thanks eval (post #9), that solved it for me! Gotta love the Yii community :)

39

Thank you :)

40

hello members, im also facing same problem, but i follow the code below but its not working for me, but filter is working, its generate drop-down, only column is empty value, but on singe view i can get value using relation,




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

	'data'=>$model,

	'attributes'=>array(

		'id',

                array(

			'name'=>'create_user_id',

			'value'=>CHtml::encode($model->create_by->username)

		),		

             ........

but on CGridView do not get through relation as well direct call using model()…




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

	'id'=>'project-grid',

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

	'filter'=>$model,

	'columns'=>array(

                'projectId',

		array(

			'name'=>'create_user_id',

			'filter'=>CHtml::listData(User::model()->findAll(), 'id', 'username'),

			'value'=>'User::Model()->findByPk($data->create_user_id)->username',

		),

        .......

its show empty, no value in colum,

how can solve?