Get nearby posts based on the ACF Google Maps field WordPress

Anthony Moore comments:

Hi Kyle,

Thanks for this example. Now will this essentially replace any query I make on the site?

Let's say I just want to use this to display "nearby locations" as part of a sidebar widget when viewing a locations post, is there a way to have greater control of what queries these are used for?

Kyle comments:

Actually this may be even [[LINK href="http://www.bluefinengineering.com/blog/wordpress-geolocation-query-wp_query-lat-lng-post-metadata"]]here[[/LINK]] it adds a class to use instead of WP_Query called WP_Geo_Query so that you can use it as you need it. You'd need to update the examples there with your custom fields. You'll want to then replace the $distance['lat'] in the search arguments with the values from your current post (global $post). See how that works.

Anthony Moore comments:

This does look promising. I seem to be running into some issues.

This is what I currently have:


//Save the lat and lng values into different custom field
add_action('save_post', 'x_update_lng_and_lat');

function x_update_lng_and_lat( $post_id ) {

$location = get_field(x_location', $post_id );
$lat = $location['lat'];
$lng = $location['lng'];

update_post_meta( $post_id, 'x_location_lat', $lat );
update_post_meta( $post_id, 'x_location_lng', $lng );
}

class WP_Query_Geo extends WP_Query {
var $lat;
var $lng;
var $distance;

function __construct( $args=array() ) {
if( !empty( $args['lat'] ) ) {
$this->lat = $args['lat'];
$this->lng = $args['lng'];
$this->distance = $args['distance'];
add_filter('posts_fields', array($this, 'posts_fields'));
add_filter('posts_groupby', array($this, 'posts_groupby'));
add_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

parent::query($args);

remove_filter('posts_fields', array($this, 'posts_fields'));
remove_filter('posts_groupby', array($this, 'posts_groupby'));
remove_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

function posts_fields($fields) {
global $wpdb;
$fields = $wpdb->prepare(" ((ACOS(SIN(%f * PI() / 180) * SIN(mtlat.meta_value * PI() / 180) + COS(%f * PI() / 180) * COS(mtlat.meta_value * PI() / 180) * COS((%f - mtlng.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance", $this->lat, $this->lat, $this->lng);
return $fields;
}

function posts_groupby($where) {
global $wpdb;
$where .= $wpdb->prepare(" HAVING distance < %d ", $this->distance);
return $where;
}

function posts_join_paged($join) {
$join .= " INNER JOIN uc_postmeta AS mtlat ON (IF(mtmaster.meta_value != uc_posts.ID, mtmaster.meta_value, uc_posts.ID) = mtlat.post_id AND mtlat.meta_key = 'lat') ";
$join .= " INNER JOIN uc_postmeta AS mtlng ON (IF(mtmaster.meta_value != uc_posts.ID, mtmaster.meta_value, uc_posts.ID) = mtlng.post_id AND mtlng.meta_key = 'lng') ";
return $join;
}
}


And then my query looks something like this:


$lat = get_post_meta( get_the_ID(), 'x_location_lat', true );
$lng = get_post_meta( get_the_ID(), 'x_location_lng', true );

$args = array(
'post_status' => 'publish',
'post_type' => 'locations',
'posts_per_page' => 2,
'fields' => 'all',
'lat' => $lat,
'lng' => $lng,
'distance' => 5
);

$foo = new WP_Query_Geo( $args );

if ( $foo->have_posts() ) :
while ( $foo->have_posts() ) : $foo->the_post();
get_the_title();
endwhile;
endif;


However I receive the following error:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HAVING distance < 5 ORDER BY wp_posts.post_date DESC LIMIT 0, 2' at line 1]

Could there be an issue with my MySQL version? Or is there an obvious issue with my code. Not too sure if I need to change anything in my posts_join_paged($join) function.

Kyle comments:

Try removing the groupby

Anthony Moore comments:

Possibly getting close, my code now looks like:


class WP_Query_Geo extends WP_Query {
var $lat;
var $lng;
var $distance;

function __construct( $args=array() ) {
if( !empty( $args['lat'] ) ) {
$this->lat = $args['lat'];
$this->lng = $args['lng'];
$this->distance = $args['distance'];

add_filter('posts_fields', array($this, 'posts_fields'));
add_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

parent::query($args);

remove_filter('posts_fields', array($this, 'posts_fields'));
remove_filter('posts_join_paged', array($this, 'posts_join_paged'));
}

function posts_fields($fields) {
global $wpdb;
$fields = $wpdb->prepare(" ((ACOS(SIN(%f * PI() / 180) * SIN(mtlat.meta_value * PI() / 180) + COS(%f * PI() / 180) * COS(mtlat.meta_value * PI() / 180) * COS((%f - mtlng.meta_value) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance", $this->lat, $this->lat, $this->lng);
return $fields;
}

function posts_join_paged($join) {
$join .= " INNER JOIN wp_postmeta AS mtlat ON (IF(mtmaster.meta_value != wp_posts.ID, mtmaster.meta_value, wp_posts.ID) = mtlat.post_id AND mtlat.meta_key = 'lat') ";
$join .= " INNER JOIN wp_postmeta AS mtlng ON (IF(mtmaster.meta_value != wp_posts.ID, mtmaster.meta_value, wp_posts.ID) = mtlng.post_id AND mtlng.meta_key = 'lng') ";
return $join;
}
}


But I receive the following error:

WordPress database error: [Unknown column 'mtmaster.meta_value' in 'on clause']