You are here

Using Formulas with Views & Hook Views Query Alter

published by Diego F. Roldán on Fri, 11/22/2013 - 13:08

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

lifestyle:

medium:

tags: