php - 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?

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.