76006


## 実現したいこと

Railsで下記のようなクエリを実行し、shopsとareasの値を取得したいです。

[code]

    SELECT shops.*, areas.*
    FROM shops
    LEFT OUTER JOIN areas
    ON shop.id = areas.id
    
[/code]

下記のような結果を取得したいです。

[code]

    <Shop id: 1, shop_name: "est店", created_at: "2021-05-17 01:45:34", updated_at: "2021-05-17 01:45:34", shop_type_id: 1, station_id: 1, area_id: 1, prefectures: "nisi県", municipalities: "officiis市", house_number: nil, building_name: nil, postal_code: nil, created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13">
    
[/code]

## 関連付けは下記のようになっています。

[code]

    models % cat shop.rb 
    class Shop < ApplicationRecord
        belongs_to :shop_type
        belongs_to :area
        belongs_to :station
    end
    
    models % cat area.rb
    class Area < ApplicationRecord
        has_one :shop
    end 
    
[/code]

## マイグレーションは下記のようになっています。

[code]

    migrate % cat 20210511064507_create_shops.rb
    class CreateShops < ActiveRecord::Migration[6.0]
      def change
        create_table :shops do |t|
          t.string :name
    
          t.timestamps
        end
      end
    end
    
    migrate % cat 20210513091441_create_areas.rb
    class CreateAreas < ActiveRecord::Migration[6.0]
      def change
        create_table :areas do |t|
          t.string :prefectures
          t.string :municipalities
          t.string :house_number
          t.string :building_name
          t.integer :postal_code
    
          t.timestamps
        end
      end
    end
    
    migrate % cat 20210514012107_add_foreign_key_to_shop.rb
    class AddForeignKeyToShop < ActiveRecord::Migration[6.0]
      def change
        add_column :shops, :shop_type_id, :integer
        add_column :shops, :station_id, :integer
        add_column :shops, :area_id, :integer
        
        add_foreign_key :shops, :shop_types
        add_foreign_key :shops, :stations
        add_foreign_key :shops, :areas
      end
    end
    
    
[/code]

## 起こっている問題

Railsで`joins`を使用しselectに`shops.*, areas.*`を指定していますがshopsの値のみ取得されareasが取得できません。

[code]

    irb(main):172:0> Shop.first
      Shop Load (0.1ms)  SELECT "shops".* FROM "shops" ORDER BY "shops"."id" ASC LIMIT ?  [["LIMIT", 1]]
    => #<Shop id: 1, shop_name: "est店", created_at: "2021-05-17 01:45:34", updated_at: "2021-05-17 01:45:34", shop_type_id: 1, station_id: 1, area_id: 1>
    
    irb(main):174:0> Area.first
      Area Load (0.1ms)  SELECT "areas".* FROM "areas" ORDER BY "areas"."id" ASC LIMIT ?  [["LIMIT", 1]]
    => #<Area id: 1, prefectures: "eum県", municipalities: "ut市", house_number: nil, building_name: nil, postal_code: nil, created_at: "2021-05-17 01:45:34", updated_at: "2021-05-17 01:45:34">
    
    irb(main):177:0> Shop.joins(:area).select("shops.*, areas.*").first
      Shop Load (0.2ms)  SELECT shops.*, areas.* FROM "shops" INNER JOIN "areas" ON "areas"."id" = "shops"."area_id" ORDER BY "shops"."id" ASC LIMIT ?  [["LIMIT", 1]]
    => #<Shop id: 1, shop_name: "est店", created_at: "2021-05-17 01:45:34", updated_at: "2021-05-17 01:45:34", shop_type_id: 1, station_id: 1, area_id: 1>
    
[/code]

* * *

## 2021/05/27追記

`eager_load`を使うことで期待していた`LEFT OUTER JOIN`を実行できました。

ですが下記のようにshopsのカラムを取得できるのですがareasのカラムは取得できませんでした。

[code]

    Shop.eager_load(:area).first
      SQL (0.1ms)  SELECT "shops"."id" AS t0_r0, "shops"."shop_name" AS t0_r1, "shops"."created_at" AS t0_r2, "shops"."updated_at" AS t0_r3, "shops"."shop_type_id" AS t0_r4, "shops"."station_id" AS t0_r5, "shops"."area_id" AS t0_r6, "areas"."id" AS t1_r0, "areas"."prefectures" AS t1_r1, "areas"."municipalities" AS t1_r2, "areas"."house_number" AS t1_r3, "areas"."building_name" AS t1_r4, "areas"."postal_code" AS t1_r5, "areas"."created_at" AS t1_r6, "areas"."updated_at" AS t1_r7 FROM "shops" LEFT OUTER JOIN "areas" ON "areas"."id" = "shops"."area_id" ORDER BY "shops"."id" ASC LIMIT ?  [["LIMIT", 1]]
    => #<Shop id: 1, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 1, station_id: 1, area_id: 1>
    
[/code]

実行されているSQLをみるとSELECT句にareasのカラムである`"areas"."prefectures"`
などが指定されていますが、返された値にはshopsのカラムしかなく、何が原因でしょうか？

## 2021/05/28追記

ご回答いただいた下記の手順ではエラーになってしまいました。

[code]

    irb(main):001:0> shop = Shop.eager_load(:area)
       (0.4ms)  SELECT sqlite_version(*)
      SQL (0.1ms)  SELECT "shops"."id" AS t0_r0, "shops"."shop_name" AS t0_r1, "shops"."created_at" AS t0_r2, "shops"."updated_at" AS t0_r3, "shops"."shop_type_id" AS t0_r4, "shops"."station_id" AS t0_r5, "shops"."area_id" AS t0_r6, "areas"."id" AS t1_r0, "areas"."prefectures" AS t1_r1, "areas"."municipalities" AS t1_r2, "areas"."house_number" AS t1_r3, "areas"."building_name" AS t1_r4, "areas"."postal_code" AS t1_r5, "areas"."created_at" AS t1_r6, "areas"."updated_at" AS t1_r7 FROM "shops" LEFT OUTER JOIN "areas" ON "areas"."id" = "shops"."area_id" LIMIT ?  [["LIMIT", 11]]
    => #<ActiveRecord::Relation [#<Shop id: 1, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 1, station_id: 1, area_id: 1>, #<Shop id: 2, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 2, station_id: 2, area_id: 2>, #<Shop id: 3, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 3, station_id: 3, area_id: 3>, #<Shop id: 4, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 4, station_id: 4, area_id: 4>, #<Shop id: 5, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 5, station_id: 5, area_id: 5>, #<Shop id: 6, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 6, station_id: 6, area_id: 6>, #<Shop id: 7, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 7, station_id: 7, area_id: 7>, #<Shop id: 8, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 8, station_id: 8, area_id: 8>, #<Shop id: 9, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 9, station_id: 9, area_id: 9>, #<Shop id: 10, shop_name: "facere店", created_at: "2021-05-27 07:49:13", updated_at: "2021-05-27 07:49:13", shop_type_id: 10, station_id: 10, area_id: 10>, ...]>
    irb(main):002:0> shop.area
    Traceback (most recent call last):
            1: from (irb):2
      SQL (0.2ms)  SELECT "shops"."id" AS t0_r0, "shops"."shop_name" AS t0_r1, "shops"."created_at" AS t0_r2, "shops"."updated_at" AS t0_r3, "shops"."shop_type_id" AS t0_r4, "shops"."station_id" AS t0_r5, "shops"."area_id" AS t0_r6, "areas"."id" AS t1_r0, "areas"."prefectures" AS t1_r1, "areas"."municipalities" AS t1_r2, "areas"."house_number" AS t1_r3, "areas"."building_name" AS t1_r4, "areas"."postal_code" AS t1_r5, "areas"."created_at" AS t1_r6, "areas"."updated_at" AS t1_r7 FROM "shops" LEFT OUTER JOIN "areas" ON "areas"."id" = "shops"."area_id" LIMIT ?  [["LIMIT", 11]]
    NoMethodError (undefined method `area' for #<Shop::ActiveRecord_Relation:0x00007fe5fa3c2278>)
    Did you mean?  arel
    irb(main):003:0>
    
    
[/code]

何卒よろしくお願いいたします。


CC BY-SA 4.0
2021-05-18T04:11:13.493
75911
2021-05-28T02:19:01.483
2021-05-28T02:19:01.483
36122
36122
question
ruby-on-rails ruby rails-activerecord
Railsでjoinsを使用しても片方のテーブルからしか値を取得できない原因は何でしょうか？


なさりたいのは `shop = Shop.eager_load(:area)` として `shop.ほにゃらら` を参照したり
`shop.area.ほにゃらら` を参照したりといったことでしょうか……？ `joins` だとクエリキャッシュが生まれないので `eager_load`
にしていますが、ユースケースによっては `includes` でも良いです。

`Shop.joins(:area).select("shops.*, areas.*").first` だと、計算結果としては `Shop`
クラスのインスタンスが返ってくるので、そこには `Area`
クラスのインスタンスメソッドは生えていません。また同名カラムの存在を考えるとそういったようにすべきでもありません。

`belongs_to :area` と設定しているため `Shop#area` メソッドによってその `Shop` インスタンスに紐づいた `Area`
インスタンスを参照できるので、このメソッドを介してアクセスするようにすると良いです。


CC BY-SA 4.0
2021-05-22T04:48:07.013
76006
2021-05-22T04:48:07.013
19110
75911
answer