

## 解決したいこと

Area Model と Shop Modelと Account Modelを結合して、Geocoder
Gemで指定した場所から近い順番に並べ替えた結果を取得したいです。

取得した内容をViewで`each` を使って一覧表示したいです。

### エラーが発生しないパターン

[code]

    irb(main):013:0> Area.eager_load(:shop).near("tokyo")
      SQL (0.9ms)  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]]
    => #<ActiveRecord::Relation [#<Area id: 107, prefectures: "東京都", municipalities: "大田区", house_number: "山王", building_name: nil, postal_code: nil, created_at: "2021-07-11 07:03:13", updated_at: "2021-07-11 07:03:13", latitude: 35.5871768, longitude: 139.7246486967578>, #<Area id: 108, prefectures: "東京都", municipalities: "大田区", house_number: "梅屋敷", building_name: nil, postal_code: nil, created_at: "2021-07-11 07:04:31", updated_at: "2021-07-11 07:04:31", latitude: 35.5669662, longitude: 139.728368>]>
    irb(main):014:0>
    
    
[/code]

### エラーが発生するパターン

これを成功させたいです。  
しかし下記のように`no such column: distance` エラーが発生します。

[code]

    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)
    
[/code]

## 環境

  * Ruby 2.6.3
  * Rails 6.0.3
  * Geocoder Gem: <<https://github.com/alexreisner/geocoder>>

### Model association

[code]

    # app/models/area
    
    has_one :shop
    
[/code]

[code]

    # app/models/shop
    belongs_to :shop_type
    belongs_to :area
    belongs_to :station
    
    has_many :accounts
    has_many :posts
    
[/code]

[code]

    # app/models/account
    
    belongs_to :shop, optional: true
    has_many :post
    
[/code]

## 試したが解決しなかった方法

[code]

    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):144:0> Area.near("tokyo").joins(shop: :accounts)
      Area Load (0.3ms)  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 FROM "areas" INNER JOIN "shops" ON "shops"."area_id" = "areas"."id" INNER 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]]
    => #<ActiveRecord::Relation []>
    irb(main):145:0>
    
[/code]


CC BY-SA 4.0
2021-07-31T03:44:32.610
80497
2021-08-02T14:18:05.807
2021-07-31T05:35:19.750
3060
36122
question
ruby-on-rails ruby
Railsで複数のテーブルを結合した結果をGeocoder Gemを使用して並べ替えたい


`Area.near("tokyo").joins(shop: :accounts)` はクエリ見る限りあってると思われます。

恐らく検索されるAreaから関連するshopとaccountデータがないのだと推測されます

`left_joins` を試してください。

`Area.near("tokyo").left_joins(shop: :accounts)`


CC BY-SA 4.0
2021-08-02T14:18:05.807
80547
2021-08-02T14:18:05.807
298
80497
answer