Comparing between a negative and positive number

Question

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"
            }
      }
}

Answer 1

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.

Answer 2

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.