By gigophant on

I am very new to Drupal modules / hooks. Banging my head trying to figure out how to get a view modified so that it will add a field and sort by it. The query that I'm trying to accomplish in plain SQL:

SELECT *, 3956 * 2 * ASIN(SQRT(POWER(SIN(($postcode_lat - abs(lat))*pi()/180/2),2) + COS($postcode_lat * pi()/180) * COS(abs(lat) * pi()/180) * POWER(SIN(($postcode_lng - lng) * pi()/180/2),2))) AS distance WHERE distance < 500 ORDER BY distance ASC

And this is the module code that I'm trying to use to implement it:

function membership_views_query_alter(&$view, &$query) { if ($view->name == 'membership_directory_2013') {

$formula = '3956 * 2 * ASIN(SQRT(POWER(SIN((32.7828 - abs(29.7631))*pi()/180/2),2) + COS(32.7828 * pi()/180) * COS(abs(29.7631) * pi()/180) * POWER(SIN((96.8039 - 95.2665) * pi()/180/2),2)))';
$query->addExpression($formula, 'distance');
$query->condition('distance', 500, '<')->orderBy('distance');

} }

And this is the error that I receive when I try to run the view:

An AJAX HTTP error occurred. HTTP Result Code: 200 Debugging information follows. Path: /admin/structure/views/view/membership_directory_2013/preview/default/ajax StatusText: OK ResponseText: Fatal error: Call to undefined method views_plugin_query_default::addExpression() in /sites/all/modules/membership/membership.module on line 48

So how can I get this to work?

Thank you!

p.s. Unfortunately I cannot use a geolocation / proximity module in this situation because CiviCRM doesn't play nice with any of the existing solutions.

⋅ Categories: Drupal 7.x

Comments

rooby’s picture

rooby commented

The problem is that the $query parameter is a view query plugin object, which is different to a drupal core select query object.

The addExpression() function is from the drupal core select query object and so isn't available on the views query object.

The functions you want are in the views_plugin_query_default class. For example, add_where_expression() and add_where().

So you want something more like this (untested):

<?php
/**
 * Implements hook_views_query_alter().
 */
function membership_views_query_alter(&$view, &$query) {
  if ($view->name == 'membership_directory_2013') {
    $formula = '3956 * 2 * ASIN(SQRT(POWER(SIN((32.7828 - abs(29.7631))*pi()/180/2),2) + COS(32.7828 * pi()/180) * COS(abs(29.7631) * pi()/180) * POWER(SIN((96.8039 - 95.2665) * pi()/180/2),2)))';
    $query->add_field(NULL, $formula, 'distance');
    $query->add_where(0, 'distance', 500, '<');
    $query->add_orderby(NULL, NULL, 'ASC', 'distance'); 
  }
}
?>