2

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?

  • When you say you're selecting some nodes based on a calculation of 'that node'. Is 'that node' the page which the view will display in? – leexonline Mar 25 '14 at 20:14
  • @leexonline - sorry I mean based on a calculation of fields in the nodes selected in the query. So I want to select nodes based on a calculation. I actually want to choose the 20 nodes that are nearest a point so I need to do some trigonometry. – gillarf Mar 26 '14 at 7:30
  • Not sure why you deleted the API reference to the function you are using, but it is your question... – mpdonadio Mar 26 '14 at 13:14
  • I edited it, and just made a mistake I think @MPD – gillarf Mar 26 '14 at 15:46
8

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].

  • If the add_table was the problem, wouldn't it throw a PDO exception? – mpdonadio Mar 26 '14 at 16:24
  • The above works beautifully, thanks even for floating point number, although i had to put the table name into the expression rather than $alias (no idea why). I would vote up if I had enough points :-) – gillarf Mar 26 '14 at 17:29
  • @gillarf Are you sure you used double quotation marks (")? If you used single quotation marks as string delimiters, it would not work. – kiamlaluno Mar 26 '14 at 19:19
1

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.

  • Thanks @MPD, I know about location and geofield, and I am considering using them, but my data is already in a text field, and before I do a batch update, I want to see if I can solve my problem this way. Also, the 'point' I want to compare to is not available to the view, it is passed in the querystring(e.g. examplesite.com/latlong?args[0]=53.123&args[1]=0.1245) which is why I am trying to use hook_views_query_alter instead of contextual filters. Also, I am actually trying to fetch all nodes within x miles - apologies for misinformation above! So I don't need to to sort :-) Will try to debug. – gillarf Mar 26 '14 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.