Yii Framework Forum: Search() & Filter of a relations field through CGridView - Yii Framework Forum

Jump to content

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

Search() & Filter of a relations field through CGridView Rate Topic: ***** 11 Votes

#21 User is offline   echo66 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 66
  • Joined: 06-May 10

Posted 06 June 2010 - 10:49 PM

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
0

#22 User is offline   plantin 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 02-June 10

Posted 15 June 2010 - 04:45 AM

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.
0

#23 User is offline   uponsun 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 19-April 10
  • Location:New Jersey

Posted 16 June 2010 - 08:07 AM

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).
0

#24 User is offline   plantin 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 02-June 10

Posted 20 August 2010 - 06:47 AM

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.
0

#25 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,359
  • Joined: 12-October 09
  • Location:Croatia

Posted 20 August 2010 - 07:04 AM

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)
Find more about me.... btw. Do you know your WAN IP?
0

#26 User is offline   plantin 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 02-June 10

Posted 20 August 2010 - 08:07 AM

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},


View Postmdomba, on 20 August 2010 - 07:04 AM, said:

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

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

0

#27 User is offline   joeysantiago 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 63
  • Joined: 01-April 10

Posted 03 September 2010 - 08:44 AM

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!
joey santiago

an Italian with Suomi inside.
0

#28 User is offline   cactork 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 22
  • Joined: 30-October 10
  • Location:Buenos Aires, Argentina.

Posted 04 November 2010 - 12:23 PM

View Postleandrofranca, on 06 June 2010 - 07:49 PM, said:

Sorry I'm speaking bullshit...
You don't need the public attribute username in your Model because you have the relation "user".
What you need to is...

In Model (search) method:
$criteria->compare('t.user_id',$this->user_id);


And in View...
		array(
			'name'=>'user_id',
			'filter'=>CHtml::listData(User::model()->findAll(), 'id', 'name'),
			'value'=>'User::Model()->FindByPk($data->user_id)->name',
		),


Try it!


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!
0

#29 User is offline   sbasuki 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 40
  • Joined: 14-January 10

Posted 19 January 2011 - 11:18 PM

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',
		),

0

#30 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 394
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 03 February 2011 - 04:26 AM

View Postleandrofranca, on 09 May 2010 - 10:26 PM, said:

Yeeepp it's working!

<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'pacote-grid',
	'dataProvider'=>$model->search(),
	'filter'=>$model,
	'emptyText'=>param('noResult'),
	'summaryText'=>'Total: {count}',
	'pagerCssClass'=>'alignCenter',
	'columns'=>array(
		'nome',
		array(
			'name'=>'roteiroId',
			'filter'=>CHtml::listData(Roteiro::model()->findAll(), 'id', 'descricao'),
			'value'=>'Roteiro::Model()->FindByPk($data->roteiroId)->descricao',
		),
		array(
			'name'=>'tipopacoteId',
			'filter'=>CHtml::listData(TipoPacote::model()->findAll(), 'id', 'descricao'),
			'value'=>'TipoPacote::Model()->FindByPk($data->tipopacoteId)->descricao',
		),
		array(
			'name'=>'destaque',
			'filter'=>CHtml::activeDropDownList($model, 'destaque', Pacote::model()->statusOptions),
			'value'=>'Pacote::model()->statusOptions[$data->destaque]',
		),
	),
)); ?>


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',
		),

Posted Image
1

#31 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 394
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 03 February 2011 - 09:11 AM

View Postfouss, on 03 February 2011 - 04:26 AM, said:

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',
		),



I posted herewith more details for my problem
Posted Image
0

#32 User is offline   nguyendh 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 80
  • Joined: 21-March 11

Posted 25 March 2011 - 11:23 AM

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.
I am new to Yii and this is my blog http://blog.michaelduy.com (deprecated hatadu.wordpress.com) about Yii, Git and PHP.
0

#33 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,359
  • Joined: 12-October 09
  • Location:Croatia

Posted 26 March 2011 - 03:53 AM

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...
Find more about me.... btw. Do you know your WAN IP?
0

#34 User is offline   nguyendh 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 80
  • Joined: 21-March 11

Posted 28 March 2011 - 10:01 AM

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']),
            ),
		));

I am new to Yii and this is my blog http://blog.michaelduy.com (deprecated hatadu.wordpress.com) about Yii, Git and PHP.
0

#35 User is offline   jesterx 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 26-October 10

Posted 12 October 2011 - 06:28 PM

View Posteval, on 09 May 2010 - 10:44 AM, said:

As a data column for each relation give the following (the example shows user's country from the country_id in user table and a dropdown filter to select users from each country):
array(
            'name' => 'country_id',
            'filter' => CHtml::listData(Country::model()->findAll(), 'country_id', 'country_name'), // fields from country table
            'value' => 'Country::Model()->FindByPk($data->country_id)->country',
        ),


That should work and generate you a dropdown filter for filtering based on country.

HTH


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

#36 User is offline   banamlehsb 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 02-September 11

Posted 10 November 2011 - 11:12 AM

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)
0

#37 User is offline   tanimgt 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 52
  • Joined: 07-April 11
  • Location:Bangladesh

Posted 06 December 2011 - 03:40 PM

View Postmdomba, on 26 March 2011 - 03:53 AM, said:

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...




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',
            	),
        	),
    	));
    	?>

0

#38 User is offline   Scott Deagan 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 70
  • Joined: 04-February 10

Posted 19 December 2011 - 10:18 PM

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

#39 User is offline   BowerUK 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 10
  • Joined: 04-April 12

Posted 15 May 2012 - 08:48 PM

View Posteval, on 09 May 2010 - 10:44 AM, said:

'filter' => CHtml::listData(Country::model()->findAll(), 'country_id', 'country_name'), // fields from country table



Thank you :)
0

#40 User is offline   Qamar Suha 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 28-November 13

Posted 05 December 2013 - 03:15 AM

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?
0

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users