Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

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 
share|improve this question
2  
Try it without the quotes. Those are currently strings. And make sure you save the meta data as actual numeric values and not strings. Else there's nothing that could be between. –  kaiser Feb 1 '13 at 16:15
    
The meta_value have type LONGTEXT in mysql, so it is not a number, but I cant change that. –  Kristian Primdal Feb 2 '13 at 13:40

2 Answers 2

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.

share|improve this answer

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 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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