0

I have two custom meta values that I am using for an events list and have almost got everything working perfectly except for the ordering. I have two dates (event_date and event_enddate). One of these is a start date which is always present, and the other is an end date which is only used when needed. I have created a query which lists all dates where either of these fields are equal or greater to today's date, which works fine. However, I want all of the events to be ordered solely by the event_date field. How can I do this? I've spent all day trying to do this and just can't work it out!

Here's my code:

SELECT DISTINCT wposts.*
FROM $wpdb->posts wposts
LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
WHERE ((wpostmeta.meta_key = 'event_enddate' AND wpostmeta.meta_value >= CURDATE())
 OR (wpostmeta.meta_key = 'event_date' AND wpostmeta.meta_value >= CURDATE()))
AND wposts.post_status = 'publish'
AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->term_taxonomy.term_id IN(15)
AND wposts.post_status = 'publish'
ORDER BY wpostmeta.meta_value ASC
2

I believe you can achieve this with WP_Query and avoid the custom SQL:

$today = date( 'Y-m-d' );
$args = array(
    'tax_query' => array(
        array(
            'taxonomy' => 'category',
            'field' => 'ID',
            'terms' => 15
        )
    ),
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key' => 'event_enddate',
            'value' => $today,
            'compare' => '>='
        )
    ),
    'meta_key' => 'event_date',
    'meta_value' => $today,
    'meta_compare' => '>=',
    'orderby' => 'meta_value_num'
);
$events = new WP_Query( $args );
4
  • Hey Milo, I tried your code and it didn't work at first (the posts that were being shown were featuring all kinds of dates), so I've been tinkering with it a bit and have now got it so that it's showing exactly the posts I want, but as soon as I add any info on the ordering it goes wrong! Here's the code which brings my unordered data perfectly: May 8 '12 at 23:25
  • $today = date( 'Y/m/d' ); $args = array( 'tax_query' => array( array( 'taxonomy' => 'category', 'field' => 'ID', 'terms' => 15 ) ), 'meta_query' => array( 'relation' => 'OR', array( 'key' => 'event_enddate', 'value' => $today, 'compare' => '>=' ), array( 'key' => 'event_date', 'value' => $today, 'compare' => '>=' ) ) ); I've tried adding this to the bottom to get the ordering but then I just get the wrong values. Why is this? What else can I try? 'meta_key' => 'event_date', 'orderby' => 'meta_value_num' May 8 '12 at 23:27
  • @RussellSlater After you query for events, add print_r($events); to your template and you'll see the SQL query that WordPress generated. That may give you some clue as to what's going wrong. I thought what I had was working, I tried it out with a bit of test data- maybe a date format issue?
    – Milo
    May 8 '12 at 23:34
  • Okay, cheers Milo. That has shed some light. Specifically, there's definitely an error in the sorting: Array ( [key] => event_enddate [value] => 2012/05/08 [compare] => >= ) ) [meta_value] => 2012/05/08 [meta_compare] => >= [orderby] => meta_value [order] => ASC [error] I'll see if I can work out what's causing it, but it may well be the date format I guess. May 8 '12 at 23:54

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.