How to modify a Drupal 7 view query to order by two columns simultaneously

I’m working on a site that uses Views to pull in lists of content.  One view pulls in a list of Blog posts, News Items, and Podcasts.  I need to sort the content by date, but there’s a problem…

The sorting field on News Items and Podcasts is a Date field named ‘Publication Date’, but this field doesn’t exist in the Blog posts.  I need our view to sort by node.created and our custom Publication Date field simultaneously.

MySQL COALESCE() and hook_views_query_alter() to the rescue!

Step 1: create and enable a custom module with this structure:

my_module
my_module/my_module.init
my_module/my_module.views.inc

Step 2: create your view.  I created a view called ‘Related Content’ with a display called ‘news_and_updates’.

Important make sure you add both fields to the SORT CRITERIA section of the view.  This will ensure that the query joins on the correct tables.

Step 3: add this to my_module.views.inc

/**
 * Implements hook_views_query_alter()
 */
function my_module_views_query_alter(&$view, &$query) {
  // this is the name of our View and Display
  if ($view->name == 'recent_content' && $view->current_display == 'news_and_press') {
    // dpm($query);

    // this is our custom field
    $field_name = 'field_publication_date';

    // override the ordering
    $query->orderby = array(
      array(
        'field' => 'COALESCE(UNIX_TIMESTAMP(field_data_' . $field_name . '.' . $field_name . '_value), node.created)',
        'direction' => 'DESC',
      )
    );
  }
}

and that should do it!

Two things to note here:

  1. We are overriding all of our view’s SORT CRITERIA.  Enable Devel and uncomment the // dpm($query); line above to see what other options you have.
  2. Using COALESCE() in ORDER BY may hinder performance because it doesn’t allow us to use indexes on our sorting fields.  An alternative solution could be to add an extra field to all of the content types we’re using in this view and sort by that field.  I thought this, but our content model is already pretty complicated and it seemed too cumbersome.  Our dataset is relatively small, and we’re caching everything, so I don’t think this is going to be too much of a performance hit.

Hope this helps someone out there!

Comments

    sticky |

    thnx. it was very helpful to me… :-)