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!

Updating a Listview in Yii with Ajax

This was an unbelievable exasperating feature to add. I have no idea why this didn't work in the myriad ways I tried to make it work, but I've got it working, now, so I wanted to post.

I looked at every example of updating a CListview that I could find on Google. None of them were doing what I wanted to do (and what I'd assumed was a very common task). In a nutshell, I wanted to have a listview on my view page and then update the results when someone clicked on a link. All of the examples I saw involved submitting a form. I saw one that was using a link, but the details provided in how they solved it were so miniscule as to be almost humorous (had I not been so frustrated).

I won't discuss this solution in great detail. I'll simply include the information below.

The View
At the top of the index.php view, I have this:

<?php

Yii::app()->clientScript->registerScript('ajaxUpdate',
"
$('.ajax_link').click(function(){
$.fn.yiiListView.update('companyList')
});
return false;
", CClientScript::POS_READY);
?>

Inside the view itself is the listview:


<?php $this->widget('zii.widgets.CListView', array(
'dataProvider'=>$dataProvider,
'itemView'=>'_view',
'id'=>'companyList',
)); ?>

The links are generated in the view, too, of course. They look like this:


foreach($allLetters as $letter) {
if(in_array($letter, $dbInitials)) {
/*
echo CHtml::ajaxLink(strtoupper($letter), CHtml::normalizeUrl(array('index', 'initial'=>$letter)),
array('success'=>"$.fn.yiiListView.update('companyList')")
);
*/
echo CHtml::link(strtoupper($letter), CHtml::normalizeUrl(array('index','initial'=>$letter)), array(
'class'=>'ajax_link',
));
} else {
echo strtoupper($letter);
}
// Add some whitespace.
echo '&nbsp;&nbsp;';

}

In fact, that's pretty much all there is in my index.php page. A Javascript portion that is registered as a client script, a list view with the id set to companyList so I can refer to it later, and a series of basic html links with a class of ajax_link so that the click event is triggered in the Javascript.

On the server side, the controller action for index looks like this:

public function actionIndex($initial='')

{

if($initial) {

$criteria = new CDbCriteria;

$criteria->compare('name', $initial . '%', true, 'AND', false);

} else {

// Retrieve all companies to list

$criteria = new CDbCriteria;

$criteria->with=array('video','image','state');

$criteria->together = TRUE;

}

// Only admin users can see ALL records. Others can only see approved records.

if(!Yii::app()->user->checkAccess('admin')) {

$criteria->addCondition('t.isApproved=:isApproved');

$criteria->params[':isApproved']=1;

}

$dataProvider=new CActiveDataProvider('Company', array('criteria'=>$criteria));

$this->render('index',array(

'dataProvider'=>$dataProvider,

'dbInitials'=>$this->dbInitials,

'allLetters'=>$this->allLetters,

));

}

If no parameters are submitted, it just renders all of the results. If an initial letter is submitted, it filters the results. Either way, the index view is rendered.

The main problems I encountered were that:

1. The AJAX request would return the results I wanted, but the div wouldn't update.
2. Once I finally got the div to update, I was getting an error from the yiiListView.update() method. Adding the "return false" in the Javascript code fixed that.

Done.

Yii: Selecting First Letter of a Column in Each Row

At some time or other, I'm sure you've browsed names of companies or people and were presented a list of initials to filter your results by letter or other character. If the search results don't contain any companies or people with a particular letter, it is nice when that letter is inactive, that is when it doesn't link to anything. Otherwise, you just get an annoying "no results" response. Shouldn't the application have known in advance that there were no people in the database whose name started with Z? Now, you've wasted your valuable time clicking it for nothing!

I'm working on building just such a list of letters and characters, and it's surprising to me, but I've never actually gone through this exercise of creating the list with anchor tags only around those letters that correspond to values in my db. I'm going to blog my steps in short posts. This will cover only retrieving the initials. Subsequent posts will cover the steps to output the results.

I'm pulling from a table called "Company." I want just the first letter of each company name. In this case, the name is stored in - can you guess? - the "name" column. So, my basic SQL looks like this:

"SELECT DISTINCT substring(name,1,1) AS initial FROM Company"

This should return the first letter (a substring of one character, beginning with the first character) of the name column from the table Company. 'DISTINCT' ensures I only get one of each.

But I'm using Yii, so I need a way to execute this query the Yii way. (Or one of the Yii ways, anyhow.) I decided to use CDbCommand with a series of available setters. Here's what that looks like:


$dbCommand = Yii::app()->db->createCommand();
$dbCommand->setSelect(array('substring(name,1,1) as initial'));
$dbCommand->setFrom('Company');
$dbCommand->setDistinct(true);

That's pretty straightforward, if you have taken a look at the CDbCommand info in the Yii documentation. In a nutshell, I've instantiated the command object. Next, I use setSelect() to provide my select information. In this case, I'm using an array. You could include it as a string (e.g., $dbCommand->setSelect('myname as name'). I'd come across a post that suggested that there were issues with using the string, however, and  so I decided to just go with an array.

The call to setFrom() is probably self-explanatory; it just provides the "from" values for the query. I used a string, because I had only one table. More than one value in from would require you to use an array instead.

The use of setDistinct() is so that I can keep my results, well, distinct. It takes a boolean (true or false) as it's value. I want it to return distinct results (only one of each), so I set it to true.

Oh, you might have noticed that I used Yii::app()->db->createCommand() instead of new CDbCommand. This is a convenient way of taking advantage of the 'db' component I've already set up for my app. If I were to instantiate a new CDbCommand object with the "new" keyword, I'd have to supply the connection information. By using the createCommand() function, I can avoid all that and just use the connection info set up in my app. (If you were connecting to a different database, you'd need to do this the long way with "new.")

OK, now how do I get the results? I've built up the command itself. Now, I just need to actually execute it. I went with one of the convenience functions provided by CDbCommand, "queryColumn()." It returns one column of results, which is exactly what I need, and stores it in an array, like so:

$dbInitials = $dbCommand->queryColumn(); // First letter of the company names from the db

If I had wanted only the first row of information, I could have used queryRow(). The queryScalar() function returns just the first column from the first row. There are a couple of other query functions, and there's also just plain execute(). Check them out in the documentation to see what works best for your situation.

So, that's it for this installment. I've now got an array of letters that I can loop through and create links for filtering. Of course, I want to include the other letters that didn't end up in my $dbInitials array, but I'll save that for the next post.

Feel free to post suggestions if you have better ideas of how to do this. I'd love to hear how this could be made more efficient or otherwise more intelligent. Thanks!