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?

  • I'm off to bed now. I have done one such post, either here or on Stack Overflow regarding this. When and where I can't remember, so feel free to delve into my answers. In short though, you cannot do this by default with build in functionality – Pieter Goosen Oct 14 '15 at 19:27
  • Thanks @PieterGoosen. I couldn't find the answer in question so my hacky solution at this point is to just create another WP_Query after the main one for the custom post type and sort it DESC for start_time < time(). It works but I wish I had a better way. – Evan Hobbs Oct 14 '15 at 19:55

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.