Sorting posts by meta values: 2 different orders

Question

I have a meta value for a custom post type called workshop which is the start_time of the workshop as a UNIX timestamp. Right now I'm able to sort the posts ascending or descending with

$query->set('posts_per_page', -1);
$query->set('meta_key', 'start_time');
$query->set('orderby', 'meta_value_num');
$query->set('order', 'ASC');

What I would like to do is sort it 2 different ways since some workshops may be the in future and some may have passed already. So my ideal sorting would be:

1. First the workshops that are still in the future, ie start_time >= time() sorted ASC
2. Then the workshops that are not in the future, ie start_time < time() sorted DESC.

But they would still show up in the same query. Is there a way to do this?

Answer 1

I think it should be able to hook into the posts_clauses filter to add a field to your sql query.

add_filter( 'posts_clauses', 'intercept_query_clauses', 20, 1 );

function intercept_query_clauses( $pieces ){
    global $wpdb;
    $pieces['fields'].=', (IF(UNIX_TIMESTAMP()>='.$wpdb->postmeta.'.meta_value,'.$wpdb->postmeta.'.meta_value,(UNIX-TIMESTAMP() - '.$wpdb->postmeta.'.meta_value)+UNIX_TIMESTAMP('2099-01-01'))) as timestamp_direction';
    $pieces['orderby']='timestamp_direction ASC';
    return $pieces;
}

The logic is as follows:

Let's say you have some timestamps in your metafield

event 1 (way past) : 9999

event 2 (past) : 10000

timestamp now: 11111

event 3 (future): 12222

event 4 (further into future): 13333

timestamp of 2099-01-01: 100000

We build a temporary field in our query that has the timestamp from the metafield if it is higher than the current timestamp. If the metafield is lower than the metafield, it first gets subtracted from the current timestamp and then the timestamp of a date way way into the future. After that, we have the following values:

event 1: 101112

event 2: 101111

event 3: 12222

event 4: 13333

Now ordering this by timestamp_direction gives us the order that you want: first the events in the future ordered ASC, then the past events ordered DESC:

event 3: 12222

event 4: 13333

event 2: 101111

event 1: 101112