query_posts ->using meta_compare / where meta value is smaller or greater or equals

Question

I am using query_posts( $args ) to filter the Loop. I want to filter posts based on their meta_value "vote", sometimes smaller than, sometimes equals and so on....

I definitly want to use the query_posts() function and pass my filter through $args! I don't want to use add_filter('posts_where', 'filter_where'); and then add an AND statement to the query.

I want to use the given functionality of WordPress to filter posts with meta_key, meta_value and meta_compare like this:

$args = array( 'meta_key'=>'vote', 'meta_compare'=>'>=', 'meta_value'=>5, 'posts_per_page'=>100 ) )

query_posts( $args );

The result of this is:

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND wp_postmeta.meta_key = 'vote' AND wp_postmeta.meta_value >= '5' GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 100

The problem of that is:

wp_postmeta.meta_value >= '5' 

It should be:

wp_postmeta.meta_value >= 5

Then it would work fine.

I don't understand why WordPress adds quotes.

I'm using the predefined parameter from WordPress (<, >, <=, >=) and it's obvious that this will only work with numbers and not strings which would need to be in quotes.

The documentation says:

Returns posts with custom field key of 'miles' with a custom field value that is LESS THAN OR EQUAL TO 22

query_posts('meta_key=miles&meta_compare=<=&meta_value=22');

Answer 1

Since WP 3.1, you can cast the meta value to anything you want using the 'type' argument in 'meta_query':

$args = array(
  'meta_query'=> array(
    array(
      'key' => 'vote',
      'compare' => '>=',
      'value' => 5,
      'type' => 'numeric',
    )
  )
  'posts_per_page' => 100
) );

query_posts( $args );

Answer 2

From quick look over documentation meta_value seems to be purposed for strings and for numerical values there is meta_value_num.

See Orderby Parameters

Update

Did some digging.

meta_value_num is indeed ignored for the purpose of filtering. I think they simply forgot to add that part. :)

The issue is that WP_Query correctly receives number as int (passing as array doesn't matter), but it passes generated meta_compare condition through $wpdb->prepare() and explicitly marks value as %s string. In which case prepare forcefully single-quotes it.

So it seems you'll have to filter posts_where after all. You can try to just unquote that specific string instead of generating condition manually.

Answer 3

I would recommend parsing your $args array and converting it to a string before passing it into query_posts. When you create the $args array, the system will automatically convert the number 5 into a string "5" when the array is turned back into a string.

So use this instead:

query_posts('meta_key=vote&meta_compare=>=&meta_value=5&posts_per_page=100');

That's still passing the same information into query_posts, but should pass in the number 5 rather than a string "5".

---

Update

Since we've now discovered that meta_value stores strings rather than numbers, and you can't effectively do a greater than/less than comparison with strings. However, after some more research I stumbled upon the query flag meta_value_num.

If you run the following query_posts call:

query_posts('meta_key=vote&meta_compare=>=&meta_value=5&posts_per_page=100&orderby=meta_value_num');

Then you should get be behavior you want. meta_value_num tells WordPress to evaluate your meta_values as numbers rather than strings.