GridView Filtering of Relational Data

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

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.

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!

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!

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

            	),

        	),

    	));

    	?>



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

            	),

        	),

    	));

    	?>



How do I vote for this post???

This is very useful indeed!

Thanks to this merciful soul!!

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

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

            	),



this thread give detail information … thank you.

didn’t find plus button …

instead i found rate topic then i gave 5 star for this thread.