Sorting by post meta in WP_Query is quite easy with the orderby argument set to meta_value. If your meta value is a number, you can use meta_value_num, however, if you run into a situation where you have a date string, like 2012-06-15, both meta_value and meta_value_num won’t give you the desired results.
Luckily, you can hook into the posts_orderby filter and do some custom SQL, like this:
add_filter( 'posts_orderby', 'my_posts_orderby_date', 10, 2 );
function my_posts_orderby_date( $orderby, $query ) {
global $wpdb;
return " CAST( $wpdb->postmeta.meta_value AS DATE ) " . $query->get( 'order' );
}
Then do your regular WP_Query with both orderby and meta_key arguments, and no matter to what orderby is set to, it’ll use the SQL generated in the filter.
However, keep in mind that CASTs aren’t the fastest and most efficient queries in MySQL, so think about using timestamp (integers) for meta values and using meta_value_num for sorting. You have PHP functions such as strtotime that can help you format a date and time string, into a numeric timestamp.
Cheers, and stay updated!
I have a working example where meta_value sorts by date. Dates are in exactly same form.
It was just yesterday where I had different issue with those fields: I tried to get posts from date range with meta_query, ‘BETWEEN’, and ‘DATE’ but it didn’t work. Eventually I created two queries with ‘>=’ and ‘<=', merging of arrays and finally third query with post__in. So painful.
Actually, you don’t with get_posts since it sets suppress_filters to true. ;)
And last thing, I would always mention to remove filter after the query since otherwise you can get another mysterious not working query.
Hi Milan, thanks for the suppress_filters heads up! It looks like your meta_value sort worked because MySQL will sort using CHAR by default,
Which in your (and my) case might work, but can easily fail over something that is very simple, like 6 instead of 06 for June :)
Thanks for your comment!