Problems finding locations nearby within a certain radius

[Abhijeet]

I am having problems finding locations nearby within a radius of 10
kms. This is my Ruby code:

@events = Event.all_with_distance([current_user.geo_lat,
current_user.geo_lng]).where("private = ? AND start > ?", false,
Time.zone.now).order("distance DESC").order("start
DESC").having("distance<?",10).limit(5)

This is the SQL:

select("#{table_name}.*, #{distance_sql} AS
distance").select("`locations`.`geo_lat`, `locations`.`geo_lng`,
`locations`.`name` as location_name").joins(:location)

This is the error I am getting:

Mysql2::Error: Unknown column 'distance' in 'having clause': SELECT
COUNT(count_column) FROM (SELECT 1 AS count_column FROM `events`
INNER JOIN `locations` ON `locations`.`id` = `events`.`location_id`
WHERE (private = 0 AND start > '2011-12-28 11:52:07') HAVING
distance<10.5 LIMIT 5) subquery_for_count

I get the same error upon using WHERE clause too.

Please help.

[Daniel Morrison]

If you look at the final query, you're not SELECTing the "distance" column. Probably because of the count.

If you're using ActiveRecord 3+, you can to .to_sql at the end of any query scope to see the generated SQL.

Also, look at the code in acts_as_geocodable for suggestions.

-Daniel

--
Daniel Morrison
[i] Collective Idea
http://collectiveidea.com
http://ideafoundry.info
25 West 8th Street, Suite 200
Holland, MI 49423
616-990-0155
dan...@collectiveidea.com