I have two custom fields:
meta_key1is boolean (
meta_key2is a date value -
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.
key2.meta_value >= today ASC
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
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.