2

Using the Custom Post Type UI extension, I've created a 'Event' custom post-type, which fields are populated using custom fields (created with the extension Advanced Custom Fields) such as 'date-debut' and 'date-fin'.

On an archive page dedicated to this post-type, I'm trying to display the 10 next (current or future) events, ordered chronologically by beginning date.

The sorting part seems to be OK by itself; but it doesn't work anymore when I add the tests on dates, and anyway those don't work either.

Here is my code:

        $current_date = date('ymd');
        $my_query = new WP_Query( array ( 
        'post_type'         => 'mna_event',
        'posts_per_page'    => 10,
        'orderby'           => 'meta_value_num',
        'meta_key'          => 'date_debut',
        'order'             => 'ASC',
        'meta_query'        => array (
            'relation'      => 'OR',
            array (
                'key'       => 'date_debut',
                'value'     => $current_date,
                'compare'   => '>='
                ),
            array (
                'key'       => 'date_fin',
                'value'     => $current_date,
                'compare'   => '>='
                )
            )
        )
    );

Advice on the matter would be very much appreciated. Thanks

  • What's the problem ? – Shazzad Jan 23 '14 at 17:00
  • 1) Past events are still returned in the loop. 2) The events are no longer sorted by beginning date when I try to add the filter. – tosca Jan 23 '14 at 17:16
  • You are asking for >= your finish date. That doesn't look right. – s_ha_dum Jan 23 '14 at 17:52
  • 1
    Try type key for meta_query with DATE or DATETIME. Check the codex BTW what is the format of date in the meta value? – Chittaranjan Jan 23 '14 at 18:29
  • (1) you don't need to meta query both 'date_debut' and 'date_fin': query for posts with 'date_fin' >= $current_date you'll get all current and future events, without querying for 'date_debut' your query became simpler and so better performant. (2) Second issue is the way you are saving date, what format are you using? If you are storing date in mysql datetime format Y-m-d H:i:s set meta_query type to DATETIME (how @Chittaranjan suggests) if you are storing date in other formats, you have to change meta_query type accordingly e.g. for timestamps you have to set type to NUMERIC – gmazzap Jan 23 '14 at 18:37
3

This happens because of the OR relation on meta_query and the way WordPress generates the actual query string. You need to leave out the meta_key and orderby from the query args and hook into the posts_clauses filter to modify the where and orderby pieces of the query:

function wpse_130954_orderby_fix($pieces){
    global $wpdb;
    $pieces['where']  .= " AND $wpdb->postmeta.meta_key = 'date_debut'";
    $pieces['orderby']  = "$wpdb->postmeta.meta_value ASC";
    return $pieces;
}

Add the filter before setting up your WP_Query object and then make sure to remove it after running your query to not affect other queries:

add_filter( 'posts_clauses', 'wpse_130954_orderby_fix', 20, 1 );
$current_date = date_i18n('Y-m-d');
$my_query = new WP_Query( array ( 
    'post_type'         => 'mna_event',
    'posts_per_page'    => 10,
    'meta_query'        => array (
        'relation'      => 'OR',
        array(
            'key'       => 'date_debut',
            'value'     => $current_date,
            'compare'   => '>=',
            'type'      => 'DATE'
            ),
        array(
            'key'       => 'date_fin',
            'value'     => $current_date,
            'compare'   => '>=',
            'type'   => 'DATE'
            )
        )
    )
);

$result = $my_query->posts;

remove_filter( 'posts_clauses', 'wpse_130954_orderby_fix', 20 );

UPDATE: Use yy-mm-dd format for your date fields on ACF settings. (yy-mm-dd in JS/ACF == Y-m-d in PHP/MySQL datetime format) I have updated the code above for this format. (updated $current_date format and added 'type' => 'DATE' in meta_query)

  • I had to get rid of the public as it provoked an error. But after that, the result hasn't really improved: I still get events whose date_fin is prior today. – tosca Jan 23 '14 at 21:03
  • @tosca Did you remove meta_key, order and orderby from your query args? Also see my updated answer above for proper mysql date format. – Parham Jan 23 '14 at 21:30
  • I delete my own code and made a copy/paste of yours while replacing 'Y-m-d' by 'ymd' as I saw it was coded yyyymmdd in the database. I hadn't seen your update yet, I'll give it a try. – tosca Jan 23 '14 at 22:09
  • @tosca Aah, here is the issue: with your original date format (yymmdd in ACF) you need to use Ymd in php (with capital Y) NOT ymd. ACF uses jQuery UI Datepicker and yy in JQUI Datepicker means 4 digit year which in php is a single capital Y. Lowercase y in php is two digit year, so date('ymd') will be always a numeric value smaller than dates stored for your events and that's why you get all events. – Parham Jan 23 '14 at 22:24
  • Great! Thanks to you, it works now. I just have to decide on date format as the display is now yyyy-mm-dd. – tosca Jan 23 '14 at 22:31

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.