When I search based on proximity (at URL/search/location), I get a database error if there is a result:

* user warning: Not unique table/alias: 'l' query: SELECT COUNT(*) FROM (SELECT i.type, i.sid, (IFNULL(ACOS(0.832261556152*COS(RADIANS(l.latitude))*(-0.469477323783*COS(RADIANS(l.longitude)) + -0.882944529658*SIN(RADIANS(l.longitude))) + 0.554383172681*SIN(RADIANS(l.latitude))), 0.00000)*6371541.65738) AS distance FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN location l ON l.lid = i.sid INNER JOIN location l ON i.sid = l.lid WHERE 1 = 1 AND l.latitude > 33.4883907221 AND l.latitude < 33.8480887832 AND l.longitude > -118.216470764 AND l.longitude < -117.784276917 AND (IFNULL(ACOS(0.832261556152*COS(RADIANS(latitude))*(-0.469477323783*COS(RADIANS(longitude)) + -0.882944529658*SIN(RADIANS(longitude))) + 0.554383172681*SIN(RADIANS(latitude))), 0.00000)*6371541.65738) < 20000 AND (i.word = 'coffee') AND i.type = 'location' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1) n1 in /PATH_TO_DRUPAL/modules/search/search.module on line 955.
* user warning: Not unique table/alias: 'l' query: SELECT i.type, i.sid, (IFNULL(ACOS(0.832261556152*COS(RADIANS(l.latitude))*(-0.469477323783*COS(RADIANS(l.longitude)) + -0.882944529658*SIN(RADIANS(l.longitude))) + 0.554383172681*SIN(RADIANS(l.latitude))), 0.00000)*6371541.65738) AS distance FROM search_index i INNER JOIN search_total t ON i.word = t.word INNER JOIN location l ON l.lid = i.sid INNER JOIN location l ON i.sid = l.lid WHERE 1 = 1 AND l.latitude > 33.4883907221 AND l.latitude < 33.8480887832 AND l.longitude > -118.216470764 AND l.longitude < -117.784276917 AND (IFNULL(ACOS(0.832261556152*COS(RADIANS(latitude))*(-0.469477323783*COS(RADIANS(longitude)) + -0.882944529658*SIN(RADIANS(longitude))) + 0.554383172681*SIN(RADIANS(latitude))), 0.00000)*6371541.65738) < 20000 AND (i.word = 'coffee') AND i.type = 'location' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 ORDER BY distance ASC LIMIT 0, 10 in /PATH_TO_DRUPAL/modules/search/search.module on line 955.

If there are no nodes within the proximity given, I get a normal "Your search yielded no results" message.

Comments

Marcelo Gonçalves Diotto’s picture

Marcelo Gonçalves Diotto commented

To fix this erros just go to file "modules/search/search.module" and change the line 955 from this:

$columns2 = str_replace('i.relevance', '('. (1.0 / $normalize) .' * SUM(i.score * t.count))', $columns2);

to this:

$columns2 = str_replace('i.relevance', (1.0 / $normalize) .' * SUM(i.score * t.count)', $columns2);

* I just removed the "(" and ")" from the query. Worked for me!

liquidcool’s picture

liquidcool commented

I tried this out, but it didn't fix it. For me, it was on line 947 - perhaps you've upgraded to 6.9? I'm still on 6.8 (it's a hidden site, not yet in production).

But I found that you specify proximity *only* - the lat/long (using map chooser) and a radius, it works! If you also specify a search word or phrase, however, you get the error listed above.

Thanks for the suggestion, though.

tronic’s picture

tronic commented
Version: 6.x-3.0 » 6.x-3.1-rc1

I get the same error too, and try to edit the module and still get the error. I'm using 6.10 and 6.x-3.1-rc1

yesct’s picture

yesct commented

Marcelo, What error were you getting before you removed the parens? Maybe that should be a separate issue? Do you think that it is a general fix that needs to be committed?

yesct’s picture

yesct commented
Title: Proximity search yields database error » Proximity search yields database error (Not unique table/alias ... line 955) when specifiying proximity and a search word
Issue tags: +Location proximity view

tagging.

I know it is frustrating, but could you try with the latest dev version?

aniss55’s picture

aniss55 commented

It does a inner join on location twice with same alias l.
I just changes Line 125.
From:
$join2 = 'INNER JOIN {location} l ON i.sid = l.lid';
To:
$join2 = '';

netbabu’s picture

netbabu commented

Can any confirm if the proximity search bug is fixed in the latest release?

Promote Drupal with Tshirts/merchandise: http://paramprojects.com/drupalstore

yesct’s picture

yesct commented
Status: Active » Postponed (maintainer needs more info)

Might be fixed in the new official release, lots of proximity search done in there.

ankur’s picture

ankur commented
Status: Postponed (maintainer needs more info) » Closed (cannot reproduce)

Closing due to lack of follow up.