2

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.

  • As of WP 4.2, you can sort by multiple meta keys and values. Whipping up an answer that shows this – Manny Fleurmond Jun 24 '15 at 0:40
2

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 );
  • Excellent, that works :) Are you using the CONCAT and letters(A,B,C,D) as a way to sort alphabetically? – eleclair Jun 29 '15 at 14:43
  • Good stuff - yes, though could use anything really, including numbers, but thought it slightly safer not to given the numeric data. – bonger Jun 29 '15 at 14:52
  • I'm trying to implement this by using WP_Query and the posts_orderby filter, but I'm not quite getting what I want. Any suggestions? Here's my code pastebin.com/VJ6s0WJa – eleclair Jul 7 '15 at 18:18
  • I wouldn't do an OR I think, you want both meta values, also then the second postmeta (ie prog_start_date) is aliased as mt1 by WP_Query... I'll update the answer with my attempt... – bonger Jul 7 '15 at 19:04
  • Thanks @bonger! This is very close, but I do need the OR though. Without it I get programs that are expired and not ongoing. It looks like simply adding the OR relation to the query jacks things up. – eleclair Jul 7 '15 at 20:31
3

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

  • 1
    Manny, I have edited your post to include the relevant link to the make article discussing this new syntax. To be honest, I did not know about it ;-). +1 for posting this as a solution – Pieter Goosen Jun 24 '15 at 5:13
  • Thanks @Manny, I did not now about the new syntax. However, I'm still not getting the results I'd like. I've uploaded my results at pastebin.com/x8saFKsp if you want to take a look. – eleclair Jun 24 '15 at 13:27
0

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; ?>
  • Did you insert test data and run this code? I am not sure that it address the problem the OP is having, though WP_Query plus some filters would be the better way to go. – s_ha_dum Jun 23 '15 at 15:55
  • 1
    How does this solve the issue of sorting/ordering? I would use WP_Query, but you can't order by multiple custom fields without filtering. If I do filtering, I'm not sure how to account for the blank date fields - thus my question. – eleclair Jun 23 '15 at 16:38
  • Yep, sorry, i understand your question now. You can pass an array into orderby in WP_Query to specify a fallback order if the default one wont work. I updated the code. Not sure if this would work for you, i tried it with a couple of posts but obviously i can't replicate the exact stuff you're trying to do. – passatgt Jun 23 '15 at 19:01
  • Thanks @passatgt for looking into this. However, sorting by "date" in the order by array which is not a custom meta field. I want to sort by 'prog_ongoing' (those that are FALSE or not ongoing first) and then 'prog_date_start' within. – eleclair Jun 24 '15 at 14:19

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.