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');

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');

• query-posts

4 Answers 4

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 );

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.

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.

WordPress treats all 'value' as strings and adds single quotes to them in the final query, so sql is also forced to treat them as strings and not numbers. You can remove these quotes with a filter:

add_filter('get_meta_sql', function($data) {
  $regex = "/'(-?\d+)'/";
  $data['where'] = preg_replace($regex, "$1", $data['where']);
  return $data;
});

And make sure you have 'type' => 'NUMERIC' or similar