Help ordering Post loop by two meta values

Question

Background: I have a custom post type "events" which has a post meta value of a unix timestamp called event start date. I also have a post meta value of ticket_status that has 3 possible values (S=Sold Out, Y=On Sale, I=Invite Only). I currently have it sorting by the event start date from earliest to latest.

Issue: I want the events to display from earliest to latest as I have them now, but I want all sold out events to appear at the bottom of the list.

I currently have this as my $args as:

'post_type' => array('events'),
'posts_per_page' => 25,
'post_status'     => 'publish',
'meta_key' => "event_start_date",
'orderby' => "meta_value title',
'order' => 'ASC'

I also have a filter which is returning $orderby:

$orderby .= ", wp_postmeta.meta_value " . $query->query_vars['order'];

I have tried ordering by event_start_date then by ticket_status, but then it orders them by 1)On sale events, 2)invite events and then 3)sold out events. This results in the events showing in the wrong chronological order.

Is it possible to do a case statement? Similar to this:

ORDER BY CASE WHEN ticket_status = 'S' THEN 1 ELSE 0 END, event_start_date ASC

Anything that can help me do this would be awesome! Thank you in advance.

Answer 1

If you're using Wordpress 4.2 or newer then you can do the following:

$args = array( 
    'post_type'       => 'events',
    'posts_per_page'  => 10,
    'meta_query'      => array(
        'relation'    => 'AND',
        'start_date_clause' => array(
            'key'     => 'start_date',
            'compare' => 'EXISTS',
        ),
        'ticket_status_order_clause' => array(
            'key'     => 'ticket_status_order',
            'compare' => 'EXISTS',
        ),
    ),
    'order_by' => array(
        'start_date_clause' => 'ASC',
        'ticket_status_order_clause' => 'ASC',
    )
);

You have to create a ticket_status_order because there's no way to order by string from your available keys/values:

ASC: (I=Invite Only, S=Sold Out, Y=On Sale) DESC: (Y=On Sale, S=Sold Out, I=Invite Only)

"Sold Out" always sit in the middle.

For more information about the query, take a look here: Query improvements in WP 4.2: ‘orderby’ and ‘meta_query’