Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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
share|improve this question
add comment

1 Answer 1

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 );
share|improve this answer
    
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: –  Russell Slater 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' –  Russell Slater 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. –  Russell Slater May 8 '12 at 23:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.