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!