Ordering post meta value, numerically

Published on July 31, 2009 in Work. 19 Comments Tags: , , , , , , , .

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.

Update: There’s a series of parameters that you can feed WP_Query to make this work without the filtering, these output using the MySQL function CAST similar to this, in case you want to continue with the raw SQL approach rather than switching to WP_Query.

CAST( whatever AS CHAR )

19 Responses to “Ordering post meta value, numerically”

Feed for this Entry Trackback Address

Leave a Reply