1

I'm having some difficulty comparing between a negative and positive number. This code works fine when it's between two positive numbers but not when it's between a negative then a positive one.

This is part of my 'meta_query':

array_push($metaQuery,
    array('relation' => 'AND',
        array(
            'key' => 'longitude',
            'value' => array($minlng, $maxlng),
            'compare' => 'BETWEEN',
        ),
    )
);

If for instance the $minlng is -1.5 and the $maxlng is 1.5. It will pass through values that equal -3.

Here is a var_dump of the meta_query if that is a help:

array(1) {
    [0]=>
    array(2) {
            ["relation"]=>
            string(3) "AND"
            [0]=>
            array(3) {
                    ["key"]=>
                    string(9) "longitude"
                    ["value"]=>
                    array(2) {
                            [0]=>
                            float(-0.989505008087)
                            [1]=>
                            float(1.31257480809)
                    }
                    ["compare"]=>
                    string(7) "BETWEEN"
            }
      }
}
2
  • try to add "type" => "NUMERIC" codex.wordpress.org/Class_Reference/…
    – mmm
    Mar 26 '18 at 9:23
  • Thank you but this doesn't seem to work either. I don't think it works with decimals. I've tried the decimal type also but it still seems to be selecting values that aren't between the range. Mar 26 '18 at 9:38
3

I tried the following code:

$posts = get_posts([
    "post_type" => "CUSTOM_POST_TYPE",
    "meta_query" => [
        'relation' => 'AND',
        [
            'key' => 'longitude',
            'value' => [-0.9895, 1.3125],
            'compare' => 'BETWEEN',
            "type" => "NUMERIC",
        ],
    ],
]);

When I remove "type" => "NUMERIC", I could reproduced your problem because the comparison is string based.

But when adding the type NUMERIC, the MySQL request contains CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '-0.9895' AND '1.3125' and the query returns the foreseen values.

1

If you only use "type" => "NUMBER", it will work but is ineffective, because it only takes the integer part to compare. You can replace it with this code "type" => "DECIMAL(5,2)"// 5:integer part length, 2:Decimal part length. I used it and it works with me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.