How do I query posts and use the 'orderby' attribute to order posts in loop according to date 'meta_value'?

Question

I am creating a website for a theatre company, and I am creating an index of all past, current, and future productions. I would like the index to 'orderby' the ending date of each production (ACF 'date' field type; 'ending_date').

Here is an example of my query:

 <?php 
  $futureProd = array(
   'post_type' => 'productions',
   'posts_per_page' => -1,
   'meta_key' => 'ending_date',
   'orderby' => 'meta_value',
   'order' => 'ASC',
 );

 $slider_posts = new WP_Query($futureProd);

 $array_rev = array_reverse($slider_posts->posts);
 $slider_posts->posts = $array_rev;

 ?>

Have also tried the following, adding the 'meta_value_date' as well as 'meta_value_num' alternatives:

<?php // query posts
  $futureProd = array(
    'post_type' => 'productions',
    'posts_per_page' => -1,
    'meta_key' => 'ending_date',
    'orderby' => 'meta_value_date',            
    'order' => 'ASC',
  );
?>

AND

<?php // query posts
      $futureProd = array(
    'post_type' => 'productions',
    'posts_per_page' => -1,
    'meta_key' => 'ending_date',
    'orderby' => 'meta_value_num',            
    'order' => 'ASC',
  );
?>

No matter what I try, the posts refuse to order themselevs by the meta_value, and instead opt to order themselves by the default, post date.

I'm sure I'm missing something simple.

Anyone have any ideas?

Margartmargate

Answer 1

You can spicify the datatype in orderby key like meta_value_* possible values are 'NUMERIC', 'BINARY', 'CHAR', 'DATE', 'DATETIME', 'DECIMAL', 'SIGNED', 'TIME', 'UNSIGNED'.

Try this,

$futureProd = array(
    'post_type' => 'productions',
    'posts_per_page' => -1,
    'meta_key' => 'ending_date',
    'orderby' => 'meta_value_date',
    'order' => 'ASC',
);

$slider_posts = new WP_Query($futureProd);

Please Note: for above code to work you need to have date in YYYY-MM-DD format if you have date in different format then you have to create your custom filter function hook in to posts_orderby filter with STR_TO_DATE MySQL function.

---

For date format YYYYMMDD

Add this in your active theme function.php file of your active child theme (or theme). Or also in any plugin php files.

function text_domain_posts_orderby($orderby, $query) {
    //Only for custom orderby key
    if ($query->get('orderby') != 'yyyymmdd_date_format')
        return $orderby;
    if (!( $order = $query->get('order') ))
        $order = 'ASC';
    global $wpdb;
    $fieldName = $wpdb->postmeta . '.meta_value';
    return "STR_TO_DATE(" . $fieldName . ", '%Y%m%d') " . $order;
}

add_filter('posts_orderby', 'text_domain_posts_orderby', 10, 2);

So now your WP_Query argument should look like this:

$futureProd = array(
    'post_type' => 'productions',
    'posts_per_page' => -1,
    'meta_key' => 'ending_date',
    'orderby' => 'yyyymmdd_date_format', //added custom orderby key
    'order' => 'ASC',
);

The code is tested and fully functional.

Reference:

• WP_Query Order & Orderby Parameters
• MySQL ORDER BY Date field which is not in date format

Hope this helps.

Chamomile

Gave it a try. Problem persists. :( Any other ideas? – Margartmargate

What is the date format you are using? Is it YYYY-MM-DD ? – Chamomile

I've got it returning YYYYMMDD so that it comes out as an 8 digit number. i.e January 1, 2017 returns: 20170101. – Margartmargate

@JamieCavanagh: I have update my answer. please read the note section and you have to use code under For date format YYYYMMDD section. – Chamomile

Hey Raunak. So sorry but this didn't work either. :( – Margartmargate

@JamieCavanagh: I have tested my code it is working fine; there must be an issue with your date format; are you 100% sure you date format is YYYYMMDD ? – Chamomile

Thank you @RaunakGupta You are amazing. There are thousands of articles with meta fields with wrong date format. Everyone who sees this comment must know that this is the only complete answer I could find on the Internet so please give an up vote :) – Werra