error when used with custom module [#116936]

Hello,

I'm trying to use the location_views module with a custom module that I've written and I've hit a bug that I can't reconcile. Whenever I try to use the "Location: proximity" filter (exposed), I get the following SQL error:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(((IFNULL(ACOS(0.88006020692029*COS(RADIANS(latitude))*(0.16213601551433*COS(RAD' at line 1 query: SELECT node.nid, node.title AS node_title, node.changed AS node_changed, fgbc_project.certification_type AS fgbc_project_certification_type, fgbc_project.city AS fgbc_project_city FROM node node LEFT JOIN fgbc_project fgbc_project ON node.nid = fgbc_project.project_id LEFT JOIN location location ON node.vid = location.eid WHERE (node.type IN ('fgbc_project')) AND (fgbc_project.certification_score >= '') AND (location.longitude IS NOT NULL) AND (location.latitude > 28.27803304409 AND location.latitude < 28.42271295591 AND location.longitude > -80.751298877914 AND location.longitude < -80.586901122086) ORDER BY distance.(((IFNULL(ACOS(0.88006020692029*COS(RADIANS(latitude))*(0.16213601551433*COS(RADIANS(longitude)) + -0.98676841886693*SIN(RADIANS(longitude))) + 0.47486211914135*SIN(RADIANS(latitude))), 0.00000)*6373296.0376023)) / 1609.347) ASC, node.title ASC LIMIT 0, 30 in /Users/michael/Sites/fgbc/db/includes/database.mysqli.inc on line 122.

Looking at the error, it appears that it is confined to the first part of the "order by" clause:

distance.(((IFNULL(ACOS(0.88006020692029*COS(RADIANS(latitude))*(0.16213601551433*COS(RADIANS(longitude)) + -0.98676841886693*SIN(RADIANS(longitude))) + 0.47486211914135*SIN(RADIANS(latitude))), 0.00000)*6373296.0376023)) / 1609.347) ASC

When I remove that part of the "order by" clause, the rest of the query works fine.

I'm a bit confused by this part of the query, as I don't believe that the "distance" table exists. I've tracked it down to the "earth_distance_sql()" function in location_views.module, but that's where I'm stumped. What actually is "distance"?

What is going on here? Is it an issue with my custom module (fgbc_project)?

Thanks in advance,
-mike

Comments

Comment #1

KarenS CreditAttribution: KarenS commented 9 February 2007 at 01:33

'distance' isn't a table, it's an alias for a computed value. I can't tell much else about why you're having problems without:

1) an export copy of your view
2) some idea of what this custom module is trying to do with Views -- does it have views handlers or use the Views hooks or anything?

Comment #2

ultimike

English

Florida, USA

CreditAttribution: ultimike commented 9 February 2007 at 03:04

Karen,

Great - thanks for the help. As I mentioned via email, the issue isn't limited to the custom module. I just created a view based on standard nodes and it is still happening. Here's the export of the view:

  $view = new stdClass();
  $view->name = 'proximity';
  $view->description = '';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = '';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'table';
  $view->url = 'views/proximity';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '10';
  $view->sort = array (
  );
  $view->argument = array (
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => 'Name',
      'handler' => 'views_handler_field_nodelink',
    ),
    array (
      'tablename' => 'location',
      'field' => 'address',
      'label' => 'Address',
    ),
    array (
      'tablename' => 'location',
      'field' => 'distance',
      'label' => 'Distance',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'story',
),
    ),
    array (
      'tablename' => 'location',
      'field' => 'proximity',
      'operator' => '5',
      'options' => '',
      'value' => '',
    ),
  );
  $view->exposed_filter = array (
    array (
      'tablename' => 'location',
      'field' => 'proximity',
      'label' => 'Proximity',
      'optional' => 0,
      'is_default' => 1,
      'operator' => 0,
      'single' => 1,
    ),
  );
  $view->requires = array(node, location);
  $views[$view->name] = $view;

I figure my next step is to install a fresh copy of 4.7 and Views and see if I can reproduce it on that.

What do you think?

Thanks,
-mike

Comment #3

KarenS CreditAttribution: KarenS commented 9 February 2007 at 16:03

Yes, try a fresh install of only the relevant modules -- Location, Location Views, and Views.

Comment #4

ultimike

English

Florida, USA

CreditAttribution: ultimike commented 11 February 2007 at 13:54

That did it - I updated Views and Location and it fixed it. Should've tried that before I bothered you!

You going to be at Drupalcon at OpenCMS? If so, I owe you a drink.

-mike

Comment #5

KarenS CreditAttribution: KarenS commented 12 February 2007 at 14:20

Status:

Active

» Fixed

I'm glad you got it working. I plan to be at DrupalCon. No drinks are necessary, but would love to say hi, so please introduce yourself if we cross paths!