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
Your problem is this:
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 is1
. What causes that cast is'type' => 'NUMERIC',
.You can pass
DECIMAL
tometa_query
instead ofNUMERIC
but I don’t see a way to pass parameters toDECIMAL
that would actually make it useful.SELECT CAST('1.2' as DECIMAL)
is also1
unless you also pass arguments toDECIMAL
like thisSELECT 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: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.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:
in my case it is casted to DECIMEL and values are numbers (not strings):
and the actual lat/lng are without single quotes -> so they’re are actual numbers.
Try running this: