You are here
Using Formulas with Views & Hook Views Query Alter
Requirement: Get a list of store locations within a mileage range.
Setup: A Location content type with fields for the address, zipcode, city and state. Client wanted them separately so using the Location or Geolocation would not have done much for us. Though I only spent less than 30 minutes with each of these to see what they had to offer for what was needed.
Challenge: Perform a mathematical formula in Views using the hook_views_query_alter.
The Geolocation does allows you to enter a whole address on a single field and auto-generates a latitude and longitude coordinates. What I decided to do, was to hook on the node save event and get this information myself using Google Maps REST API.
I've created 2 fields to hold the latitude and longitude information field_latitude and field_longitude.
/**
* Implements hook_node_presave().
*/
function MODULE_NAME_node_presave($node) {
// Only affect this content type.
if($node->type != 'NODE_TYPE_NAME') {
return;
}
// Get all data required to perform the request from Google Map API.
$gmap_location = array();
// Address
if($field_distributor_address = $node->field_distributor_address['und']) {
$gmap_location['address'] = $field_distributor_address[0]['value'];
}
// City
if($city = $node->field_city['und']) {
$gmap_location['city'] = $city[0]['value'];
}
// State
if($state = $node->field_state['und']) {
$gmap_location['state'] = $state[0]['value'];
}
// Zipcode
if($zip = $node->field_zip['und']) {
$gmap_location['zip'] = $zip[0]['value'];
}
if($gmap_location['address'] && $gmap_location['city'] && ($gmap_location['zip'] || $gmap_location['state'])) {
$gmap_location_str = urlencode(implode(',', $gmap_location));
$url = "http://maps.googleapis.com/maps/api/geocode/json?address={$gmap_location_str}&sensor=true";
$http_req = drupal_http_request($url);
if($http_req->code == 200) {
$data = json_decode($http_req->data);
if($data->results) {
// Store the latitude and longitude.
$node->field_latitude['und'][0]['value'] = $data->results[0]->geometry->location->lat;
$node->field_longitude['und'][0]['value'] = $data->results[0]->geometry->location->lng;
}
}
}
}
Next, is the code used to alter the views query fetching the content. I could have fetch all nodes and then perform the range formula, but we are dealing with a lot of content, and have limited time so I decided to do this directly on the query. The decision was purely an educated guess given the data. I did NOT perform any benchmarking to see MySQL query vs PHP. See the references list below to check formula.
/**
* Implements hook_views_query_alter().
*/
function MODULE_NAME_views_query_alter(&$view, &$query) {
// Only alter the view this module is designed for.
if($view->name != 'VIEW_NAME') {
return;
}
// Grab the arguments passed to the views. These are currently input by the user thru exposed fields in views, but
// you can do this from wherever so I am not gonna get into that.
// In the view, these are set to expect the values in the Contextual Filters under the Advanced options.
$lat1 = $view->args[0];
$lng1 = $view->args[1];
$lat2 = 'field_data_field_latitude.field_latitude_value';
$lng2 = 'field_data_field_longitude.field_longitude_value';
// This is the formula that will find us all the location within a range. (Uses rounded miles)
// To use Kilometers change the 3959 to 6371. Again these are rounded numbers.
$formula = "3959 * ACOS( SIN( RADIANS( $lat1 ) ) * SIN( RADIANS( $lat2 ) ) + COS( RADIANS( $lat1 ) ) * COS( RADIANS( $lat2 )) * COS( RADIANS( $lng2 ) - RADIANS( $lng1 )) )";
// In order for the fields $lat2 and $lng2 to be used at this point in operation,
// these had to be explicitly included.
$query->add_table('field_data_field_latitude');
$query->add_table('field_data_field_longitude');
// Sets the formula's alias.
$query->add_field(NULL, $formula, 'distance');
// Only fetch those store locations within a 5 mile area.
// Eventually this will be part of the arguments passed thru the views.
$query->add_where(0, "$formula < 5", array(), 'formula');
}
References
- Formulas - http://www.movable-type.co.uk/scripts/latlong.html
- add_field() - https://api.drupal.org/api/views/plugins!views_plugin_query_default.inc/...
- add_where() - https://api.drupal.org/api/views/plugins%21views_plugin_query_default.in...
- add_table() - https://api.drupal.org/api/views/plugins%21views_plugin_query_default.in...
lifestyle:
medium:
- Diego F. Roldán's blog
- Log in to post comments
- 11066 veces leido