Order By Multiple Meta Fields

Question

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.

Answer 1

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 );

Answer 2

First pass for a solution. It uses the new meta sorting that was introduced in 4.2:

 <?php
    $args = array(
        'posts_per_page' => -1,
        'meta_query' => array(
            'relation' => 'OR',
            'ongoing' => array(
                'key'     => 'prog_ongoing',
                'value'   => 1
            ),
            'start_date' => array(
                array(
                    'key' => 'prog_date_start',
                    'value'   => date('Ymd'),
                    'type'    => 'numeric',
                    'compare' => '>='
                )
            )
        ),
        'orderby' => 'start_date ongoing',
        'order'   => 'ASC',
    );

    $programs = new WP_Query($args);
    ?>
    <?php while($programs->have_posts()): $programs->the_post(); ?>
        <h1><?php the_title(); ?></h1>
    <?php endwhile; ?>

I think I got the logic right, but let me know if otherwise

EDIT

You can check out the new syntax to sort by multiple custom fields in Wordpress 4.2 in the following make

• Query improvements in WP 4.2 orderby and meta query

Answer 3

Why not use WP_Query()? Lot simpler this way:

    <?php
    $args = array(
        'posts_per_page' => -1,
        'meta_query' => array(
            'relation' => 'OR',
            array(
                'key'     => 'prog_ongoing',
                'value'   => 1
            ),
            array(
                'relation' => 'AND',
                array(
                    'key' => 'prog_date_start',
                    'value'   => date('Ymd'),
                    'type'    => 'numeric',
                    'compare' => '<='
                ),
                array(
                   'key' => 'prog_date_start',
                    'value'   => 1,
                    'type'    => 'numeric',
                    'compare' => '>'
                )
            )
        ),
        'orderby' => 'meta_value_num date',
        'order'   => 'ASC',
        'meta_key' => 'prog_date_start'
    );

    $programs = new WP_Query($args);
    ?>
    <?php while($programs->have_posts()): $programs->the_post(); ?>
        <h1><?php the_title(); ?></h1>
    <?php endwhile; ?>