Sort View by two date fields

Updated: Comment #3 by dooug

Problem/Motivation

I am trying to sort nodes by the combination of two different date fields. The View can be sorted by one date field, but using two date fields in the sort will sort by one field first, then the other.

I want to achieve this: (Nodes are being sorted by date, no matter if the date is from "date-field 1" or "date-field 2")

• Node 1 (date-field 1: 01. 2012)
• Node 2 (date-field 2: 02. 2012)
• Node 3 (date-field 2: 03. 2012)
• Node 4 (date-field 2: 04. 2012)
• Node 5 (date-field 1: 05. 2012)
• Node 6 (date-field 2: 06. 2012)
• Node 7 (date-field 1: 07. 2012)
• Node 8 (date-field 2: 08. 2012)
• Node 9 (date-field 1: 09. 2012)

By setting up views "sort" panel adding the two date fields will render the following view:

• Node 1 (date-field 1: 01. 2012)
• Node 5 (date-field 1: 05. 2012)
• Node 7 (date-field 1: 07. 2012)
• Node 9 (date-field 1: 09. 2012)
• Node 2 (date-field 2: 02. 2012)
• Node 3 (date-field 2: 03. 2012)
• Node 4 (date-field 2: 04. 2012)
• Node 6 (date-field 2: 06. 2012)
• Node 8 (date-field 2: 08. 2012)

It sorts the date field correctly, but wont combine the date-fields. So the Dates are getting mixed up completely.

There appear to be a handful of requests for this, including but not limited to:
#378694: sort by date, combining fields →
http://stackoverflow.com/questions/12578343/drupal-views-multiple-sort-c...
http://stackoverflow.com/questions/11769902/drupal-7-sorting-multiple-co...
http://drupal.stackexchange.com/questions/32679/how-do-i-sort-two-differ...

Proposed resolutions

Summary of possible solutions / work-arounds:

1. Combining date fields so there is only one date field to sort by. As necessary, hiding elements that are unnecessary for some field instances. Suggested here: http://skvare.com/blog/sort-multiple-formats-date-fields-views
2. Similar to the last but keep the existing separate date fields, then do custom coding to sync/hide the multiple fields on a content type so one field can be used to sort. Suggested here: https://drupal.org/comment/6927200#comment-6927200
3. Use either views_php or computed_field to write custom PHP to sort the view. Suggested here: https://drupal.org/comment/4558482#comment-4558482

(likely) Best solution (at the time of writing this):
In a custom module use the hook_views_query_alter() function to alter the sort criteria to use CASE ... WHEN to add a conditional in the orderby condition. A good example is shown here:

/**
 * Implements hook_views_query_alter
 * @param type $view
 * @param type $query
 */
function MODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'views_name' && $view->current_display == 'display_name') {
    $query->orderby = array(
      array(
        'field' => 'CASE WHEN field_data_field_date_publication.field_date_publication_value THEN field_data_field_date_publication.field_date_publication_value ELSE node.created END',
        'direction' => 'DESC',
      )
    );
  }
}

Credit goes to eloone here: https://drupal.org/comment/6443200#comment-6443200