Edited at

Rails における内部結合、外部結合まとめ


0. はじめに

Qiitaはじめ、さまざまなところでRailsのActiveRecordの内部結合や外部結合に関する記事がありますが、それらがまとまって存在していると良いリファレンスとなるのではないかと思い本記事を作成しました。


また、Rails5で動作確認しておきながら、Rails5から追加されたleft_outer_joinsなどは載せてません。今後、載せていきたいと思います。

group byやサブクエリ(副問い合わせ)に関しては下記もご参照ください。


0-1. RubyとRailsとPostgreSQLのバージョン

$ ruby -v

ruby 2.2.4p230 (2015-12-16 revision 53155) [x86_64-darwin15]
$ rails -v
Rails 5.0.0

=> SELECT version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.3 on x86_64-apple-darwin15.4.0, compiled by Apple LLVM version 7.3.0 (clang-703.0.31), 64-bit


0-2. 今回扱うERD

ERD
説明


女優テーブルがある。
女優テーブルは映画テーブルを持つ。
映画テーブルは、映画に関連するキーワードを入れたタグテーブルを持つ。


0-3. モデル


Actress

class Actress < ApplicationRecord

has_many :movies
end


Movie

class Movie < ApplicationRecord

has_many :tags
belongs_to :actress
end


Tag

class Tag < ApplicationRecord

belongs_to :movie
end


0-4. 今回扱うデータ


actress

SELECT id, name FROM actresses;

id | name
----+------------
1 | 多部未華子
2 | 佐津川愛美
3 | 新垣結衣
4 | 堀北真希
5 | 吉高由里子
6 | 悠城早矢


movies

SELECT id, actress_id, title, year FROM movies;

id | actress_id | title | year
----+------------+---------------------+------
1 | 2 | 蝉しぐれ | 2005
2 | 1 | 夜のピクニック | 2006
3 | 4 | ALWAYS 三丁目の夕日 | 2005
4 | 2 | 忍道-SHINOBIDO- | 2012
5 | 2 | 貞子vs伽椰子 | 2016
6 | 4 | 県庁おもてなし課 | 2013
7 | 5 | 真夏の方程式 | 2013


tags

SELECT id, movie_id, key FROM tags;

id | movie_id | key
----+----------+------------
1 | 1 | 時代劇
2 | 1 | 子役
3 | 3 | 昭和
4 | 5 | ホラー
5 | 7 | ミステリー
6 | 7 | 夏
7 | 6 | 公務員
8 | 6 | 地方活性
9 | 1 | 夏


0-5. 補足


  • arel_tableを使う方法もありますが、今回は省略しました。

  • 今回、左部分除外結合という聞きなれない言葉を使わせていただきました。


    • これは、Visual Representation of SQL Joinsにある「LEFT JOIN EXCLUDING INNER JOIN」の日本語として当てはめたものです。


      • 適切な日本語をご存知の方は教えて下さい。



    • 3と6における左部分除外結合のベン図を載せます。



actressesとmoviesの左部分除外結合
(moviesのないactressesを抽出する)
actressesとmoviesとtagsの左部分除外結合
(tagsのないactressesを抽出する)


※今回のデータの場合、moviesは必ずactress_idをもつし、tagsは必ずmovie_idを持つので、上記のベン図は厳密には違いますが、より一般性を持たせた図にしました。


1. actressesとmoviesの内部結合


1-1. joins(selectなし)を使う


  • actressesのカラムしか持ってきてくれない。

  • 結合先の情報が不要な場合はこれで良い。


Rails

Actress.joins(:movies)



SQL

SELECT

"actresses".*
FROM
"actresses"
INNER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"


1-2. joins(selectあり)を使う


  • 結合先(この場合はmovies)のカラムも取得できる。


Rails

Actress.joins(:movies).select("actresses.*, movies.*").first.title

=> "夜のピクニック"


SQL

SELECT

actresses.*,
movies.*
FROM
"actresses"
INNER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"
ORDER BY "actresses"."id" ASC
LIMIT 1


  • 実行してみます。


execute

Actress.joins(:movies).select("actresses.*, movies.*").each do |actress_movie|

puts "#{actress_movie.name} : #{actress_movie.title}"
end
# =>
Actress Load (1.8ms) SELECT actresses.*, movies.* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"


result

佐津川愛美 : 蝉しぐれ

多部未華子 : 夜のピクニック
堀北真希 : ALWAYS 三丁目の夕日
佐津川愛美 : 忍道-SHINOBIDO-
佐津川愛美 : 貞子vs伽椰子
堀北真希 : 県庁おもてなし課
吉高由里子 : 真夏の方程式


  • カラム名がかぶる場合は、select内で名前付けをする。


Rails

Actress.joins(:movies).select("actresses.*, movies.id AS movie_id").last.id

# =>
Actress Load (0.9ms) SELECT actresses.*, movies.id AS movie_id FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" ORDER BY "actresses"."id" DESC LIMIT 1
=> 5 # actressのid

Actress.joins(:movies).select("actresses.*, movies.id AS movie_id").last.movie_id
# =>
Actress Load (0.6ms) SELECT actresses.*, movies.id AS movie_id FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" ORDER BY "actresses"."id" DESC LIMIT 1
=> 7 # movieのid



1-3. joinsに関する補足


  • mergeを使うと結合先のモデルのscopeを使うことができる。


execute

# 実用的には普通にモデル内に書けばいい。

Movie.class_eval { scope :year_2013, -> { where(year: 2013) } }
=> :year_2013
# ベタに書くと、Actress.joins(:movies).where(movies: { year: 2013 })
Actress.joins(:movies).merge(Movie.year_2013)
# =>
Actress Load (0.5ms) SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."year" = $1 [["year", 2013]]
=> [#<Actress:0x007fed0bc19eb8
id: 4,
name: "堀北真希",
created_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00,
updated_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00>,
#<Actress:0x007fed0bba9820
id: 5,
name: "吉高由里子",
created_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00,
updated_at: Sat, 03 Sep 2016 06:51:29 UTC +00:00>]


  • 複数個selectしてcountをするとSQLが壊れる・・・。注意!


    • selectしない場合はOK。




select使わないときやselect内が1個の時はOK

Actress.joins(:movies).count

# =>
(2.5ms) SELECT COUNT(*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7
Actress.joins(:movies).select("actresses.*").count
# =>
(0.5ms) SELECT COUNT(actresses.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7
Actress.joins(:movies).select("movies.*").count
# =>
(0.5ms) SELECT COUNT(movies.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7


selectしてcountするとNG

Actress.joins(:movies).select("actresses.*, movies.*").count

# =>
SELECT COUNT(actresses.*, movies.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: function count(actresses, movies) does not exist
LINE 1: SELECT COUNT(actresses.*, movies.*) FROM "actresses" INNER J...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
: SELECT COUNT(actresses.*, movies.*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
from /Users/user_name/.rbenv/versions/2.2.4/lib/ruby/gems/2.2.0/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `async_exec'



  • sizelengthを使うと良い。


selectしてsizeするとacctressの数が取れ、SQLもCOUNT(*)となっている

Actress.joins(:movies).select("actresses.*, movies.*").size

# =>
(0.5ms) SELECT COUNT(*) FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7


selectしてlengthするとacctressの数が取るが、SQLはactresses.*となっている

Actress.joins(:movies).select("actresses.*, movies.*").length

# =>
Actress Load (0.5ms) SELECT actresses.*, movies.* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
=> 7


  • 普段あまり意識しないcountsizelengthだが、SQLが絡むと挙動が違うので注意が必要。


1-4. joins + preloadを使う


Rails

Actress.joins(:movies).preload(:movies)



SQL

  Actress Load (0.4ms)  SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"

Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (2, 1, 4, 5)


  • 実行する際にはいささか注意が必要。


    • 以下のような場合、distinctをつける。(uniqでもいいが、Rails5では、DEPRECATION WARNINGが出る。5.1で削除予定。)




execute

Actress.joins(:movies).preload(:movies).distinct.each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
Actress Load (0.5ms) SELECT DISTINCT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (5, 2, 4, 1)


result

吉高由里子:

真夏の方程式
佐津川愛美:
蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
堀北真希:
ALWAYS 三丁目の夕日
県庁おもてなし課
多部未華子:
夜のピクニック


  • distinctをつけないと・・・。


execute

Actress.joins(:movies).preload(:movies).each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
Actress Load (0.5ms) SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (2, 1, 4, 5)


result

佐津川愛美:

蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
多部未華子:
夜のピクニック
堀北真希:
ALWAYS 三丁目の夕日
県庁おもてなし課
佐津川愛美:
Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" = $1 [["actress_id", 2]]
蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
佐津川愛美:
Movie Load (0.3ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" = $1 [["actress_id", 2]]
蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
堀北真希:
Movie Load (0.3ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" = $1 [["actress_id", 4]]
ALWAYS 三丁目の夕日
県庁おもてなし課
吉高由里子:
真夏の方程式


  • joinsを使っているからか、where句やmergeによる結合先のscopeが使える。


Rails

Actress.joins(:movies).preload(:movies).where(movies: { year: 2013 })

Actress.joins(:movies).preload(:movies).merge(Movie.year_2013)
Actress.joins(:movies).merge(Movie.year_2013).preload(:movies)


  • ただし、結合しているわけではないので使い方に注意が必要。


    • 例えばこんな感じに使うと・・・。




execute

Actress.joins(:movies).merge(Movie.year_2013).preload(:movies).each do |actress|

puts "#{actress.name}"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
Actress Load (0.3ms) SELECT "actresses".* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."year" = $1 [["year", 2013]]
Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (4, 5)


  • せっかくmoviesの条件を絞ったのに出てきてしまう。(当たり前だけど)


result

堀北真希

ALWAYS 三丁目の夕日
県庁おもてなし課
吉高由里子
真夏の方程式


1-5. joins + eager_loadを使う



  • 2-2. eager_loadを使うで後述するように単にeager_loadを使うと外部結合になるが、joinsと組み合わせると内部結合になる。

  • 結合先も取得してくれ、actressごとにmoviesを取得できる。


  • 1-6. joins + includesを使うと同じ挙動


Rails

Actress.joins(:movies).eager_load(:movies)



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
"actresses"
INNER JOIN
"movies" ON "movies"."actress_id" = "actresses"."id"


  • 実行してみます。


execute

Actress.joins(:movies).eager_load(:movies).each do |actress|

puts "#{actress.name} "
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
SQL (0.5ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"


result

佐津川愛美

蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
多部未華子
夜のピクニック
堀北真希
ALWAYS 三丁目の夕日
県庁おもてなし課
吉高由里子
真夏の方程式


  • ただし、eachブロック内でcountなどの集合関数を使うと、旨味が減る。


execute

Actress.joins(:movies).eager_load(:movies).each do |actress|

puts "#{actress.name}: #{actress.movies.count}"
end
# =>
SQL (0.6ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
(2.0ms) SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1 [["actress_id", 2]]
佐津川愛美: 3
(0.4ms) SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1 [["actress_id", 1]]
多部未華子: 1
(0.3ms) SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1 [["actress_id", 4]]
堀北真希: 2
(0.3ms) SELECT COUNT(*) FROM "movies" WHERE "movies"."actress_id" = $1 [["actress_id", 5]]
吉高由里子: 1



  • countを使うのではなく、sizelengthを使うと良い。


execute

Actress.joins(:movies).eager_load(:movies).each do |actress|

puts "#{actress.name}: #{actress.movies.size}"
end
# =>
SQL (0.6ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"
佐津川愛美: 3
多部未華子: 1
堀北真希: 2
吉高由里子: 1


1-6. joins + includesを使う


Rails

Actress.joins(:movies).includes(:movies)



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
"actresses"
INNER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"


  • 実行してみます。


execute

Actress.joins(:movies).includes(:movies).each do |actress|

puts "#{actress.name}"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
SQL (0.6ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"


result

佐津川愛美

蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
多部未華子
夜のピクニック
堀北真希
ALWAYS 三丁目の夕日
県庁おもてなし課
吉高由里子
真夏の方程式


  • where句やmergeによる結合先のscopeが使えます。


    • 以下の3つは同じ挙動。




Rails

Actress.joins(:movies).includes(:movies).where(movies: { year: 2013 })

Actress.joins(:movies).includes(:movies).merge(Movie.year_2013)
Actress.joins(:movies).merge(Movie.year_2013).includes(:movies)


2. actressesとmoviesの左外部結合


2-1. joinsを使う


  • joinsの中にSQLを書く必要がある。

  • actressesのカラムだけ必要な場合は、selectは不要。


execute

Actress.joins("LEFT OUTER JOIN movies ON actresses.id = movies.actress_id")

# =>
Actress Load (0.4ms) SELECT "actresses".* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id


  • moviesのカラムが必要な場合は、selectが必要。


execute

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).select("movies.*")
# =>
Actress Load (0.4ms) SELECT movies.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id


  • actressesとmoviesの両方のカラムが必要な場合は、ふたつ書く。

  • カラム名がかぶる場合は、1-2に同じ。

  • 順番も変わらないので、左外部結合として一般に想像できる結果が返ってくる。


execute

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).select("actresses.*, movies.*").each do |actress_movie|
puts "#{actress_movie.name}: #{actress_movie.title}"
end
# =>
Actress Load (0.5ms) SELECT actresses.*, movies.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id


result

佐津川愛美: 蝉しぐれ

多部未華子: 夜のピクニック
堀北真希: ALWAYS 三丁目の夕日
佐津川愛美: 忍道-SHINOBIDO-
佐津川愛美: 貞子vs伽椰子
堀北真希: 県庁おもてなし課
吉高由里子: 真夏の方程式
悠城早矢:
新垣結衣:


2-2. eager_loadを使う


  • 結合先も取得してくれる。

  • あくまでactressesがベースとなっているので、発行するSQLとしては純粋な左外部結合だが、2-1. joinsを使うのように外部結合先の値を.first.titleのように取り出すことができない。


Rails

Actress.eager_load(:movies)



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
"actresses"
LEFT OUTER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"


  • eager_loadで取得した後にeachブロック内でactress.moviesを使うときにSQLクエリを発行せずにmoviesを取得できる。


execute

Actress.eager_load(:movies).each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
SQL (1.3ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"


result

佐津川愛美:

蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
多部未華子:
夜のピクニック
堀北真希:
ALWAYS 三丁目の夕日
県庁おもてなし課
吉高由里子:
真夏の方程式
悠城早矢:
新垣結衣:


  • firstとかを使うと旨味が出ない(こういう使い方を想定していないのか)


    • 2回クエリが発行されてしまう。




execute

Actress.eager_load(:movies).first.movies

# =>
SQL (0.5ms) SELECT DISTINCT "actresses"."id", "actresses"."id" AS alias_0 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" ORDER BY "actresses"."id" ASC LIMIT $1 [["LIMIT", 1]]
SQL (0.5ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "actresses"."id" = 1 ORDER BY "actresses"."id" ASC


2-3. preloadを使う


  • actressesとmoviesに対して2回クエリが発行される。

  • SQL的には左外部結合ではないが、情報としては同等のものが得られる。


  • 2-4. includes(referencesなし)と同じ挙動。


Rails

Actress.preload(:movies)



SQL

  Actress Load (0.3ms)  SELECT "actresses".* FROM "actresses"

Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)


execute

Actress.preload(:movies).each do |actress|

puts "#{actress.name} "
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
Actress Load (0.4ms) SELECT "actresses".* FROM "actresses"
Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)


result

多部未華子

夜のピクニック
佐津川愛美
蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
新垣結衣
堀北真希
ALWAYS 三丁目の夕日
県庁おもてなし課
吉高由里子
真夏の方程式
悠城早矢


ruby.execute

Actress.preload(:movies).where(movies: { year: 2013 }).each do |actress|

puts "#{actress.name} "
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "movies"
LINE 1: SELECT "actresses".* FROM "actresses" WHERE "movies"."year" ...
^
: SELECT "actresses".* FROM "actresses" WHERE "movies"."year" = $1
from /Users/user_name/.rbenv/versions/2.2.4/lib/ruby/gems/2.2.0/gems/activerecord-5.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:657:in `prepare'


2-4. includes(referencesなし)を使う


  • SQLクエリが2回走る。

  • SQL的には外部結合ではないが、情報としては同等のものが得られる。


  • 2-3. preloadを使うと同じ挙動


Rails

Actress.includes(:movies)



SQL

  Actress Load (0.3ms)  SELECT "actresses".* FROM "actresses"

Movie Load (0.4ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)


  • 実行してみます。


execute

Actress.includes(:movies).each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
Actress Load (0.5ms) SELECT "actresses".* FROM "actresses"
Movie Load (0.6ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)


result

2−3と同じ。



2-5. includes(referencesあり)を使う


  • includesした先も持ってきてくれる。

  • 結果、SQLクエリは1回で済む。


  • 2-2. eager_loadと同じ挙動。


Rails

Actress.includes(:movies).references(:movies)



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
"actresses"
LEFT OUTER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"


  • 実行してみる


result

Actress.includes(:movies).references(:movies).each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
SQL (0.6ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id"


result

佐津川愛美:

蝉しぐれ
忍道-SHINOBIDO-
貞子vs伽椰子
多部未華子:
夜のピクニック
堀北真希:
ALWAYS 三丁目の夕日
県庁おもてなし課
吉高由里子:
真夏の方程式
悠城早矢:
新垣結衣:


  • includes先にwhere句をつけるとreferencesが自動的に付与され、クエリは1回で済む。


execute

Actress.includes(:movies).where(movies: { year: 2013 }).each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
SQL (1.1ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."year" = $1 [["year", 2013]]


result

堀北真希:

県庁おもてなし課
吉高由里子:
真夏の方程式


1, 2のまとめ

処理
joins(:movies)
joins(:movies)
.preload(:movies)
joins(:movies)
.eager_load(:movies)
joins(:movies)
.includes(:movies)
preload(:movies)
eager_load(:movies)
includes(:movies)
includes(:movies)
.references(:movies)

結合
内部結合
SELECT2回
内部結合
内部結合
SELECT2回
左外部結合
SELECT2回
左外部結合

情報
内部結合
内部結合
内部結合
内部結合
左外部結合
左外部結合
左外部結合
左外部結合

moviesの取得
×
selectを使う必要あり。






その他

where句は使える。
扱いにやや注意。
joins(:movies).
includes(:movies)と同じ。
joins(:movies).
eager_load(:movies)と同じ。
includesと同じ。
但し、where句は使えない。

preloadと同じ。
where句が使える。
eager_loadと同じ。

※情報のところは少し語弊があると思います。SELECT2回のものは厳密には左外部結合ではないですが、ActiveRecordで得られる情報としては同じという意味で記載しました。


3. actressesとmoviesの左除外結合(moviesのないactressesを抽出)



(再掲)


3-1. joinsを使う


  • 左外部結合したあとwhere(movies: { actress_id: nil })で絞る。


Rails

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).where(movies: { actress_id: nil })


SQL

SELECT

actresses.*,
movies.*
FROM
"actresses"
LEFT OUTER JOIN
movies
ON
actresses.id = movies.actress_id
WHERE "movies"."actress_id" IS NULL


execute

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id"
).where(movies: { actress_id: nil }).select("actresses.*, movies.*").each do |actress_movie|
puts "#{actress_movie.name}: #{actress_movie.title}"
end
# =>
Actress Load (0.5ms) SELECT actresses.*, movies.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id WHERE "movies"."actress_id" IS NULL


result

新垣結衣:

悠城早矢:


3-2. eager_loadを使う


Rails

 Actress.eager_load(:movies).where(movies: { actress_id: nil })



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5
FROM
"actresses"
LEFT OUTER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"
WHERE
"movies"."actress_id" IS NULL


  • 実行してみます。


execute

Actress.eager_load(:movies).where(movies: { actress_id: nil }).each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
SQL (0.6ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."actress_id" IS NULL


result

新垣結衣:

悠城早矢:


3-3. includesを使う


  • where句を使うことになるので、結果的にeager_loadと同じになる。

  • 実行結果のみ記す。


execute

Actress.includes(:movies).where(movies: { actress_id: nil }).each do |actress|

puts "#{actress.name}:"
actress.movies.each { |movie| puts " #{movie.title}" }
end
# =>
SQL (0.5ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" WHERE "movies"."actress_id" IS NULL


result

新垣結衣:

悠城早矢:


4. ActressとMovieとTagの内部結合


4-1. joinsを使う


Rails

Actress.joins(movies: :tags)



SQL

SELECT

"actresses".*
FROM
"actresses"
INNER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"
INNER JOIN
"tags"
ON
"tags"."movie_id" = "movies"."id"


  • selectをつけて実行してみます。


execute

Actress.joins(movies: :tags).select("actresses.*, movies.*, tags.*").each do |actress_movie_tag|

puts "#{actress_movie_tag.name} : #{actress_movie_tag.title} : #{actress_movie_tag.key}"
end
# =>
Actress Load (0.6ms) SELECT actresses.*, movies.*, tags.* FROM "actresses" INNER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" INNER JOIN "tags" ON "tags"."movie_id" = "movies"."id"


result

佐津川愛美 : 蝉しぐれ : 時代劇

佐津川愛美 : 蝉しぐれ : 子役
堀北真希 : ALWAYS 三丁目の夕日 : 昭和
佐津川愛美 : 貞子vs伽椰子 : ホラー
吉高由里子 : 真夏の方程式 : ミステリー
吉高由里子 : 真夏の方程式 : 夏
堀北真希 : 県庁おもてなし課 : 公務員
堀北真希 : 県庁おもてなし課 : 地方活性
佐津川愛美 : 蝉しぐれ : 夏


5. ActressとMovieとTagの左外部結合


5-1. joinsを使う


Rails

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
LEFT OUTER JOIN tags ON movies.id = tags.movie_id"

)


SQL

SELECT

"actresses".*
FROM
"actresses"
LEFT OUTER JOIN
movies
ON
actresses.id = movies.actress_id
LEFT OUTER JOIN
tags
ON
movies.id = tags.movie_id


  • selectをつけて実行してみます。


execute

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
LEFT OUTER JOIN tags ON movies.id = tags.movie_id"

).select("actresses.*, movies.*, tags.key AS key").each do |act_mov_tag|
puts "#{act_mov_tag.name} : #{act_mov_tag.title} : #{act_mov_tag.key}"
end
# =>
Actress Load (0.7ms) SELECT actresses.*, movies.*, tags.key AS key FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
LEFT OUTER JOIN tags ON movies.id = tags.movie_id


result

佐津川愛美 : 蝉しぐれ : 時代劇

佐津川愛美 : 蝉しぐれ : 子役
堀北真希 : ALWAYS 三丁目の夕日 : 昭和
佐津川愛美 : 貞子vs伽椰子 : ホラー
吉高由里子 : 真夏の方程式 : ミステリー
吉高由里子 : 真夏の方程式 : 夏
堀北真希 : 県庁おもてなし課 : 公務員
堀北真希 : 県庁おもてなし課 : 地方活性
佐津川愛美 : 蝉しぐれ : 夏
多部未華子 : 夜のピクニック :
佐津川愛美 : 忍道-SHINOBIDO- :
悠城早矢 : :
新垣結衣 : :


5-2. eager_loadを使う


Rails

Actress.eager_load(movies: :tags)



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5,
"tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4
FROM
"actresses"
LEFT OUTER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"
LEFT OUTER JOIN
"tags"
ON
"tags"."movie_id" = "movies"."id"


  • 多少見やすいようにprintして、実行してみます。


execute

Actress.eager_load(movies: :tags).each do |actress|

puts "#{actress.name}:"
actress.movies.each do |movie|
print " #{movie.title}: "
movie.tags.each { |tag| print "#{tag.key} " }
puts
end
end
# =>
SQL (1.0ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" LEFT OUTER JOIN "tags" ON "tags"."movie_id" = "movies"."id"


result

佐津川愛美:

蝉しぐれ: 時代劇 子役 夏
貞子vs伽椰子: ホラー
忍道-SHINOBIDO-:
堀北真希:
ALWAYS 三丁目の夕日: 昭和
県庁おもてなし課: 公務員 地方活性
吉高由里子:
真夏の方程式: ミステリー 夏
多部未華子:
夜のピクニック:
悠城早矢:
新垣結衣:


5-3. includes(referencesなし)を使う


  • 3回クエリが走る。

  • 実行結果のみ記す。


execute

Actress.includes(movies: :tags).each do |actress|

puts "#{actress.name}:"
actress.movies.each do |movie|
print " #{movie.title}: "
movie.tags.each { |tag| print "#{tag.key} " }
puts
end
end
# =>
Actress Load (0.4ms) SELECT "actresses".* FROM "actresses"
Movie Load (0.7ms) SELECT "movies".* FROM "movies" WHERE "movies"."actress_id" IN (1, 2, 3, 4, 5, 6)
Tag Load (0.4ms) SELECT "tags".* FROM "tags" WHERE "tags"."movie_id" IN (1, 2, 3, 4, 5, 6, 7)


result

5-2に同じ。



5-4. includes(referencesあり)を使う


  • 結合先がmoviesとtagsの2種類あるのでreferencesの引数は3パターン考えられるがどれもSQLクエリは同じだった。


moviesとtags

Actress.includes(movies: :tags).references(:movies, :tags)



moviesのみ

Actress.includes(movies: :tags).references(:movies)



tagsのみ

Actress.includes(movies: :tags).references(:tags)



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5,
"tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4
FROM
"actresses"
LEFT OUTER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"
LEFT OUTER JOIN
"tags"
ON
"tags"."movie_id" = "movies"."id"


result

5-2、5-3に同じ。



6. actressesとmoviesとtagsの左部分除外結合(tagsのないactressesを抽出する)



(再掲)


6-1. joinsを使う


Rails

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
LEFT OUTER JOIN tags ON movies.id = tags.movie_id"

).where(movies: { tags: { movie_id: nil } })


SQL

SELECT

"actresses".*
FROM
"actresses"
LEFT OUTER JOIN
movies
ON
actresses.id = movies.actress_id
LEFT OUTER JOIN
tags
ON
movies.id = tags.movie_id
WHERE "tags"."movie_id" IS NULL


  • selectをつけて実行してみます。


execute

Actress.joins(

"LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
LEFT OUTER JOIN tags ON movies.id = tags.movie_id"

).where(movies: { tags: { movie_id: nil } }).select("actresses.*, movies.*, tags.*").each do |amt|
puts "#{amt.name} : #{amt.title} : #{amt.key}"
end
# =>
Actress Load (0.6ms) SELECT actresses.*, movies.*, tags.* FROM "actresses" LEFT OUTER JOIN movies ON actresses.id = movies.actress_id
LEFT OUTER JOIN tags ON movies.id = tags.movie_id WHERE "tags"."movie_id" IS NULL


result

多部未華子 : 夜のピクニック :

佐津川愛美 : 忍道-SHINOBIDO- :
悠城早矢 : :
新垣結衣 : :


6-2. eager_loadを使う


Rails

Actress.eager_load(movies: :tags).where(tags: { movie_id: nil })



SQL

SELECT

"actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3,
"movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5,
"tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4
FROM
"actresses"
LEFT OUTER JOIN
"movies"
ON
"movies"."actress_id" = "actresses"."id"
LEFT OUTER JOIN
"tags"
ON
"tags"."movie_id" = "movies"."id"
WHERE "tags"."movie_id" IS NULL


  • 実行してみます。


execute

Actress.eager_load(movies: :tags).where(tags: { movie_id: nil }).each do |actress|

puts "#{actress.name}:"
actress.movies.each do |movie|
print " #{movie.title}:"
movie.tags.each { |tag| print "#{tag.key}" }
puts
end
end
# =>
SQL (0.6ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" LEFT OUTER JOIN "tags" ON "tags"."movie_id" = "movies"."id" WHERE "tags"."movie_id" IS NULL


result

多部未華子:

夜のピクニック:
佐津川愛美:
忍道-SHINOBIDO-:
悠城早矢:
新垣結衣:


6-3. includesを使う


execute

Actress.includes(movies: :tags).where(tags: { movie_id: nil }).each do |actress|

puts "#{actress.name}:"
actress.movies.each do |movie|
print " #{movie.title}:"
movie.tags.each { |tag| print "#{tag.key}" }
puts
end
end
# =>
SQL (0.9ms) SELECT "actresses"."id" AS t0_r0, "actresses"."name" AS t0_r1, "actresses"."created_at" AS t0_r2, "actresses"."updated_at" AS t0_r3, "movies"."id" AS t1_r0, "movies"."actress_id" AS t1_r1, "movies"."title" AS t1_r2, "movies"."year" AS t1_r3, "movies"."created_at" AS t1_r4, "movies"."updated_at" AS t1_r5, "tags"."id" AS t2_r0, "tags"."movie_id" AS t2_r1, "tags"."key" AS t2_r2, "tags"."created_at" AS t2_r3, "tags"."updated_at" AS t2_r4 FROM "actresses" LEFT OUTER JOIN "movies" ON "movies"."actress_id" = "actresses"."id" LEFT OUTER JOIN "tags" ON "tags"."movie_id" = "movies"."id" WHERE "tags"."movie_id" IS NULL


result

6-2と同じ。



最後に


  • Railsの経験の浅い若輩者ですので、間違い等ございましたら、優しく教えて下さい。

  • 本記事を書くにあたり、幾つかの過去の記事を参考にさせていただきました。参考をご覧ください。とても勉強になりました。ありがとうございました。

  • 今回は、1988年生まれの女優さんを題材にさせていただきました。ここに感謝の意を表します。ありがとうございました。


参考


  1. Visual Representation of SQL Joins

  2. ActiveRecordのjoinsとpreloadとincludesとeager_loadの違い

  3. has_manyをeager_load/includesするときの注意点

  4. preloadとeager_loadで1000000億倍早くなったはなし

  5. Rails と テーブル結合

  6. INNER JOIN で eager loading

  7. 佐津川愛美 | オフィシャルサイト

  8. 堀北真希|Sweet Power

  9. Mikako Tabe official web site

  10. 吉高 由里子 - オフィシャル ウェブサイト