WordPress Screwing Up 'orderby' => 'meta_value_num' in 'pre_get_posts'

Question

I have a bunch of _ai_price_int meta values set up as just numbers. This is how the table looks:

meta_id, post_id, meta_key,      meta_value
1309,    1111,    _ai_price_int, 6550
1310,    1115,    _ai_price_int, 7100
1311,    1113,    _ai_price_int, 7500
1312,    1103,    _ai_price_int, 6000
etc.

I'm using a pre_get_posts filter to modify a particular query, like this:

public function filterQuery($query) {
    $query->set('post_type', 'sponsorship');
    $query->set('meta_key', '_ai_price_int');
    $query->set('meta_value', '4000');
    $query->set('meta_compare', '<=');
    $query->set('orderby', 'meta_value_num');
    $query->set('order', 'DESC');
    return $query;
}
add_action('pre_get_posts', Array($this, 'filterQuery'));

But playing around with $query->set('meta_value', '4000');, it turns out that the results are incorrect.

• When using 2000, I'm getting a post that has an _ai_price_int value of 15000.
• When using 4000, I'm getting a post that has an _ai_price_int value of 35000.

I tried using:

    $query->set('post_type', 'sponsorship');
    $query->set('meta_key', '_ai_price_int');
    $query->set('orderby', 'meta_value_num');
    $query->set('order', 'DESC');
    $query->set('meta_query', array(
        array(
           'key' => '_ai_price_int',
           'value' => array(0, 4000),
           'compare' => 'BETWEEN'
        )
    ));

instead, but the results are the same.

Looking at the performed query, I can see that it has this portion:

...AND ((wp_13postmeta.meta_key = '_ai_price_int'AND CAST(wp_13postmeta.meta_value AS CHAR) <= '4000') )...

Don't tell me that WordPress is ordering these values alphabetically?!

I'm also saving this custom value as an integer explicitly:

$price = (int)preg_replace("/([^0-9\\.])/i", "", $match[1]);
update_post_meta( $post_id, '_ai_price_int', $price );

What am I doing wrong?

I'm testing on WP version: 3.5.2

Answer 1

Use a meta_query and set type to NUMERIC-

$query->set('meta_query', array(
    array(
       'key' => '_ai_price_int',
       'value' => array(0, 4000),
       'compare' => 'BETWEEN',
       'type' => 'NUMERIC'
    )
));
$query->set('meta_key', '_ai_price_int');
$query->set('orderby', 'meta_value_num');

Answer 2

I think you may need to change $query->set('meta_value', '4000'); to $query->set('meta_value_num', '4000'); in your pre_get_posts filter.

The WP_Query docs note that:

"By using the 'meta_value' parameter the value 99 will be considered greater than 100 as the data are stored as 'strings', not 'numbers'. For number comparison use 'meta_value_num'."

from: http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters