Query meta field using between

2 Views

I’m trying to Query some lattitude and logitude and then I get some long numbers and apperently the Query dont like that.

Let says I have these in the database

Read More
Meta-key: lat 
Meta-value: 54.3415000 
Meta-key: lng
Meta-value: 10.1254100

and do a query like this: 

$args['meta_query'] = 
        array(
            'relation' => 'AND', 
            array(
                'key'       => 'lat',
                'value'     =>  array( '53.82659686199116', '54.946076335668714' ),
                'type'      => 'NUMERIC',
                'compare'   => 'BETWEEN',
            ),
            array(
                'key'       => 'lng',
                'value'     =>  array( '8.429260683593725', '11.889954042968725' ),
                'type'      => 'NUMERIC',
                'compare'   => 'BETWEEN',
            )
        );

Then it works, BUT, when it looks like this: 

$args['meta_query'] = 
        array(
            'relation' => 'AND', 
            array(
                'key'       => 'lat',
                'value'     =>  array( '54.10933293482647', '54.66906633195788' ),
                'type'      => 'NUMERIC',
                'compare'   => 'BETWEEN',
            ),
            array(
                'key'       => 'lng',
                'value'     =>  array( '9.294434023437475', '11.024780703124975' ),
                'type'      => 'NUMERIC',
                'compare'   => 'BETWEEN',
            )
        );

Then it does not work, even it is still between the 2 numbers.

Anyone have a clue what is going on here ? Must be some MYSQL stuff.

EDIT:

Here is what a mysql request looks like: 

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON
(wp_posts.ID = wp_postmeta.post_id)INNER JOIN wp_postmeta AS mt1 ON 
(wp_posts.ID = mt1.post_id) WHERE 1=1 AND wp_posts.post_type = 'branchenbuch' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' 
OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private') AND ( (wp_postmeta.meta_key = 'lat' 
AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '54.1833340338' 
AND '54.4636527622')AND (mt1.meta_key = 'lng' AND CAST(mt1.meta_value AS SIGNED) 
BETWEEN '9.76272625977' AND '10.6278995996') ) GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10
Post Views: 2

Related posts

Leave a Reply

2 comments

  1. Your problem is this:

    CAST(wp_postmeta.meta_value AS SIGNED)

    Run a simple SQL query (command line or PhpMyAdmin, whatever you like) like SELECT CAST('1.2' as SIGNED) and you will see that the value returned is 1. What causes that cast is 'type' => 'NUMERIC',.

    You can pass DECIMAL to meta_query instead of NUMERIC but I don’t see a way to pass parameters to DECIMAL that would actually make it useful. SELECT CAST('1.2' as DECIMAL) is also 1 unless you also pass arguments to DECIMAL like this SELECT CAST('1.2' as DECIMAL(20,10)). The first number is ‘precision’– the number of decimal places used in the math– and the second is the number of decimal places returned. The second appears to me that the second value needs to be at least 2 less than the first or you get odd rounding issues. Obviously the second needs to be at or greater than the maximum decimal places in your number.

    Your best approach would be to use 'type' => 'DECIMAL', and apply a filter:

    function add_decimal_params($sqlarr) {
  remove_filter('get_meta_sql','add_decimal_params');
  $sqlarr['where'] = str_replace('DECIMAL','DECIMAL(20,16)',$sqlarr['where']);
  return $sqlarr;
}
add_filter('get_meta_sql','add_decimal_params');

    Add the filter immediately before your query and it will remove itself.

    You may be able to get this to work by left-padding your numbers to the same length and not using a type argument at all, but I didn’t test that.

  2. I used something similar a couple of years ago, I finally used raw sql (can’t remember why) but the main difference I see from your raw sql and mine is this row:

    AND CAST(wp_postmeta.meta_value AS SIGNED) BETWEEN '54.1833340338'

    in my case it is casted to DECIMEL and values are numbers (not strings):

    AND CAST(wp_postmeta.meta_value AS DECIMAL(20,10)) BETWEEN 54.1833340338

    and the actual lat/lng are without single quotes -> so they’re are actual numbers.

    Try running this:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON
(wp_posts.ID = wp_postmeta.post_id)INNER JOIN wp_postmeta AS mt1 ON 
(wp_posts.ID = mt1.post_id) WHERE 1=1 AND wp_posts.post_type = 'branchenbuch' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'future' 
OR wp_posts.post_status = 'draft' OR wp_posts.post_status = 'pending' 
OR wp_posts.post_status = 'private')

AND ( (wp_postmeta.meta_key = 'lat' 
AND CAST(wp_postmeta.meta_value AS DECIMAL(20,10))
BETWEEN 54.1833340338 AND 54.4636527622)

AND (mt1.meta_key = 'lng'
AND CAST(mt1.meta_value AS DECIMAL(20,10)) 
BETWEEN 9.76272625977 AND 10.6278995996))

GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10