How to use hook_views_query_alter to add field with no table in Drupal 7 Views 3

Question

I am new to Drupal 7/Views 3 hooks. I am trying to approximate this query:

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

This gives a list of locations sorted by distance. However, in trying to implement with hooks, I am struggling. Here is the code that I tried:

$query->addField('', '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)))', 'distance');

Drupal doesn't like the empty parameter for table. But how can I add a field with no table?

I appreciate any suggestions offered.

Answer 1

I had the same problem. And I've solved it.

    function ModuleName_views_query_alter(&$view, &$query) {
  if ($view->name == 'my_view' && 'my_block_name' == $view->current_display)) {


    $formula = "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)))";

    // Sets the formula's alias.
    $query->add_field(NULL, $formula, 'distance');
    $query->add_where(0, "$formula < 500", array(), 'formula');

    // Sets the order by reference
    $query->orderby = array(
      array(
        'field' => 'distance',
        'direction' => 'ASC',
      )
    );
  }
}

See http://roldanarts.com/2013/11/22/diego-f-roldan/blog/using-formulas-views-hook-views-query-alter for more details.

Answer 2

You can use the SelectQuery::addExpression() method (instead of addField()) to add complex expressions to the query. e.g.

$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');

Answer 3

You can't add fields using hook_views_query_alter().

You need to use hook_views_data() or hook_views_pre_render().

See this answer for show fields after adding joins in hook_views_query_alter for more info.