Custom Views Sort Plugin for Upcoming Events

This week, our client came up with a seemingly simple request that turned out not so simple: When listing events, they want to show upcoming events before past ones. Not only that, they also want events nearest the current day to show up first.

We could implement this with two separate views, but because the view is already fully configured and has many displays, that's impractical. Instead, we'll have to implement an unusual custom sort.

The Solution

At first, I wasn't even sure whether sorting like this was possible. But after playing around with the database a bit, I hit on an idea. Here's the query that helped me realize what to do:

SELECT
  title,
  FROM_UNIXTIME(field_event_date_value) AS date,

  -- Is this event in the past? Compare to current timestamp.
  UNIX_TIMESTAMP() > field_event_date_value AS is_past,

  -- How far in the past/future is this event? Use absolute value.
  ABS(UNIX_TIMESTAMP() - field_event_date_value) AS distance_from_now

FROM node n
  JOIN field_data_field_event_date d ON n.nid = d.entity_id
ORDER BY date DESC;

This yields a result like this:

+---------------+---------------------+---------+-------------------+
| title         | date                | is_past | distance_from_now |
+---------------+---------------------+---------+-------------------+
| Far future 2  | 2020-02-03 19:00:00 |       0 |         116122045 |
| Far future    | 2020-01-01 19:00:00 |       0 |         113270845 |
| Medium future | 2016-08-31 20:00:00 |       0 |           8035645 |
| Soon!         | 2016-06-09 20:00:00 |       0 |            864445 |
| Recent past   | 2016-05-19 20:00:00 |       1 |            949955 |
| Far past      | 2013-01-01 19:00:00 |       1 |         107567555 |
| Far past 2    | 2012-04-02 20:00:00 |       1 |         131241155 |
+---------------+---------------------+---------+-------------------+

That makes it clear what we want to do: order by those two fields. So I can write a views sort handler that does just that:

class event_sort_handler_sort extends views_handler_sort {
  function query() {
    $this->ensure_my_table();

    $date_alias = $this->table_alias . '.' . $this->real_field;

    // Are we in the past?
    $this->query->add_orderby(NULL,
      "unix_timestamp() > $date_alias",
      $this->options['order'],
      $this->table_alias . '_in_past'
    );

    // How far are we from the current time?
    $this->query->add_orderby(NULL,
      "abs($date_alias - unix_timestamp())",
      $this->options['order'],
      $this->table_alias . '_date_distance'
    );
  }
}

We have to tell views about our handler, by using a hook_views_data_alter() and adding our handler to our .info file, you can see the result on GitHub. Then we get the sort we want!

Conclusion

As a result, instead of vastly complicating our views configuration and theme, we've got a straightforward custom module that does what we want, easier to maintain (it's just a handful of lines), and reusable for other projects.

Do you disagree we've taken the right approach here, from a maintainability perspective? We love controversy, tell us why in the comments!

Are you looking for a Drupal developer job and love writing solutions like this? We're hiring, so apply to work with us soon! But if you do, be ready for a little Drupal code test to challenge you.