Skip to content

Categories:

Customising CGridView – Using A Function To Display Related Data

It often happens with creating grid views that we have related data that we want to both display and filter. If your database is designed well, this relation is defined in your main table as an ID to reference the related table. For this example I will use a ‘user’ table, along with an ‘organisations’ table. The relation will be referenced in the ‘user’ table with the column ‘organisation_id’.

We don’t however just want to display the ‘organisation_id’ in our grid view, with a standard text input for filtering – we want the organisation name for each of our users, plus a drop down filter that lists the organisations.

The first thing we need is a standard GII generated style controller action for displaying our grid view:

	public function actionUsers()
	{
		$model=new User('search');
		$model->unsetAttributes();  // clear any default values
		if(isset($_GET['User']))
			$model->attributes=$_GET['User'];
		$this->render('users_grid',array(
			'model'=>$model,
		));
	}

Now the best way that I have found for displaying things like ‘organisation_name’ from a related table is to build an array to reference. First thing we will do is to pull all the organisations in our controller. With this we can build our array, plus we have the data available for our organisations drop down filter. We can add the following at the top of our controller action:

		$organisations = Organisations::model()->findAll(array('order' => 'organisation_name'));
		$organisations_array = array('0' => '');
		foreach($organisations as $val) {
			$organisations_array[$val->id] = $val->organisation_name;
		}

We then pass both to our view like this:

$this->render('users_grid',array(
			'model'=>$model, 'organisations' => $organisations, 'organisations_array' => $organisations_array,
		));

Next we need to add the column to our CGridView. To display our ‘organisation_name’ requires the use of a function within the ‘value’ attribute:

'value' => function($data,$row) use ($organisations_array){ return $organisations_array[$data->organisation_id];}

Now one may ask “Why don’t you just put a relation in your model and use ‘with’ in your ‘search()’ method? Then you can reference it like this”:

$data->organisations->organisation_name

Simply because this performs much better. Yii builds abominable ‘count()’ queries once you begin joining tables, which perform very badly (you can create your own count queries, but that’s another story). We need the organisations for our filter anyway, so why do a JOIN?

Our filter is built very simply:

'filter' => CHtml::listData($organisations, 'id', 'organisation_name')

Thus the full code for our new column is as follows:

array('name' => 'organisation_id', 'type' => 'raw', 'value' => function($data,$row) use ($organisations_array){ return $organisations_array[$data->organisation_id];}, 'filter' => CHtml::listData($organisations, 'id', 'organisation_name')),

Well, that was easy, now get coding!

Posted in PHP, Yii Framework.


2 Responses

Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.

  1. Abhishek says

    In Other option,You can do like this,
    Take public variable organization_name,put it in search critaria,
    Define relation in relations, In search,

    $criteria->with = array('Rel_org_id');
    $criteria->compare('Rel_org_id.organization_name',$this->organization_name,true);
    // and that's it,Then in,cgridview,
    array('name' =>; 'organization_name',
    'value' => '$data->Rel_org_id->organization_name',
    ),

    You will get it.

    • Matti says

      I think you have missed the point entirely. I did mention that above, along with the reason for NOT doing it this way.

      Let me be clear:

      1. We need all the organisations for the drop menu in our filter, so why grab the data twice by using a JOIN?
      2. Once you begin using JOINs with Active Record, horrible things happen with count() queries, which are required for a CGridview – it severely impacts performance.



Some HTML is OK

or, reply to this post via trackback.


one × = one