[Drupal] How to use Drupal hook_views_query_alter to change views sorting

Have you ever felt the need to change the sorting order of a View?. The most common method to change the sorting order would be to edit the corresponding View template file. Now there is a more elegant solution for doing that instead of editing a view template file. Read on to know how to use Drupal hook_views_query_alter to change views sorting.

I had a view result which displays taxonomy terms and its content. But here terms are created in a hierarchical manner. There will be a parent and child terms. There are also some terms which doesn't have the child terms. Here the view was sorted by parent weight and then sorted by child term weight. My view result was showing all parent terms first and then child terms even after I apply relationship. My requirement was to show all child terms order by its weight, remove parent terms and show parent terms which doesn't have the child terms. So I started fixing this issue by a view table template file by hard coding the term name and unsettling it. But every this was fine until I realize that pagination was not proper for my view result. Because I was trying to unset some view result and applying pagination. So if I reduce number of items per page to 2 or 3, my first page was empty because same page contains the rows which were unset by template. Here the best method to manage this complex sorting was altering the view result itself.

In my view I was sorting the result by two columns first was taxonomy_term_data_taxonomy_term_hierarchy.weight and second one was taxonomy_term_data_node.weight. Here the first column was NULL for parent terms. Due to this parent will appears as the first in result set. We know that when we sort a column in which has NULL values in ASC order, it will show up first in result. Similarly if it's DESC order then NULL values will displays in the last.

This is the case where add_where and orderby with CASE come in handy. Here add_where adds a where clause for the view's SQL statement to unset the terms which we do not want to display and orderby with CASE will adds conditions to ORDER BY statement of SQL query.

For example, here I will use a sample code to show how where and orderby statements are used.

function mymodule_views_query_alter(&$view, &$query) {
  // Term id's to unset from result set.
  $tids = array(368,346,351,355,358,365);
  if (($view->name == 'yourviewname') && 'page' == $view->current_display) {
    $query->add_where(1,'taxonomy_term_data_node.tid', $tids, 'NOT IN');
    $query->orderby[0]['field'] = "CASE WHEN taxonomy_term_data_taxonomy_term_hierarchy.weight IS NULL THEN taxonomy_term_data_node.weight ELSE taxonomy_term_data_taxonomy_term_hierarchy.weight END";
    $query->orderby[0]['direction'] = "ASC";
    $query->orderby[1]['field'] = "taxonomy_term_data_node.weight";
    $query->orderby[1]['direction'] = "ASC";
  }
}

Here add_where statement will adds an additional where clause to current where conditions and orderby will alter the orderby statement to reorder the result.Also orderby[0] will alter the first part and orderby[1] will alter the second part of orderby clause. In first ordering if the value is NULL then use second column ordering else use first column ordering, also order by second column in ASC. It's clear that non-null values comes first and we get the desired sorted result. By using CASE statement on our view_query_alter we can have a full control on our result set. I hope this article was helpful to understand view_query_alter and using CASE in ordering. Thanks for reading.