Using hook_views_query_alter to change your views sort order

Sometimes you might need some extra help getting views to sort correctly. This is especially the case when sorting on a column that might include NULL values, or when your criteria for how results should sort is complex. Instead of implementing template logic to correct the result sorting, you can use hook_views_query_alter to modify the sort criteria in the query itself.

For example, lets say that you have a bunch of items on your website, and have created a nodequeue that contains featured items. When you list all of your items, you want to have those featured items listed first.

Sometimes you might need some extra help getting views to sort correctly. This is especially the case when sorting on a column that might include NULL values, or when your criteria for how results should sort is complex. Instead of implementing template logic to correct the result sorting, you can use hook_views_query_alter to modify the sort criteria in the query itself.

For example, lets say that you have a bunch of items on your website, and have created a nodequeue that contains featured items. When you list all of your items, you want to have those featured items listed first.

If you add the nodequeue as a relationship in your view, and sort on nodequeue position in ascending order, you won’t get the results that you desire. The items that are in the nodequeue will be sorted in ascending order, but they will appear as the last results, not the first.

This is because the results that aren’t in the nodequeue have a value of NULL for that column. When MySQL sorts a column in ascending order that includes NULL values, those values show up at the top of the result set. Conversely, sorting a column in descending order puts the NULL values last.

This is where hook_views_query alter and MySQL’s CASE statement come in handy. hook_views_query_alter allows you to alter the views sql statement, and MySQL’s CASE statement adds a conditional block to your statement.

In this example, we can add a views_query_alter hook in one of our modules, and add the following statement:

$query->orderby [0] = 'CASE WHEN nodequeue_nodes_node_position Is NULL Then 1 Else 0 End, nodequeue_nodes_node_position ASC';

This statement alters the first part of the ORDER BY clause of the SQL statement to reorder the sort results. All values of NULL will get added to one subset of results at the end of the list, and non-NULL values will come first.

The CASE statement also comes in handy when you need to do complex ordering of query results. Let’s say that you have a column that contains a string with the type of animal. If you want to order the nodes so that the results are sorted by this column, but not in either ascending or descending order ( “cat”,“dog”,“bird” ), you can add the CASE statement to your ORDER BY clause. In this case, your SQL would look something like

$query->orderby [0] = 'CASE table.type WHEN 'cat' THEN 1 WHEN 'dog' THEN 2 WHEN 'bird' THEN 3 ELSE 4 END';

Using the CASE statement, you can have complete control over the sorted result of your queries.

Brad Blake

Brad Blake