.order('distance DESC') fails with ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'distance' in 'order clause'

[geokit]

.order('distance DESC') fails with ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'distance' in 'order clause'

[wspruijt]

I have the exact same problem.

It is caused by the fact that - somehow - the column alias for distance is missing (version 2.0.1) Therefore, when adding the order, the "undefined method `distance'" message will appear.

A (temporary) workaround is to include the distance (using distance_sql) in the order clause:

geokit_origin = Geokit::LatLng.new(39.833193, -94.862794)
order_sql = Company.distance_sql(geokit_origin, units: :kms)
companies.within(10, units: :kms, origin: geokit_origin).order("#{order_sql} ASC")

[hiphapis]

you can use by_distance

locations = Location.by_distance(origin: @YOUR_LOCATION)

[mnoack]

See #39 for a similar discussion on this where an alternative has been created.

[wspruijt]

Thanks @hiphapis, that is an easier solution. However, you can't sort descending by distance.

[hiphapis]

@wspruijt you known reverse_order

locations = Location.by_distance(origin: @YOUR_LOCATION).reverse_order

if you use rails3 then just use reverse ;)

locations = Location.by_distance(origin: @YOUR_LOCATION).reverse

[thoraxe]

hiphapis - when you use "by_distance" it appears you lose the distance column:

barns = Barn.by_distance(origin: "30093")
barns[0].distance
 => nil

The same is true for wspruijt's suggestion.

mnoack's suggestion appears to work great:

((DistanceCollection.new(Barn.within(5, :origin => "30093"))).set_distance_from("30093")).sort_by(&:distance)[0].distance
  Barn Load (0.7ms)  SELECT `contacts`.* FROM `contacts` WHERE `contacts`.`type` IN ('Barn') AND ((
 (ACOS(least(1,COS(0.591845095540716)*COS(-1.4692258158522673)*COS(RADIANS(contacts.lat))*COS(RADIANS(contacts.lng))+
 COS(0.591845095540716)*SIN(-1.4692258158522673)*COS(RADIANS(contacts.lat))*SIN(RADIANS(contacts.lng))+
 SIN(0.591845095540716)*SIN(RADIANS(contacts.lat))))*3963.19)
 <= 5))
 => 2.0967481609455723

If I find some time I might issue a pull request for that method, but I can hack around it.