Order by distance #42

Closed
burlesona opened this Issue Mar 25, 2011 · 33 comments

Projects

None yet
@burlesona

I can't seem to figure out how to order near results by distance from the search coordinates. How do you do that?

@alexreisner
Owner

Just add .order("distance")

@burlesona

Actually I tried that. I get:

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: distance:

@alexreisner
Owner

Oops, I think you found a bug. I'm not at my computer now but I think I know
what the problem is. It should work in MySQL and Postgres, just not SQLite.
I'll fix it as soon as I get a chance.

@burlesona

Great, thanks!

@burlesona burlesona closed this Mar 26, 2011
@alexreisner alexreisner reopened this Mar 26, 2011
@alexreisner
Owner

Actually, after some further thought, I'm not so sure I know what's going on. Are you using gem version 0.9.11? This problem should have been fixed in that release.

@burlesona

Ah, I was on 0.9.10, and after updating it's fixed.

Thanks for looking into it, and for making this fantastic gem!

On Mar 25, 2011, at 10:17 PM, alexreisner wrote:

Actually, after some further thought, I'm not so sure I know what's going on. Are you using gem version 0.9.11? This problem should have been fixed in that release.

Reply to this email directly or view it on GitHub:
#42 (comment)

@alexreisner
Owner

Just remember that the distances you get with SQLite are not very accurate, so make sure you're using MySQL or Postgres in production.

@calderon

I have the same problem. I just added Geocoder to my project:

# store.rb
class Store < ActiveRecord::Base
  validates :city, :address, :hours_of_opening, presence: true

  attr_accessible :address, :lat, :lon

  geocoded_by :address, latitude: :lat, longitude: :lon

  after_validation :geocode, if: :address_changed?

  def self.stores_by_city
    Store.all.group_by(&:city)
  end

end
 #stores_controller.rb
class StoresController < ApplicationController
  respond_to :html

  def index
    respond_with @stores do |format|
      format.html{
        if params[:search].present?
          @stores = Store.near(params[:search], 50, :unit => :kilometers, :order => :distance)
        else
          @stores = Store.stores_by_city
        end
      }
    end
  end
end

When I open http://localhost:3000/stores I get this error:

    ActionView::Template::Error (Mysql2::Error: Unknown column 'distance' in 'order clause': [A HUGE SQL QUERY] 

I use Ruby 1.9.2, Rails 3.1 and latest Geocoder 1.0.5

@alexreisner
Owner

Can you please include the "[A HUGE SQL QUERY]"?

Also, note: it should be :units => :km.

@calderon

I fix that, thanks.

Here is the mysql query:

(Mysql2::Error: Unknown column 'distance' in 'order clause': SELECT COUNT(*) AS count_all, stores.id,stores.city,stores.address,stores.phone,stores.hours_of_opening,stores.lat,stores.lon,stores.created_at,stores.updated_at AS stores_id_stores_city_stores_address_stores_phone_stores_hours_of_opening_stores_lat_stores_lon_stores_created_at_stores_updated_at FROM `stores`  WHERE (lat BETWEEN 36.69407738444577 AND 38.14139521555425 AND lon BETWEEN -6.912185720287322 AND -5.089887279712677) GROUP BY stores.id,stores.city,stores.address,stores.phone,stores.hours_of_opening,stores.lat,stores.lon,stores.created_at,stores.updated_at HAVING 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.41773630000001 - lat) * PI() / 180 / 2), 2) + COS(37.41773630000001 * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((-6.0010365 - lon) * PI() / 180 / 2), 2) )) <= 50 ORDER BY distance)
@alexreisner
Owner

This looks odd. Can you tell me what happens if you get rid of :order => :distance? (should be the default order anyway) Can you also try running Store.near(...) in the console to make sure the error is coming from where we think it is?

@calderon

If I get rid of :order => :distance works 'well' (at least, the page is shown), but I don't know if stores are ordered by distance or not.

If I run in the console

Store.near('MY LOCATION STRING', 50, unit: :km)

I get:

Store Load (0.6ms)  SELECT *, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.424226 - lat) * PI() / 180 / 2), 2) + COS(37.424226 * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((-5.9615421 - lon) * PI() / 180 / 2), 2) )) AS distance, CAST(DEGREES(ATAN2( RADIANS(lon - -5.9615421), RADIANS(lat - 37.424226))) + 360 AS decimal) % 360 AS bearing FROM `stores` WHERE (lat BETWEEN 36.70056708444576 AND 38.147884915554236 AND lon BETWEEN -6.872770288110604 AND -5.050313911889396) GROUP BY stores.id,stores.city,stores.address,stores.phone,stores.hours_of_opening,stores.lat,stores.lon,stores.created_at,stores.updated_at HAVING 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.424226 - lat) * PI() / 180 / 2), 2) + COS(37.424226 * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((-5.9615421 - lon) * PI() / 180 / 2), 2) )) <= 50 ORDER BY 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.424226 - lat) * PI() / 180 / 2), 2) + COS(37.424226 * PI() / 180) * COS(lat * PI() / 180) * POWER(SIN((-5.9615421 - lon) * PI() / 180 / 2), 2) )) ASC
@calderon

After seed the database with data examples, stores are shown in the view ordered by distance, but if I include :order => :distance still crash.

@alexreisner
Owner

You can see from the query that it is ordering results by distance, but for some reason it's not giving you a "distance" column. I'm not sure why that's happening. Can you try changing to the no_grouping branch? Just change your Gemfile to:

gem 'geocoder', :git => 'git://github.com/alexreisner/geocoder.git', :branch => 'no_grouping'

And then run:

bundle install

Let me know if that changes anything.

@phstc
phstc commented Feb 22, 2012

I have the same problem using with AR.includes

  Restaurant.includes(:dishes).near(lat, lng, 100).where("dishes.name = ?", dish_name)

Geocorder doesn't add the distance column in the query if I add AR.includes.

How can I use near and includes together? I would like to retrieve all restaurants near to a specific lat and lng AND containing a specific dish ORDERED by distance.

@morgz
morgz commented Mar 11, 2012

This bug happens to me when I add .count to the query

@thrillcall

I can confirm this bug as well. I did a bit of investigation and here is what I found.

Side note, we maintain our own fork of the Geocoder gem since we are using the current release version in a Rails 2.3x app. We only needed a single commit to allow the gem to work really well for us in Rails 2.3x (++1 if you can release a version of the gem that works on both 2.3x and 3.x). Our change is here:

thrillcall@165cfa2

With that out of the way. I just applied that commit on top of the latest master branch of your upstream Geocoder repo.

It is only in this updated version that we are seeing this problem. I can confirm that the issue was introduced somewhere between the following commits (as referenced from my forked repo):

165cfa2 (good)
02c90c8 (bad)

The commit log for that period shows:

➜  geocoder git:(thrillcall) git lg 165cfa278ecb9a7d1e53a274a4b66753659c81ee..02c90c8b7ba9bb964d3eb0f1fe9eee52d7e78fe0                                   system
* 02c90c8 Glenn Rempe - (HEAD, origin/thrillcall, thrillcall) Change scope to named_scope to allow use with Rails 2.3.x. All tests green. (4 days ago)
* 1b65830 Alex Reisner - (origin/alexreisner/master, alexreisner/master, alexreisner/master) Call false_condition instead of :id => false. (9 days ago)
*   55fd955 Alex Reisner - Merge branch 'master' of github.com:alexreisner/geocoder (9 days ago)
|\  
| *   5af9f7b Alex Reisner - Merge pull request #193 from rfb/master (2 weeks ago)
| |\  
| | * 0fab1ae Ryan Barber - make it possible to raise Geocoder::OverQueryLimitError, issue #187 (2 weeks ago)
| |/  
* | eba79f2 Alex Reisner - Fix issue #199. (9 days ago)
* | 864fc7b Alex Reisner - Add and use using_sqlite? method. (9 days ago)
|/  
* 42166c8 Francesco Vollero - Fix a typo 'villiage' with village (3 weeks ago)
* 096d8b5 Alex Reisner - Fix inconsistent indent. (3 weeks ago)
* 2642ddb Alex Reisner - (1.1.1) Prepare for release of gem version 1.1.1. (5 weeks ago)
*   2f11f79 Alex Reisner - Merge pull request #176 from sld/master (5 weeks ago)
|\  
| * fdbc1d9 sld - Updated lib/geocoder/results/yandex.rb. Fixed city and sub_state method. (6 weeks ago)
|/  
*   59d9349 Alex Reisner - Merge pull request #168 from galvinhsiu/master (7 weeks ago)
|\  
| * b8b5124 Galvin Hsiu - Fix for parse_raw_data -- json parse catches invalid json and activesupport::json needs to be caught. (7 weeks ago)
|/  
* 170a934 Alex Reisner - Code cleanup. (8 weeks ago)
*   7ca1ba9 Alex Reisner - Merge pull request #163 from kor6n/patch-1 (8 weeks ago)
|\  
| * 48d2e21 Alexey Falin - Fix for: Issue #162 (9 weeks ago)
|/  
*   da7db55 Alex Reisner - Merge pull request #160 from kongo/within_bounding_box_fix (9 weeks ago)
|\  
| * e6f5662 kongo - within_bounding_box scope takes field names from options (10 weeks ago)
|/  
*   e9cd55c Alex Reisner - Merge pull request #157 from JustinLove/master (3 months ago)
|\  
| * b637884 Justin Love - ensure all do_lookup blocks have empty list protection (3 months ago)
|/  
* 0e157f6 Alex Reisner - (alexreisner/dont_cache_errors) Only cache response if successful. (3 months ago)
* 6e63149 Alex Reisner - Rename methods added by last commit. (3 months ago)
*   01d2b7c Alex Reisner - Merge pull request #147 from dwilkie/master (3 months ago)
|\  
| * 4ecdf40 David Wilkie - Add the ability to order by distance in associations by extracting the distance calculation into a method 'distance_from' (4 months a
* | 6bfb1bb Alex Reisner - (1.1.0) Prepare for release of gem version 1.1.0. (4 months ago)
* |   81f2ea0 Alex Reisner - Merge branch 'always_execute_geocode_block' (4 months ago)
|\ \  
| |/  
|/|   
| * 1f04dff Alex Reisner - (alexreisner/always_execute_geocode_block) Execute block even if no geocoding results. (7 months ago)
* 60ee62e Hannes Wüthrich -  Swap arguments for BETWEEN in #within_bounding_box (4 months ago)
* 732895a Alex Reisner - Add OverQueryLimitError and raise for Google. (4 months ago)
* a0f7afa Alex Reisner - Explicitly return false if no exception raised. (4 months ago)
* 56aa273 Alex Reisner - Improve method description. (4 months ago)
* 870d3ae Alex Reisner - Allow raise_error to take a message. (4 months ago)
* a3bebda Alex Reisner - Use teardown for resetting array after each test. (4 months ago)
* 9005742 Alex Reisner - Move exceptions to new file. (4 months ago)
*   730d52d Alex Reisner - Merge pull request #144 from sdball/update-cli-for-google-premier (4 months ago)
|\  
| * c95de3c Stephen Ball - removed debugging puts statements (4 months ago)
| * fc31291 Stephen Ball - updated cli to accept google premier key (4 months ago)
* |   4592b73 Alex Reisner - Merge branch 'no_grouping' (4 months ago)
|\ \  
| |/  
|/|   
| * 4c37c98 Alex Reisner - (alexreisner/no_grouping) Remove grouping from near scope. (5 months ago)
* 47edf4e Alex Reisner - (alexreisner/select_using_table_name) Include table name in select clause. (4 months ago)
* 38db9d1 Alex Reisner - Merge pull request #141 from christophermanning/master (4 months ago)
* 0b6c6e1 Christopher Manning - Change lat and lon configurations in README to latitude and longitude so it matches the gem's defaults and the generate migratio
(END)

Here is a query I ran (and the .to_sql output as well) using the older commit vs. the newer commit:

>> Event.includes(:artists).near([37.7961540222168, -122.42085266113281], 100).where('artists.id = ?', 391737)
=> []
>> Event.includes(:artists).near([37.7961540222168, -122.42085266113281], 100).where('artists.id = ?', 391737).to_sql
=> "SELECT *, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.7961540222168 - latitude) * PI() / 180 / 2), 2) + COS(37.7961540222168 * PI() / 180) * COS(latitude * PI() / 180) * POWER(SIN((-122.42085266113281 - longitude) * PI() / 180 / 2), 2) )) AS distance, CAST(DEGREES(ATAN2( RADIANS(longitude - -122.42085266113281), RADIANS(latitude - 37.7961540222168))) + 360 AS decimal) % 360 AS bearing FROM `events` WHERE ((artists.id = 391737) AND (latitude BETWEEN 36.34883619110832 AND 39.243471853325275 AND longitude BETWEEN -124.25244577571192 AND -120.5892595465537))  GROUP BY events.id,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.created_at,events.updated_at,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.latitude,events.longitude,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE,events.PRIVATE HAVING 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.7961540222168 - latitude) * PI() / 180 / 2), 2) + COS(37.7961540222168 * PI() / 180) * COS(latitude * PI() / 180) * POWER(SIN((-122.42085266113281 - longitude) * PI() / 180 / 2), 2) )) <= 100 ORDER BY 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.7961540222168 - latitude) * PI() / 180 / 2), 2) + COS(37.7961540222168 * PI() / 180) * COS(latitude * PI() / 180) * POWER(SIN((-122.42085266113281 - longitude) * PI() / 180 / 2), 2) )) ASC"

Newer version to_sql:

>> Event.includes(:artists).near([37.7961540222168, -122.42085266113281], 100).where('artists.id = ?', 391737).to_sql
=> "SELECT events.*, 3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.7961540222168 - events.latitude) * PI() / 180 / 2), 2) + COS(37.7961540222168 * PI() / 180) * COS(events.latitude * PI() / 180) * POWER(SIN((-122.42085266113281 - events.longitude) * PI() / 180 / 2), 2) )) AS distance, CAST(DEGREES(ATAN2( RADIANS(longitude - -122.42085266113281), RADIANS(latitude - 37.7961540222168))) + 360 AS decimal) % 360 AS bearing FROM `events` WHERE ((artists.id = 391737) AND (3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.7961540222168 - events.latitude) * PI() / 180 / 2), 2) + COS(37.7961540222168 * PI() / 180) * COS(events.latitude * PI() / 180) * POWER(SIN((-122.42085266113281 - events.longitude) * PI() / 180 / 2), 2) )) <= 100))  ORDER BY distance"

Newer version fail:

>> Event.includes(:artists).near([37.7961540222168, -122.42085266113281], 100).where('artists.id = ?', 391737)
ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'distance' in 'order clause': SELECT `events`.`id` AS t0_r0, `events`.`PRIVATE` AS t0_r1, `events`.`PRIVATE` AS t0_r2, `events`.`PRIVATE` AS t0_r3, `events`.`PRIVATE` AS t0_r4, `events`.`PRIVATE` AS t0_r5, `events`.`created_at` AS t0_r6, `events`.`updated_at` AS t0_r7, `events`.`PRIVATE` AS t0_r8, `events`.`PRIVATE` AS t0_r9, `events`.`PRIVATE` AS t0_r10, `events`.`PRIVATE` AS t0_r11, `events`.`PRIVATE` AS t0_r12, `events`.`PRIVATE` AS t0_r13, `events`.`PRIVATE` AS t0_r14, `events`.`PRIVATE` AS t0_r15, `events`.`PRIVATE` AS t0_r16, `events`.`PRIVATE` AS t0_r17, `events`.`PRIVATE` AS t0_r18, `events`.`PRIVATE` AS t0_r19, `events`.`PRIVATE` AS t0_r20, `events`.`latitude` AS t0_r21, `events`.`longitude` AS t0_r22, `events`.`PRIVATE` AS t0_r23, `events`.`PRIVATE` AS t0_r24, `events`.`PRIVATE` AS t0_r25, `events`.`PRIVATE` AS t0_r26, `events`.`PRIVATE` AS t0_r27, `artists`.`id` AS t1_r0, `artists`.`PRIVATE` AS t1_r1, `artists`.`PRIVATE` AS t1_r2, `artists`.`PRIVATE` AS t1_r3, `artists`.`PRIVATE` AS t1_r4, `artists`.`PRIVATE` AS t1_r5, `artists`.`PRIVATE` AS t1_r6, `artists`.`PRIVATE` AS t1_r7, `artists`.`PRIVATE` AS t1_r8, `artists`.`PRIVATE` AS t1_r9, `artists`.`created_at` AS t1_r10, `artists`.`updated_at` AS t1_r11, `artists`.`PRIVATE` AS t1_r12, `artists`.`PRIVATE` AS t1_r13, `artists`.`PRIVATE` AS t1_r14, `artists`.`PRIVATE` AS t1_r15, `artists`.`PRIVATE` AS t1_r16, `artists`.`PRIVATE` AS t1_r17, `artists`.`PRIVATE` AS t1_r18, `artists`.`PRIVATE` AS t1_r19, `artists`.`rating` AS t1_r20, `artists`.`PRIVATE` AS t1_r21, `artists`.`PRIVATE` AS t1_r22, `artists`.`PRIVATE` AS t1_r23, `artists`.`PRIVATE` AS t1_r24, `artists`.`PRIVATE` AS t1_r25, `artists`.`PRIVATE` AS t1_r26, `artists`.`PRIVATE` AS t1_r27, `artists`.`PRIVATE` AS t1_r28, `artists`.`PRIVATE` AS t1_r29, `artists`.`PRIVATE` AS t1_r30 FROM `events`  LEFT OUTER JOIN `bookings` ON (`events`.`id` = `bookings`.`event_id`)  LEFT OUTER JOIN `artists` ON (`artists`.`id` = `bookings`.`artist_id`) WHERE ((artists.id = 391737) AND (3958.755864232 * 2 * ASIN(SQRT(POWER(SIN((37.7961540222168 - events.latitude) * PI() / 180 / 2), 2) + COS(37.7961540222168 * PI() / 180) * COS(events.latitude * PI() / 180) * POWER(SIN((-122.42085266113281 - events.longitude) * PI() / 180 / 2), 2) )) <= 100))  ORDER BY distance
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/connection_adapters/abstract_adapter.rb:227:in `rescue in log'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/connection_adapters/abstract_adapter.rb:204:in `log'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/connection_adapters/mysql_adapter.rb:324:in `execute'
    from /Users/glenn/src/thrillcall/thrillcall/config/initializers/connection_fix.rb:20:in `execute'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/connection_adapters/mysql_adapter.rb:639:in `select'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all_with_query_cache'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/associations.rb:1622:in `select_all_rows'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/associations.rb:1401:in `block in find_with_associations'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/associations.rb:1399:in `catch'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/associations.rb:1399:in `find_with_associations'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/base.rb:1580:in `find_every'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/base.rb:619:in `find'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:186:in `block in method_missing'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/fake_arel-0.9.9/lib/fake_arel/with_scope_replacement.rb:68:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:118:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:179:in `method_missing'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:186:in `block in method_missing'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/fake_arel-0.9.9/lib/fake_arel/with_scope_replacement.rb:68:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:118:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:118:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:179:in `method_missing'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:186:in `block in method_missing'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/fake_arel-0.9.9/lib/fake_arel/with_scope_replacement.rb:68:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:118:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:118:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:118:in `with_scope'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:179:in `method_missing'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:193:in `load_found'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:171:in `proxy_found'
    from /Users/glenn/.rvm/gems/ruby-1.9.2-p290@thrillcall/gems/activerecord-2.3.14/lib/active_record/named_scope.rb:114:in `inspect'
    from /Users/glenn/.rvm/rubies/ruby-1.9.2-p290/bin/irb:16:in `<main>'>>

Most of the column names changed to PRIVATE to protect the innocent. :-)

@trappist

For me, joins works where includes does not. I think this is a duplicate of #99 and #243.

@jacek213

+1 for the includes problem

@c-alvarez

+1 for the includes + where problem. If you use a where on the query, they distance column is not added correctly anymore.

@vassyz
vassyz commented May 29, 2014

Yeah, broken for me as well. I'm searching inside a huge radius (3000) and I have to use a where country statement. I don't get the results ordered by distance.

@sungwoncho

I have the same problem that the original poster had. When I pass order: :distance as an argument in near, I get `SQLite3::SQLException: no such column: locations.distance: SOME QUERIES.

@8vius
8vius commented Oct 30, 2014

I have this same problem

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "distance" does not exist LINE 1: ...2,205,415,51,208,52,61,4,77,68,14,90,80)) ORDER BY distance

@nekliw
nekliw commented Apr 21, 2015

I have the same issue w/ :order => :distance. Any solutions?

@alexnofoget

Type :order => 'distance' instead :order => :distance

@Baril27
Baril27 commented Nov 6, 2015

+1 on this issue.

My original query on rails 3 was:

scope = scope.near(coords, radius, order: 'distance').limit(100)

When I migrated to rails 4 I started to get:

ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "distance" does not exist

So I had to change my query to get it to work. Now its:

scope = scope.near(coords, radius, order: '').limit(100)

Any news on this issue?

NOTE: I can get more details on the error I originally ran into if that would help. I would just need to migrate my project back to rails 3.

@rohanpujaris

+1 on this issue. I am using geocoder gem 1.2.14 and rails 4.2.5. Though removing order: :distance solves the issue. Also by default its ordered by distance so no worry till now.

@MaxPleaner

This issue is closed, but many like myself are still encountering the issue. In fact it is a newly emerged issue for me. Is there a different issue thread which is open or contains a confirmed solution?

Now that I've checked, this issue contains links to the parts in the READMe where the issue is discussed.

What solved it for me was to change my pluck(:id) call to select(:id).

@looser990

I had the same issue, but I don't even use Geocoder, so maybe worthwhile mentioning.
I'm on Ruby 1.9.3 with rails 4.0.1
And I have a query for search + ordering for models User and Billing Infos. Users has one to many Billing infos and I'm searching through address/city/zip on Billing info, something along lines:

User.select('id, name, lastname, CONCAT(users.firstname, \' \', users.lastname) as fullname').
inclues(:billing_infos).
where("billing_infos.address like %#{address} and 
billing_infos.zip like %#{zip}% and
billing_infos.city like %#{city}%").
references(:billing_infos).order("#{sort_column} #{sort_direction}") 

Witch yields unknown_column for fullname if I try to order by it, but when I just change joins instead of includes it works as expected.
I know it seems it is offtopic but I have feeling this is somehow connected so maybe it will help you guys.

Regards.

@mikedhart

Just thought I'd leave a note on this - I hit this issue when using pluck. When I don't use pluck, it works fine.

Was: ContactPoint.near([c.latitude, c.longitude], Centre::DEFAULT_RADIUS).pluck(:id)
Now: ContactPoint.near([c.latitude, c.longitude], Centre::DEFAULT_RADIUS).map(&:id)

Not ideal, but a small price to pay for such a good gem.

@alexey
alexey commented Mar 29, 2016

@mikedhart +1
.map instead of .pluck solve this issue, but i recommend to use .select(:id).map(&:id) for better selection

@mikedhart

@alexey agreed.

@johansmitsnl johansmitsnl added a commit to johansmitsnl/trackdays4fun that referenced this issue Nov 10, 2016
@johansmitsnl johansmitsnl Order by distance
Need to use select and map instead of pluck.
alexreisner/geocoder#42
cbc414c
@shivabhusal

and, how would you alter the direction of order?
I mean, I want to show the farthest item at the top (as selected by the user) and toggle.

context

@stores = Store.near(params[:search], 50, :unit => :kilometers, :order => :distance)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment