Sort by meta key within same day

Question

I try to build something that can sort posts by day - and within the same day sort them with any meta value (integer) - like popularity or vote or what have you.

So that, in the end, i get today's posts ordered by meta, then yesterday posts by meta, etc, etc...

The WordPress query object allows nice multiple table sorting since v4, including meta data.

<?php $the_query = new WP_Query( 
    array( 
        'meta_key' => 'my_meta_field',
        'orderby' => array( 
            'date' => 'DESC', 
            'my_meta_field' => 'DESC' 
        ) 
    ) 
);
?>

But the orderby date seems more like time based, where i would need something day based. My posts are then ordered just by time, ignoring the meta field.

Any help ?

Answer 1

So here is a quick solution - with a bit of searching over SO and query analyzing : Keep the main query, but add a special filter for ordering.

The filter (in function.php) :

function my_posts_orderby( $orderby ) {
  $orderby = 'DATE(wp_posts.post_date) DESC, wp_postmeta.meta_value DESC';
  return $orderby;
}

Note the DATE() mysql function that takes the date out of the full time field.

And then, before the loop, run the query thus :

<?php add_filter( 'posts_orderby', 'my_posts_orderby' ); ?>

<?php $the_query = new WP_Query( array( 'meta_key' => '_my_meta_field' ) ); ?>

Wich means, call the filter, and then create a query that includes the custom meta field so that it can be sorted.

Then, after the loop, don't forget to call

<?php remove_filter( 'posts_orderby', 'posts_orderby' ); ?>

failling that no other query will ever work (including displaying a single post)

And that works.