meta_query: using BETWEEN with floats and/or casting to DECIMAL

0 Views

Each post has a lat/lng value attached to it via postmeta. I’m trying to grab all posts within a bounding lat/lng value. Here’s the get_posts query: 

$posts = get_posts(array(
    'posts_per_page' => 100,
    'post_type' => 'place',
    'post_status' => 'publish',
    'meta_query' => array(
        array(
            'key' => 'places_lat',
            'value' => array($lat_min, $lat_max),
            'compare' => 'BETWEEN',
            //'type' => 'DECIMAL',
        ),
        array(
            'key' => 'places_lng',
            'value' => array($lng_min, $lng_max),
            'compare' => 'BETWEEN',
            //'type' => 'DECIMAL',
        ),
    ),
));

Since postmeta values are stored as strings, I figured I should be casting to DECIMAL, but it just seems to trim the decimal value from the string due to the lack of DECIMAL arguments/precision parameters.

Read More

I did notice the query treats the floats within the value array as strings, which could also be another point of failure. Running the compiled query without the quotes around each floating value works as expected.

I’ll be using get_permalink() on each post. I can run a custom query outside of get_posts (via $wpdb->get_results()) to properly grab the posts within the bounding box, then loop through the posts and get_permalink, but it ends up firing an additional database query per post to build the permalink – not an ideal solution!

Any ideas?

Post Views: 0

Related posts

Leave a Reply

2 comments

  1. You can filter generated SQL and add precision parameters that you need.

    Enable filters for get_posts() by adding following to query:

    'suppress_filters' => false,

    And:

    add_filter('posts_where','cast_decimal_precision');

function cast_decimal_precision( $where ) {

    return str_replace('DECIMAL','DECIMAL(10,3)',$where);
}

    Update

    With Jan’s suggestion:

    add_filter('get_meta_sql','cast_decimal_precision');

function cast_decimal_precision( $array ) {

    $array['where'] = str_replace('DECIMAL','DECIMAL(10,3)',$array['where']);

    return $array;
}

  2. As of 3.8 (see track) the precision can be added to the cast type like so:

    $posts = get_posts(array(
    'posts_per_page' => 100,
    'post_type' => 'place',
    'post_status' => 'publish',
    'meta_query' => array(
        array(
            'key' => 'places_lat',
            'value' => array($lat_min, $lat_max),
            'compare' => 'BETWEEN',
            'type' => 'DECIMAL(10,3)',
        ),
        array(
            'key' => 'places_lng',
            'value' => array($lng_min, $lng_max),
            'compare' => 'BETWEEN',
            'type' => 'DECIMAL(10,3)',
        ),
    ),
));