Drupal 7 Views Sort by Two Date Fields

To achieve this you will need to implement hook_views_query_alter() in a custom module. I did not do this in Views UI.

A recent project at Headscape required me to build view listing two different content types that would use two different date fields to sort by. For examples I’ll use the default basic page and article content types.

The Problem

Some content types need to be sorted on the node created date and others on the display date field.

Trying to use the Views UI to add the two sort fields would result in first ordering by node created, then ordering by display date which is not the result we were after.

The Solution

First we need to add a new field that we can sort by. This field will have the alias order_date.

To create it we check if the display date field is null, if it is order_date gets the node created timestamp. If display date has a value we convert it to a Unix timestamp and that value is assigned to order_date.

In hook_views_query_alter we need this code

$query->fields[] = array(
  'field' => 'IFNULL (UNIX_TIMESTAMP(field_data_field_display_date.field_display_date_value), node.created)',
  'alias' => 'order_date'
);

Next we need to add the order by clause. As this is the only field we want to order by we completley over write the order by array.

$query->orderby = array(
  array(
    'field' => 'order_date',
    'direction' => 'DESC',
  )
);

Put together hook_views_query_alter will look like this.

/**
 * Implements hook_views_query_alter().
 */
function MYMODULE_views_query_alter(&$view, &$query) {

if ($view->name == 'YOUR_VIEW_NAME') {

  $query->fields[] = array(
    'field' => 'IFNULL (UNIX_TIMESTAMP(field_data_field_display_date.field_display_date_value), node.created)',
    'alias' => 'order_date'
  );

  $query->orderby = array(
    array(
      'field' => 'order_date',
      'direction' => 'DESC',
    )
  );

 }

}
This entry was posted in Web Development and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *