search

geokit / geokit-rails

Official Geokit plugin for Rails/ActiveRecord. Provides location-based goodness for your Rails app. Requires the Geokit gem.
MIT License
1.57k stars 245 forks source link

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

Closed thoraxe closed 10 years ago

thoraxe commented 10 years ago 
Barn.within(5, :origin => "30093").order('distance DESC')

This produces:

Barn Load (0.3ms)  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)) ORDER BY distance DESC
ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'distance' in 'order clause': 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)) ORDER BY distance DESC
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/mysql_adapter.rb:308:in `query'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/mysql_adapter.rb:308:in `block in exec_without_stmt'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activesupport-3.2.14/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/mysql_adapter.rb:307:in `exec_without_stmt'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/mysql_adapter.rb:290:in `exec_query'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/mysql_adapter.rb:430:in `select'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/querying.rb:38:in `block in find_by_sql'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/explain.rb:41:in `logging_query_plan'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/querying.rb:37:in `find_by_sql'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/relation.rb:171:in `exec_queries'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/relation.rb:160:in `block in to_a'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/explain.rb:41:in `logging_query_plan'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/relation.rb:159:in `to_a'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/relation.rb:498:in `inspect'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/railties-3.2.14/lib/rails/commands/console.rb:47:in `start'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/railties-3.2.14/lib/rails/commands/console.rb:8:in `start'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/railties-3.2.14/lib/rails/commands.rb:41:in `<top (required)>'
        from script/rails:6:in `require'
        from script/rails:6:in `<main>'

Doing the order directly in the search works, but the SQL doesn't appear to do anything with distance:

Barn.within(5, :origin => "30093", :order => 'distance desc')
Barn Load (4.3ms)  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))

And then the distance column isn't available:

barns[0].distance
NoMethodError: undefined method `distance' for #<Barn:0xb5a525c>
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activemodel-3.2.14/lib/active_model/attribute_methods.rb:407:in `method_missing'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/activerecord-3.2.14/lib/active_record/attribute_methods.rb:149:in `method_missing'
        from (irb):9
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/railties-3.2.14/lib/rails/commands/console.rb:47:in `start'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/railties-3.2.14/lib/rails/commands/console.rb:8:in `start'
        from /home/riding/.rvm/gems/ruby-1.9.3-p362@rr-3xmid/gems/railties-3.2.14/lib/rails/commands.rb:41:in `<top (required)>'
        from script/rails:6:in `require'
        from script/rails:6:in `<main>'

Here's relevant bits from the model:

acts_as_mappable :auto_geocode => true, :distance_field_name => :distance

I tried adding attr_accessor :distance and attr_accessible :distance to no avail.

Here's my Gemfile:

source 'https://rubygems.org'

gem 'rails', '3.2.14'

gem 'mysql'

# Gems used only for assets and not required
# in production environments by default.
group :assets do
  gem 'sass-rails',   '~> 3.2.3'
  gem 'coffee-rails', '~> 3.2.1'
  # See https://github.com/sstephenson/execjs#readme for more supported runtimes
  gem 'therubyracer', :platforms => :ruby
  gem 'uglifier', '>= 1.0.3'
end

gem 'jquery-rails', '3.0.4'
gem 'twitter', '4.8.1'
gem 'haml', '2.2.6'
gem 'formtastic', '2.2.1'
gem 'json', '1.8.0'
gem 'tld', '0.6.4'
gem 'addressable', '2.1.0'
gem 'stringex', '2.1.0'
gem 'will_paginate', '~> 3.0.0'
gem 'geokit-rails', '2.0.1'
gem 'awesome_print', '1.1.0'
gem 'paperclip', '3.5.2'
gem 'gruff', '0.3.6'
gem 'rmagick', '2.11.0'

group :development, :test do
  gem 'rspec-rails', '~> 2.0'
  gem 'shoulda-matchers', '2.4.0'
  gem "capybara", '2.1.0'
  gem "webrick", '~> 1.3.1'
  gem "pry", '0.9.12.3'
end

group :test do
  gem 'database_cleaner', '1.1.1'
end

I would be happy to test additional things, but I'm not sure where to start digging.

wspruijt commented 10 years ago

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 commented 10 years ago

you can use by_distance

locations = Location.by_distance(origin: @YOUR_LOCATION)
mnoack commented 10 years ago

See https://github.com/geokit/geokit-rails/issues/39 for a similar discussion on this where an alternative has been created.

wspruijt commented 10 years ago

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

hiphapis commented 10 years ago

@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 commented 10 years ago

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.