This week I ran into a problem I’d never run into before with a view. I needed to specify some custom sorting that the Views UI simply doesn’t have options for. Luckily, views provides a hook for just this situation.

One of our clients at CHROMATIC has a site that features their entire product catalog. Each product line has its own node type and each product page presents the user with a “Related Products” view. Here’s the rub: the results of these related products views needed to be sorted (grouped) by node type. That’s usually not a problem since you can just add a filter to sort by Node: Type, ascending or descending. But in this case, the client wanted an arbitrary order based on the current view. So, I needed a way to tell each view what order to use based on the view. Enter the hook_views_query_alter() function. This function lets you alter any views query before the query is actually run. You can basically change any part of the query. In this case, we’ll be altering the “ORDER BY”. Here’s how I handled this particular problem using the mySQL FIELD function:

<?php
function yourModule_views_query_alter(&$view, &$query) {
  switch($view->name) {
    case 'yourViewName':  		
      $query->orderby[0] = "FIELD(node.type, 'productTypeB', 'productTypeA', 'productTypeC', 'productTypeD') ASC";
      $query->orderby[1] = "node_title ASC";
      break;
    }
}
?>

To summarize that snippet: Switching on the name of the view ($view->name), I add two clauses to the Order BY portion of the query. The first defines the custom sort order for the node.type field. The second orders then by the node title. Note that this essentially clears out any existing sort filters from your view. Also, I'm no mySQL expert, but from what I can tell, the mySQL FIELD function might not be the best for performance. The point here is that you have the ability to alter any query for any view on your site with this nifty little hook. I wish I would have know about this a long time ago...