CODEWITHSUNDEEP
ruby-on-railsrubyrails-geocoder
lflflf
lflflf

Reputation: 67

I want to join tables and sort them with Geocoder Gem

What I want to solve

I want to combine the Area Model, Shop Model, and Account Model and get the results sorted in order of proximity from the location specified by the Geocoder Gem.

I want to list the results using each in a view.

However, I get a no such column: distance error as shown below.

In this case, no error will occur.

irb(main):043:0> Area.eager_load(:shop).near("tokyo")
  SQL (0.4ms)  SELECT areas.*, (69.09332411348201 * ABS(areas.latitude - 35.6828387) * 0.7071067811865475) + (59.836573914187355 * ABS(areas.longitude - 139.7594549) * 0.7071067811865475) AS distance, CASE WHEN (areas.latitude >= 35.6828387 AND areas.longitude >= 139.7594549) THEN  45.0 WHEN (areas.latitude <  35.6828387 AND areas.longitude >= 139.7594549) THEN 135.0 WHEN (areas.latitude <  35.6828387 AND areas.longitude <  139.7594549) THEN 225.0 WHEN (areas.latitude >= 35.6828387 AND areas.longitude <  139.7594549) THEN 315.0 END AS bearing, "areas"."id" AS t0_r0, "areas"."prefectures" AS t0_r1, "areas"."municipalities" AS t0_r2, "areas"."house_number" AS t0_r3, "areas"."building_name" AS t0_r4, "areas"."postal_code" AS t0_r5, "areas"."created_at" AS t0_r6, "areas"."updated_at" AS t0_r7, "areas"."latitude" AS t0_r8, "areas"."longitude" AS t0_r9, "shops"."id" AS t1_r0, "shops"."shop_name" AS t1_r1, "shops"."created_at" AS t1_r2, "shops"."updated_at" AS t1_r3, "shops"."shop_type_id" AS t1_r4, "shops"."station_id" AS t1_r5, "shops"."area_id" AS t1_r6 FROM "areas" LEFT OUTER JOIN "shops" ON "shops"."area_id" = "areas"."id" WHERE (areas.latitude BETWEEN 35.3933751337783 AND 35.972302266221696 AND areas.longitude BETWEEN 139.40308602609107 AND 140.11582377390894) ORDER BY distance ASC LIMIT ?  [["LIMIT", 11]]

In this case, an error will occur

I want this to work.

irb(main):045:0> Area.eager_load(shop: :accounts).near("tokyo")
  SQL (3.3ms)  SELECT DISTINCT "areas"."id" FROM "areas" LEFT OUTER JOIN "shops" ON "shops"."area_id" = "areas"."id" LEFT OUTER JOIN "accounts" ON "accounts"."shop_id" = "shops"."id" WHERE (areas.latitude BETWEEN 35.3933751337783 AND 35.972302266221696 AND areas.longitude BETWEEN 139.40308602609107 AND 140.11582377390894) ORDER BY distance ASC LIMIT ?  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: distance)

Environment

Model association

# app/models/area

has_one :shop

# app/models/shop
belongs_to :shop_type
belongs_to :area
belongs_to :station

has_many :accounts
has_many :posts

# app/models/account

belongs_to :shop, optional: true
has_many :post

Addition

I tried the below, but it failed.

irb(main):001:0> Area.near("tokyo").eager_load(shop: :accounts)
   (0.9ms)  SELECT sqlite_version(*)
  SQL (0.4ms)  SELECT DISTINCT "areas"."id" FROM "areas" LEFT OUTER JOIN "shops" ON "shops"."area_id" = "areas"."id" LEFT OUTER JOIN "accounts" ON "accounts"."shop_id" = "shops"."id" WHERE (areas.latitude BETWEEN 35.3933751337783 AND 35.972302266221696 AND areas.longitude BETWEEN 139.40308602609107 AND 140.11582377390894) ORDER BY distance ASC LIMIT ?  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: distance)

irb(main):002:0> Area.near("tokyo").eager_load(shop: :accounts)
  SQL (0.3ms)  SELECT DISTINCT "areas"."id" FROM "areas" LEFT OUTER JOIN "shops" ON "shops"."area_id" = "areas"."id" LEFT OUTER JOIN "accounts" ON "accounts"."shop_id" = "shops"."id" WHERE (areas.latitude BETWEEN 35.3933751337783 AND 35.972302266221696 AND areas.longitude BETWEEN 139.40308602609107 AND 140.11582377390894) ORDER BY distance ASC LIMIT ?  [["LIMIT", 11]]
Traceback (most recent call last):
ActiveRecord::StatementInvalid (SQLite3::SQLException: no such column: distance)
irb(main):003:0>

Upvotes: 0

Views: 109

Answers (1)

Naveen Honest Raj
Naveen Honest Raj

Reputation: 132

The documentation mentions,

You cannot use the near scope with another scope that provides an includes option because the SELECT clause generated by near will overwrite it (or vice versa).

Instead of using includes to reduce the number of database queries, try using joins with either the :select option or a call to preload. For example:

So, maybe try Area.near("tokyo").eager_load(shop: :accounts) or Area.near("tokyo").joins(shop: :accounts) and see if that works?

Upvotes: 0

Related Questions