Please, keep in mind this post was written for Drupal 6 and Views 2! D7/Views 3 sites might not take so kindly to it.

Recently I was looking at creating a new Todo Feature with a due date. I cracked open CCK’s manage fields UI and added a date field, careful to keep in mind that the default value should be no date, which just happens to translate as NULL. You see, for my Todo use case, not all Todos would have a deadline.

My next step was to create a View of all upcoming items. I wanted the next most urgent todo to float to the top of the list. Sadly, NULL counts as 0 in database land, so my carefully clicked Sort was preloading all my urgent todos with all the lowest priority tasks.

Seeing as this was a SQL problem, I googled the ‘net for viable query tweaks. I found a nice article illustrating exactly what I wanted: MySQL Sort Order with NULL. The grand secret? Sort first by whether the duedate is NULL to flip your empty values to the bottom of the result set.

The fastest way for me to apply this tweak to my feature was to hack the Views query. hook_views_query_alter() is usually a horrible decision. Once you’ve put it into code, tweaking your View a lot around your alteration can quickly result in a broken query. Meanwhile:

/**
 * Implementation of hook_views_query_alter().
 */
function example_views_query_alter(&$view, &$query) {
  if ($view->name == 'example_listing') {
    array_unshift($query->orderby, 'ISNULL(node_data_field_duedate_field_duedate_value) ASC');
  }
}

(For a longer use case on this Views bandaid, see Correcting views queries using views_query_alter)

I arrived here by turning on the Devel module, and dropping a dpm($query) into that function first thing. This gave me the array structure of my View, including the various SQL entries for the ORDER BY arguments. A quick and dirty array_unshift() forces the NULL check for my date field (the alias I also found in the $query object, there are more elegant methods) onto the top of the ordering hierarchy.

Bingo! NULL-dated nodes are sorted to last.

Terms: sqlviewsviews2drupal