Order By Multiple Meta Fields

I have two custom fields:

• meta_key1 is boolean (0 or 1).
• meta_key2 is a date value – 20150623 for example.

Not all meta_key2 fields have an explicit value. It looks like their being treated as an empty string ''.

I’d like to find all posts that have a meta_key2 (date) value that is greater than today or have a meta_key1 value that is true.

This is the order I’d like them to display.

1. key2.meta_value >= today ASC
2. key1.meta_value = TRUE ordered by date ASC (those with dates given first).

Part of the issue is that fields with an empty date parameter appear to be treated as 0 and thus come first in ASC order. I’ve attempted to use COALESCE to account for this and have had some success but I’m hung up on one part. I can’t get the posts that key1.meta_value = FALSE and key2.meta_value is >= today to display in ASC order.

Here’s my query:

$meta_key1 = 'prog_ongoing';
$meta_key2 = 'prog_date_start';
$start_date = date('Ymd');

$postids = $wpdb->get_col( $wpdb->prepare( 
"
SELECT      DISTINCT key1.post_id
FROM        $wpdb->postmeta key1
INNER JOIN  $wpdb->postmeta key2
            ON key2.post_id = key1.post_id
            AND key2.meta_key = %s
WHERE       key1.meta_key = %s
            AND key1.meta_value is TRUE
            OR key2.meta_value >= %d
ORDER BY    COALESCE(NULLIF(key1.meta_value, 0), 0) DESC, COALESCE(NULLIF(key2.meta_value, ''), $start_date) ASC, key2.meta_value ASC 
",
$meta_key2,
$meta_key1,
$start_date
) );

I’m not sure my COALESCE statement on key1.meta_value is doing anything. Thanks for any insight on this.

One problem with the query is that the self-join with the ambiguous WHERE gives you a crossed dataset (which is masked by the DISTINCT), so it would be simpler to use wp_post as a base to attach the joins that precisely match the keys, eg

    SELECT      p.ID, key1.meta_value as prog_ongoing, key2.meta_value as prog_date_start
    FROM        $wpdb->posts p
    INNER JOIN  $wpdb->postmeta key1
                ON key1.post_id = p.ID
                AND key1.meta_key = %s
    INNER JOIN  $wpdb->postmeta key2
                ON key2.post_id = p.ID
                AND key2.meta_key = %s

which gives a linear dataset. Then you could add (or not) the WHERE clause to restrict the data:

    WHERE       key1.meta_value IS TRUE OR key2.meta_value >= %d

and for the ORDER BY use a CASE statement single field sort:

    ORDER BY    CASE
                    WHEN key2.meta_value >= %d THEN CONCAT('A', key2.meta_value)
                    WHEN key1.meta_value AND key2.meta_value THEN CONCAT('B', key2.meta_value)
                    WHEN key1.meta_value THEN 'C'
                    ELSE 'D'
                END ASC

or something similar, the above needing the prepare args to be:

    $meta_key1,
    $meta_key2,
    $start_date, $start_date

You could use the posts_orderby filter to do something similar using WP_Query (although it uses a method that produces crossed datasets, requiring it to have to use a GROUP BY, which can complicate things). For instance

$args = array(
    'posts_per_page' => -1,
    'post_type' => 'cpt_program',
    'meta_query' => array(
        'ongoing' => array(
            'key' => 'prog_ongoing',
        ),
        'start_date' => array(
            'key' => 'prog_date_start',
        )
    ),
);

add_filter( 'posts_orderby', $func = function ( $orderby, $query ) {
    $start_date = date('Ymd');
    global $wpdb;
    $orderby = $wpdb->prepare(
        "
        CASE
            WHEN mt1.meta_value >= %d THEN CONCAT('A', mt1.meta_value)
            WHEN {$wpdb->postmeta}.meta_value AND mt1.meta_value THEN CONCAT('B', mt1.meta_value)
            WHEN {$wpdb->postmeta}.meta_value THEN 'C'
            ELSE 'D'
        END ASC
        "
        , $start_date
    );
    return $orderby;
}, 10, 2 );
$query = new WP_Query( $args );
remove_filter( 'posts_orderby', $func, 10, 2 );