Views Different Sort Orders

Question

I have a content type called events (obviously it keeps a list of events (conferences, meetings, etc).

I have a views-view--events-list.tpl.php file where I render a list of events. All of this is working perfectly.

What I need to do is sort events differently based on the event date. In specific, events that have passed (earlier than today's date), should be sorted descending - that is the most recent past event first and the event that is furthest back in history should be shown last.

For events that are still pending (in the future), the sort order is exactly the opposite - the event that will happen next should be first in the list and the event that is furthest in the future, should be shown last.

For example (assume today is 1/4/2016)

event 4: 5 April 2016 (future, the next date we will encounter)
event 6: 15 May 2016
event 5: 25 Dec 2016 (future, the last future event we will encounter)
event 1: 25 Feb 2016 (past, this was the most recent past event)
event 3: 2 Jan 2016
event 2: 24 Dec 2015 (past, this is the furthest past event)

I need to do this in one view (I do not want to create two views in code with different date ranges and sort orders; I know how to do that - I am trying to do it using just a clever sort order and the default views-view--events.tpl.php) template file.

My question then - how do I create different sort orders for different nodes (based on a field value such as date) in one view? Is this even possible?

Answer 1

I'm pretty sure this can't be done in the standard Views UI.

A bad solution would be to use the Views PHP module to programmatically alter the order of the view results, or perhaps the Views Raw SQL module to insert some sorting statements into the SQL, although I haven't ever tried that.

A good solution would be to implement hook_views_query_alter() in your own custom module and add your custom sorting order to the SQL query.

Going by this answer I think something like this might work, although I haven't tested it:

function mymodule_views_query_alter(&$view, &$query) {
  if ($view->name == 'my_view') {
    $query->orderby = array(
      0 => array(
        'field' => 'my_date_field < CURDATE()',
        'direction' => 'ASC',
      ),
      1 => array(
        'field' => 'greatest(my_date_field, CURDATE())',
        'direction' => 'ASC',
      ),
      2 => array(
        'field' => 'least(my_date_field, CURDATE())',
        'direction' => 'DESC',
      ),
    );
  }
}

You would obviously have to substitute in the names of your view and your date field.

Note that this code overwrites any sorting already defined by the view. You could of course add to the $query->orderby array rather than replacing it.