search - Comparing between a negative and positive number

admin管理员组

文章数量:1024174

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

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

• search
• meta-query
• comparison

Share Improve this question asked Mar 26, 2018 at 9:15 James George DunnJames George Dunn 11144 bronze badges 3

• 1 try to add "type" => "NUMERIC" codex.wordpress/Class_Reference/… – mmm Commented Mar 26, 2018 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. – James George Dunn Commented Mar 26, 2018 at 9:38
• I really don't get it, why it needs "type" => "NUMERIC"???? Isn't it obvious that if you'd use between you'll want to use numeric values by default? Half a day is gone because of that. – Nadav Commented Jul 7, 2022 at 19:55

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 4

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.

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.

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

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

• search
• meta-query
• comparison

Share Improve this question asked Mar 26, 2018 at 9:15 James George DunnJames George Dunn 11144 bronze badges 3

• 1 try to add "type" => "NUMERIC" codex.wordpress/Class_Reference/… – mmm Commented Mar 26, 2018 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. – James George Dunn Commented Mar 26, 2018 at 9:38
• I really don't get it, why it needs "type" => "NUMERIC"???? Isn't it obvious that if you'd use between you'll want to use numeric values by default? Half a day is gone because of that. – Nadav Commented Jul 7, 2022 at 19:55

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 4

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.

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.

本文标签： searchComparing between a negative and positive number