Searching Relations in Yii's CGridview

[NOTE: I'm typing this pretty frantically, just to get it out there and then move on to my next task! So, it might be a bit confusing. You should familiarize yourself with Yii's CGridView widget before trying to understand what the heck I'm rambling on about below.]

In the default grid view widget usage in admin.php (when you use gii to generate it), all of the fields displayed are directly tied to the model you're viewing. Many times, you don't want to see the value of a foreign key but rather the actual string that would be associated with that particular foreign element. For example, if I have a grid view to display a series of Company models, I don't want the "state" column to display "2" if it really could display "California." That's easy enough to fix by providing some additional information in the CGridView widget definition. Instead of including the state column as "stateId", include an array that specifies the column in the Company table ("stateId"), a header to display at the top of the grid column ("State"), and the value that should actually be shown ("$data->state->name" which represents the "name" column from the related model, "state," for the current data element - $data - of the many Company models returned):


<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'company-grid',
'dataProvider'=>$model->search(),
'filter'=>$model,
'selectableRows'=>2,
'columns'=>array(
'name',
'street',
'street2',
'city',
array(
'name'=>'stateId',
'header'=>'State',
'value'=>'$data->state->name',
),
'zipCode',
array(
'class'=>'CButtonColumn',
),
),
)); ?>

The problem, however, is that you lose the search field at the top of the column that is used for filtering the result set shown in the grid! To get that to show up again, you'll need to do a couple of other things. Both steps involve the model itself. In my case, I'm dealing with my Company model. Here goes.

First, you need to change the way the comparison is handled in the search() function when it comes to the model's state information. By default, you probably would have something like this:

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

But this doesn't get you what you want, because we're not really comparing the value of a foreign key with an integer that has been entered. (I mean, who's gonna enter an integer when they're searching for Georgia?) So, make it look like this:

$criteria->compare('state.name',$this->stateId, true);

This tells the comparison to compare the name of the state with the value returned from the incoming 'stateId' field (which is now going to be a textual representation of the state's name).

Notice that I'm using the "dot" notation, as in "state.name". This is the syntax for querying a table with the alias "state" for the value of it's "name" column, of course. The reason I can do this, here, is that I've already made sure that

1. I have a relation in Company with the State table that is referenced with the alias "state" (part of the Company model's relations() function that returns an array of relations).
2. I have included "with" in my search() function's criteria to let Yii know to join the State table when it selects the Company models:  $criteria->with=array('user','image','video','state','approvedBy0','deletedBy0');
3. I have included "together" in the criteria and set it to true: $criteria->together=true;

These are important for defining for Yii how to retrieve the related states information in a way that makes it available to search().

The other piece of the puzzle involves the rules() function of the Company model. You have to declare that the "name" column of the State table is safe on search. Most likely, you already have a rule for searching that looks something like this:

array('name, street, street2, city', 'safe', 'on'=>'search')

This means that the columns, "name," "street," and "street2" are safe to include for the search scenario. But we also want the "name" column from our State table to be safe. The way we add this is to take advantage of our relation (which has provided us with the alias, "state," for the State table). Because states are retrieved actively - since "with" includes the state alias and "together" is set to true - we can refer to it's member variables. To include the State tables "name" column in the search, then, just add it to the array:

array('name, street, street2, city, state.name', 'safe', 'on'=>'search')

That's all there is to it! You should now have:

• A column in your grid that displays the state's actual name, rather than the foreign key for the state
• A header at the top of the column that reads "State" instead of "stateId" (how gauche)
• A text field for searching on states that compares what you've typed in with the actual state name values from the available models.

I've seen examples of how to make the search field a pull down menu rather than a text field (which makes sense for states), but I'm not going to post on that, at the moment. I would point out, however, that you can also now sort the state names by clicking the "State" header. Sweet!