I’ve had to fix some interesting problems at work recently, related to a Drupal site that we’ll be rolling out soon. I just finished fixing one issue that, while seemingly minor, took quite a while to figure out.

I’m really glad to have come up with a good solution. The thing that amuses me most about this is that, after more than eight hours of messing around, the final solution involved writing only about a half-dozen lines of code.

The problem, in a nutshell, is that we have a content type in the system that represents a university. There’s a location field on each node with, minimally, city and country specified. The user can search for locations, using some custom search code, and the search results are displayed via a standard Drupal view. We allow the user to sort the results by one of a few different fields, with the sort drop-down exposed from the view. This works fine, except when sorting by country. On the location record, only the two-letter country code is stored, for instance “AE” for “United Arab Emirates”. So, when you sort by country, it’s really sorting on country code, so “AE” goes to the top, which isn’t really what the client wanted.

Of course, the first thing I did was Google the problem. I found this issue discussion, which pretty much matches my problem. There was a suggestion in the comments there about using hook_views_pre_render to re-sort the results right before displaying them. That works great, if you’re not paging results. But, if you’re pulling results back one page at a time from a large result set, this doesn’t work, since the pre-render hook only gives you the current page.

So I figured out that I really need to sort by country name at the SQL level, while retrieving results. This led to my next problem, which is that, even with the location module installed, there’s no SQL country lookup table in Drupal. The list of country codes and names is just stored in code, in an array, which can be retrieved via _country_get_predefined_list. (You shouldn’t call that directly, though, of course; you should use country_get_list.)

So off I went to find a module that could give me a SQL table with country info in it. The countries module does that, and a bit more. So, I installed that and figured out where the country table was. Then, my next blind alley was figuring out how to join to the new country table in a view. I was hoping I could just add a join to it in the view definition, and go from there. Well, I still don’t know that much about Drupal views, and it didn’t seem possible to do that easily.

So, the next blind alley was to see if I could alter the view SQL with hook_views_query_alter, which seemed sensible. Well, the query object that you get from that hook isn’t a nice simple query object that can easily be changed, so that turned out to be another dead end. (It’s likely possible that I could have figured it out, but it seemed like the wrong approach.)

Then, finally, I stumbled across this SO question. The one answer posted there led me in the direction of modifying the query with hook_query_alter, which can be used to modify just about any query Drupal issues to MySQL. So, finally, I found a workable solution.

hasAllTags('views', 'views_university_search')) {
    $ord =& $query->getOrderBy();
    if (array_key_exists('location_country', $ord)) {
      $query->addJoin('INNER', 'countries_country', 'cc', 'cc.iso2 = location.country');
      $ord = array('cc.name' => $ord['location_country']);
    }
  }
}

So that’s it. I add a join, and replace the ‘order by’ clause. About a half-dozen lines of code. Oh, and I now also understand passing by reference in PHP a little better too!