Yii Framework Forum: GridView Filtering of Relational Data - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

GridView Filtering of Relational Data How can I configure GridView to allow filtering of data from relations Rate Topic: ***** 11 Votes

#1 User is offline   Mark O'Keeffe 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 03-October 11

Posted 03 October 2011 - 07:27 AM

Hi Everyone,

I am having trouble getting the GridView to display filters for relational data. I am trying to manage a TopDestination model, that holds a list of location IDs. The TopDestination model is related to a Location model as follows:

TopDestination Model:
	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(
			'location' => array(self::BELONGS_TO, 'Location', 'location_id'),
		);
	}


Location Model:
	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(
			'carHireSearchSessions' => array(self::HAS_MANY, 'CarHireSearchSession', 'pick_up_location_id'),
			'carHireSearchSessions1' => array(self::HAS_MANY, 'CarHireSearchSession', 'drop_off_location_id'),
			'topAirport' => array(self::HAS_ONE, 'TopAirport', 'location_id'),
			'topDestination' => array(self::HAS_ONE, 'TopDestination', 'location_id'),
		);
	}


I have created the admin view to manage TopDestinations, and I have added the Location data to the GridView as follows:

Admin View:
<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'top-destination-grid',
	'dataProvider'=>$model->search(),
  'filter' => $model,
  'columns'=>array(
    array(
        'name' => 'location_id',
    ),
    array(
        'name'=>'location.location_title',
        'value'=>'$data->location->location_title',
        'filter'=>CHtml::listData(Location::model()->findAll(), 'id', 'location_title'),
            'htmlOptions'=>array('width'=>'90px'),
    ),

    array(
      'name' => 'location.city_title',
    ),
    array(
      'name' => 'location.airport_code',
    ),
    array(
      'class'=>'CButtonColumn',
    ),
	),
)); ?>


Here is how the admin function looks in my TopDestination Controller:

TopDestination Controller:
	public function actionAdmin()
	{
	  //$locations = TopDestination::model()->with('location')->search();
		$model=new TopDestination('search');
    
    //$locations =  TopDestination::model()->with('location')->findByPK($model->location_id);
  
    // Only show the active records
    $model = $model->active();
    
		$model->unsetAttributes();  // clear any default values
		
		//$model->with('location');


    //fb($locations);
    
		if(isset($_GET['TopDestination']))
			$model->attributes=$_GET['TopDestination'];

		$this->render('admin',array(
			'model'=>$model,
			//'locations'=>$locations,
		));
	}


And here is the search function in the TopDestination Model:

TopDestination Model:
	public function search()
	{
		// Warning: Please modify the following code to remove attributes that
		// should not be searched.

		$criteria=new CDbCriteria;
    fb($this);
		$criteria->compare('t.location_id',$this->location_id,true);
    $criteria->with = 'location';
    $criteria->compare('location.location_title', $this->location);
    $criteria->addSearchCondition("location.location_title",$this->location->location_title); // This line does not work - 'trying to get property of non object' error
    $criteria->together = TRUE;


		return new CActiveDataProvider($this, array(
			'criteria'=>$criteria,
		));
	}


I cannot get the GridView to display filters for any of the Location data. Only the filter for location_id displays.

Does anyone know what I am missing? I have a feeling it is in the search criteria, but I am unable to get any criteria to work for the fields coming from the Location model!

Your help is much appreciated :)
0

#2 User is offline   Piotr Osipa 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 12-August 11

Posted 06 October 2011 - 07:37 AM

hi,
I've just spent few days over the same problem.
Source of this problem is a '.' given in
name and condition in method
renderFilterCellContent
...
else if($this->filter!==false && $this->grid->filter!==null && $this->name!==null && strpos($this->name,'.')===false)
...


My solution for your problem is easy

TopDestination Model:

// property
public $location_city_title

public function rules()
{
	return array(
		...
		// The following rule is used by search().
		// Please remove those attributes that should not be searched.
		array('...., location_city_title ', 'safe', 'on'=>'search'),
	);
}



public function attributeLabels()
{
	return array(
		...
		'location_city_title ' => 'City Title',
	);
}



public function search()
{
	...

	if($this->location_city_title)
	{
		$criteria->together  =  true;
		$criteria->with = array('location');
		$criteria->compare('location.city_title',$this->location_city_title,true);
	}

	...
	
	return new CActiveDataProvider(get_class($this), array(
	'criteria'=>$criteria,
	));
}



/views/topdestination/admin.php

<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'project-grid',
	'dataProvider'=>$model->search(),
	'filter'=>$model,
	'columns'=>array(
		array(
			'name'=>'location_city_title',
			'value'=>'$data->location->city_title',
		),
)); ?>


Finally
  • Make new "property" (required)
  • mark as "safe" in 'on'=>'search' scenario in rules method (required)
  • set name in attributeLabels method (optional)
  • add condition with "with" and "together" in search function (required)
  • set "name" in "columns" as the "property" name(required)


I've tested it with MANY_MANY realtion but I'm qite sure it works with other types.
4

#3 User is offline   Mark O'Keeffe 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 03-October 11

Posted 06 October 2011 - 08:39 AM

View PostPiotr Osipa, on 06 October 2011 - 07:37 AM, said:

hi,
I've just spent few days over the same problem.
Source of this problem is a '.' given in
name and condition in method
renderFilterCellContent
...
else if($this->filter!==false && $this->grid->filter!==null && $this->name!==null && strpos($this->name,'.')===false)
...


My solution for your problem is easy

...Snipped...



Thank you Piotr!

That is exactly right. I was unaware that the related attributes had to be declared as public at the top of the model.

It makes sense now!
0

#4 User is offline   Mark O'Keeffe 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 8
  • Joined: 03-October 11

Posted 07 October 2011 - 04:17 AM

Also, for anyone with the same problem, I ran into some issues trying to get the sort links for each of the relational columns in the grid view to work. But I have found the solution.

After declaring the relational attributes as public at the top of my model:
  // Instantiate required attributes from the Location model
  public $location_location_title;
  public $location_city_title;
  public $location_airport_code;


Adding the attributes to the search criteria:
// Please remove those attributes that should not be searched.
array('location_id, location_location_title, location_city_title, location_airport_code', 'safe', 'on'=>'search'),


And creating the attibute labels:
	public function attributeLabels()
	{
		return array(
			'location_id' => 'Location ID',
			'location_location_title' => 'Location Title',
			'location_city_title' => 'City',
			'location_airport_code' => 'Airport Code',
		);
	}


My search function in the model looks as follows:
public function search()
{
    // Create a new query
    $criteria=new CDbCriteria;

    $criteria->compare('location_id',$this->location_id, true);
    
    // Do all joins in the same SQL query
    $criteria->together  =  true;
    // Join the 'location' table
    $criteria->with = array('location');
    
    // Add comparisons for each of the relational attributes
    if($this->location_location_title){
      $criteria->compare('location.location_title',$this->location_location_title,true);
    }
    if($this->location_city_title){
      $criteria->compare('location.city_title',$this->location_city_title,true);
    }
    if($this->location_airport_code){
      $criteria->compare('location.airport_code',$this->location_airport_code,true);
    }

    // Create a custom sort
    $sort=new CSort;
    $sort->attributes=array(
      'location_id',
      // For each relational attribute, create a 'virtual attribute' using the public variable name
      'location_location_title' => array(
        'asc' => 'location.location_title',
        'desc' => 'location.location_title DESC',
        'label' => 'Location Title',
      ),
      'location_city_title' => array(
        'asc' => 'location.city_title',
        'desc' => 'location.city_title DESC',
        'label' => 'City',
      ),
      'location_airport_code' => array(
        'asc' => 'location.airport_code',
        'desc' => 'location.airport_code DESC',
        'label' => 'Airport Code',
      ),
      '*',
    );
    
    // Return the criteria and sort to the view
    return new CActiveDataProvider(get_class($this), array(
      'criteria'=>$criteria,
      'sort'=>$sort,
    ));
}


And the view works like so:
<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'top-destination-grid',
	'dataProvider'=>$model->search(),
	'filter' => $model,
  'columns'=>array(
    'location_id',
    array(
        'name' => 'location_location_title',
        'value' => '$data->location->location_title',
    ),
    array(
      'name' => 'location_city_title',
      'value' => '$data->location->city_title',
    ),
    array(
      'name' => 'location_airport_code',
      'value' => '$data->location->airport_code',
    ),
    array(
      'class' =>'CButtonColumn',
    ),
	),
)); ?>



Hopefully this can help anyone who has the same problems!
1

#5 User is offline   tanimgt 

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

Posted 06 December 2011 - 03:05 PM

View PostMark O, on 06 October 2011 - 08:39 AM, said:

Thank you Piotr!

That is exactly right. I was unaware that the related attributes had to be declared as public at the top of the model.

It makes sense now!


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

#6 User is offline   tanimgt 

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

Posted 06 December 2011 - 03:42 PM

View PostPiotr Osipa, on 06 October 2011 - 07:37 AM, said:

hi,
I've just spent few days over the same problem.
Source of this problem is a '.' given in
name and condition in method
renderFilterCellContent
...
else if($this->filter!==false && $this->grid->filter!==null && $this->name!==null && strpos($this->name,'.')===false)
...


My solution for your problem is easy

TopDestination Model:

// property
public $location_city_title

public function rules()
{
	return array(
		...
		// The following rule is used by search().
		// Please remove those attributes that should not be searched.
		array('...., location_city_title ', 'safe', 'on'=>'search'),
	);
}



public function attributeLabels()
{
	return array(
		...
		'location_city_title ' => 'City Title',
	);
}



public function search()
{
	...

	if($this->location_city_title)
	{
		$criteria->together  =  true;
		$criteria->with = array('location');
		$criteria->compare('location.city_title',$this->location_city_title,true);
	}

	...
	
	return new CActiveDataProvider(get_class($this), array(
	'criteria'=>$criteria,
	));
}



/views/topdestination/admin.php

<?php $this->widget('zii.widgets.grid.CGridView', array(
	'id'=>'project-grid',
	'dataProvider'=>$model->search(),
	'filter'=>$model,
	'columns'=>array(
		array(
			'name'=>'location_city_title',
			'value'=>'$data->location->city_title',
		),
)); ?>


Finally
  • Make new "property" (required)
  • mark as "safe" in 'on'=>'search' scenario in rules method (required)
  • set name in attributeLabels method (optional)
  • add condition with "with" and "together" in search function (required)
  • set "name" in "columns" as the "property" name(required)


I've tested it with MANY_MANY realtion but I'm qite sure it works with other types.



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

#7 User is offline   felipe_Brz 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 02-January 12
  • Location:Rio de Janeiro - Brazil

Posted 07 August 2012 - 03:40 PM

How do I vote for this post???

This is very useful indeed!

Thanks to this merciful soul!!
0

#8 User is offline   huanito 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 48
  • Joined: 21-November 10

Posted 10 December 2012 - 09:40 PM

View Postfelipe_Brz, on 07 August 2012 - 03:40 PM, said:

How do I vote for this post???

Easy: Click the plus button on the bottom right of the post...
0

#9 User is offline   huanito 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 48
  • Joined: 21-November 10

Posted 10 December 2012 - 09:48 PM

View Posttanimgt, on 06 December 2011 - 03:42 PM, said:

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




I think you don't need the filter line:
  'filter'=>CHtml::listData(StudentTbl::model()->findAll(), 'student_name', 'student_name'),

make that part just:
            	array(
                	'name' => 'name',
                	'value' => '$data->student->student_name',
            	),

0

#10 User is offline   putranovan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 22-May 14

Posted 10 June 2014 - 02:16 AM

this thread give detail information .. thank you.

View Posthuanito, on 10 December 2012 - 09:40 PM, said:

Easy: Click the plus button on the bottom right of the post...


didn't find plus button ..
instead i found rate topic then i gave 5 star for this thread.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

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