I have a client who wants to show a list of their posts ordered by the numerical value of a post meta field (custom fields are referred to as post meta data in the WordPress code, so I’ll be following that convention from now on). Unfortunately there doesn’t seem to be a way to do this using query_posts or by creating a new WP_Query object, which makes life a little trickier.
WordPress allows you a variety of ways to hook into the database queries to add order by directives, etc, so we’ll be using two filters to change the query in a couple of important ways. You can see a demo code snippet here. We’ll be changing the order by directive so the posts are shown in order of the value assigned to the relevant post meta field, and we’ll be ensuring that that ordering is done as though the value was a number (not, as it’s stored in WP’s post meta fields, a string).
Ordering in MySQL can be done alphabetically or numerically, and this is determined by looking at what type the field to be ordered by is: if the field is a string, the ordering is alphabetical, if it’s a number, ordering is numerical. Unfortunately WP stores all meta values as strings which means that MySQL will naturally order alphabetically, with values like ’33,234′ coming before values like ’434′ and ’9,645′, so we need to force it to order numerically. The top tip here is to use a MySQL mathematical function to force the field to be cast as a number. So we add an extra field into the query and we add 0 (zero) to it, e.g. wp_postmeta.meta_value+0 AS fry_views.
The order by stuff is really easy, we hook into the posts_orderby filter and replace the SQL with fry_views DESC
The resultant code from these changes and from the query in the demo code snippet looks like this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.*, wp_postmeta.meta_value+0 AS fry_views FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('21', '20', '22') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'fry-views' GROUP BY wp_posts.ID ORDER BY fry_views DESC LIMIT 0, 3
Have a look at the demo code snippet for a more complete view of what I’m suggesting.
Have you tried this with dates?
Hi Johan. Ordering by date should work fine, and you wouldn’t need to do the string => number conversion (i.e. the “+0″ bit). Good luck.
Simon
THANK YOU!! This was perfect. Just like Johan I needed to order several WP_Query loops by date. I just took your code and removed “+0″ from line 9 and it worked like a charm. I was even able to run multiple WP_Query loops inside the filter. The only thing was that if I did not have meta_key=my_variable in the WP_Query arguments then nothing was returned from the loop. That was fine for me though because all of my loops needed the same ordering. Thanks – you are a life saver!
Hi Simon, i have tried to use your demo code but it doesn’t show any posts. i simply swapped your fry_views like this:
function my_posts_orderby( $order_by ) {
// Order by fry views meta, desc
$order_by = “price DESC”;
return $order_by;
}
function my_posts_fields( $sql ) {
$sql .= ‘, wp_postmeta.meta_value+0 AS price ‘;
return $sql;
}
$args = array(
‘cat’ => 4,
‘showposts’ => 50,
‘meta_key’ => ‘price’
);
but im gettting nothing have i done something wrong?
maby you get the posts with the “get posts()” method?
the get posts has a ‘suppress_filters’ argument.
Thanks!
This worked perfectly and was very helpful. I ordered things numerically instead of alphabetically. Im using your post template plugin also
Thanks
Hey simon, thanks for the post, was very helpful!
Thanks for the code, very helpful. How would you select a category?
Oh wait, I got it, thanks again!