How to use $query->add_where_expression in hook_views_query_alter

Question

I'm trying to use hook_views_query_alter() to select some nodes based on a calculation using two fields in that node. I can't find much documentation or many examples that help me understand how this works.

I could use Views PHP, but this module is very buggy in Drupal 7.

I think I need to use $query->add_where_expression() but I can't work out how to use it.

My view is called "latlong", and as a test to see if I have the right approach, I've tried to select nodes based on the value of field_latitude. Copying other functions i've seen through googling, this is my best guess, but it's not working. I know one of my nodes has the value 53.9519 so if my syntax is right I should get a row back, but I don't:

function my_module_views_query_alter(&$view, &$query) {
  switch($view->name) {
    case 'latlong':  
      $query->add_where_expression(0,'field_data_field_latitude.field_latitude_value = 53.9519');      
      break;
  }
}

If I print_r($query->where[0]) I get:

Array ( 
  [conditions] => 
    Array ( 
      [0] => 
        Array ( 
         [field] => field_data_field_latitude.field_latitude_value = 53.9519
         [value] => Array ( ) 
         [operator] => formula ) ) 
         [args] => Array ( ) 
         [type] => AND )

What am I doing wrong?

Answer 1

If you look at the documentation for views_plugin_query_default::add_where_expression(), you will notice the following note. (The emphasys is mine.)

The caller is reponsible for ensuring that all fields are fully qualified (TABLE.FIELD) and that the table already exists in the query. Internally the dbtng method "where" is used.

Looking at this Stack Overflow question, I would get your code should be the following one.

function mymodule_views_query_alter(&$view, &$query) {
  switch($view->name) {
    case 'latlong':
      $alias = $query->add_table('field_data_field_latitude');  
      $query->add_where_expression(0, "$alias.field_latitude_value = :latitude", array(':latitude' => 53.9519);      
      break;
  }
}

Since the latitude is a floating point, I would use a comparison that is different from $alias.field_latitude_value = :latitude, for example checking the latitude is inside the range [$latitude - $epsilon, $latitude + $epsilon].

Answer 2

The query dump you provided definitely looks wrong.

If you read through views_plugin_query_default::add_where_expression(), you will see that the the $snippet is just the clause, and that you should really be adding the {table.field} blip with an placeholder.

I think that this is what you really want

$query->add_where_expression(0, 'field_data_field_latitude.field_latitude_value = :latitude', array(':latitude' => 53.9519));

However, doing exact comparisons with floating point values with decimal portions can be troublesome. You should test this with something simple (say, node.uid) to be double sure. If the simple test works, but yours doesn't then I suspect you are running into floating point problems.

You can also preview the SQL to see exactly what is being generated. Copy/paste this into your favorite SQL tool and see what is going on.

You mention that you are trying to find the twenty nodes closest to a point. Your approach is wrong for this. You need to add a correlation field that gets returns the distance between the node in that row and your point, sort ascending on it, and then limit to 20 results. This isn't a total pain, but not simple, either (I just did this on a project).

Thankfully, there are at least two modules that can do this for you. One is Geofield, the other is Location. Both integrate with Views, and I know Geofield support proximity sorting, and I am pretty sure Location does to. Using one of these may require converting your fields to use the ones defined by the module, but those two modules provide other benefits, too.