🎯

【Rails/SQL】INNER/LEFT/RIGHT JOINとjoins/includes/preload/eager_load

2024/05/26に公開

Rails

SQL

tech

概要

SQLのJOIN系の挙動と、Railsのjoins/includes/preload/eager_loadの挙動について、実験を行い把握する

環境

MacBook Pro 2023
Rails 7.0.8.3
MySQL 8.0
Docker
環境構築はこちらの記事をご覧ください。

データの概要

著者（Author）が複数の本（Book）を持つ。ユーザー（User）は複数のレビュー（Review）を投稿できる。
データの特徴的な点
- ID: 3のAuthorはBookを持たない
- ID: 1, 2のAuthorは2つのBookを持つ

Railsにてこの環境を構築するまで

migrationファイルを作成

db/migrate/xxxxxxxx_create_authors.rb

class CreateAuthors < ActiveRecord::Migration[7.0]
  def change
    create_table :authors do |t|
      t.string :name, null: false

      t.timestamps
    end
  end
end

db/migrate/xxxxxxxx_create_books.rb

class CreateBooks < ActiveRecord::Migration[7.0]
  def change
    create_table :books do |t|
      t.string :title, null: false
      t.references :author, null: false, foreign_key: true

      t.timestamps
    end
  end
end

db/migrate/xxxxxxxx_create_users.rb

class CreateUsers < ActiveRecord::Migration[7.0]
  def change
    create_table :users do |t|
      t.string :nickname, null: false

      t.timestamps
    end
  end
end

db/migrate/xxxxxxxx_create_reviews.rb

class CreateReviews < ActiveRecord::Migration[7.0]
  def change
    create_table :reviews do |t|
      t.integer :rating, null: false, default: 0
      t.text :body, null: true
      t.references :user, null: false, foreign_key: true
      t.references :book, null: false, foreign_key: true

      t.timestamps
    end
  end
end

modelファイルを作成

app/models/author.rb

class Author < ApplicationRecord
  has_many :books, dependent: :destroy
end

app/models/book.rb

class Book < ApplicationRecord
  belongs_to :author
  has_many :reviews, dependent: :destroy
  has_many :users, through: :reviews
end

app/models/user.rb

class User < ApplicationRecord
  has_many :reviews, dependent: :destroy
  has_many :books, through: :reviews
end

app/models/review.rb

class Review < ApplicationRecord
  belongs_to :user
  belongs_to :book
end

テストデータを作成するseedファイルを作成

db/seeds.rb

# データをリセットする場合は以下のコマンドを実行
# docker compose exec web rails db:drop db:create db:migrate db:seed

# Authorは4人で確定
BOOKS_COUNT = 5
USERS_COUNT = 5
REVIEWS_COUNT = 5

AUTHOR_NAMES = %w[A1-芥川一郎 A2-太宰二郎 A3-川端三郎 A4-夏目四郎].freeze
BOOKS_SUFFIX = %w[紀行 小説 評論 随筆].freeze
USER_LAST_NAMES = %w[山田 田中 鈴木 佐藤 高橋 伊藤 渡辺 小林 加藤 吉田].freeze
USER_FIRST_NAMES = %w[タロウ ハナコ ジロウ マサオ ユキコ シンジ ユウキ ナオミ ヒロシ ミキ].freeze
REVIEW_COMMENTS_1 = %w[これは 本当に この本は 今までで一番 とても かなり 自分史上 最高に].freeze
REVIEW_COMMENTS_2 = %w[面白かった つまらなかった 期待外れだった 期待通りだった 感動した 驚いた 予想外だった 泣いた 笑った びっくりした].freeze

AUTHOR_NAMES.each do |name|
  Author.create!(name: name)
end

authors = Author.all
BOOKS_COUNT.times do |i|
  author = authors.sample
  Book.create!(
    author: author,
    title: "B#{i + 1}-#{BOOKS_SUFFIX.sample}-by-A#{author.id}",
  )
end

USERS_COUNT.times do |i|
  name = "U#{i + 1}-#{USER_LAST_NAMES.sample} #{USER_FIRST_NAMES.sample}"
  User.create!(nickname: name)
end

users = User.all
books = Book.all
REVIEWS_COUNT.times do |i|
  user = users.sample
  book = books.sample

  rating = rand(1..5)
  comment = "R#{i + 1}-#{REVIEW_COMMENTS_1.sample}#{REVIEW_COMMENTS_2.sample}-by-U#{user.id}-for-B#{book.id}"
  Review.create!(
    user: user,
    book: book,
    rating: rating,
    body: comment,
  )
end

SQLの挙動

INNER JOIN（JOIN）

Bookを持たないAuthor（ID: 3）は表示されない。
複数のBookを持つAuthor（ID: 1, 2）は複数行表示される。

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors INNER JOIN books ON authors.id = books.author_id;
+----+-----------------+----+-----------------+-----------+
| id | name            | id | title           | author_id |
+----+-----------------+----+-----------------+-----------+
|  1 | A1-芥川一郎     |  1 | B1-随筆-by-A1   |         1 |
|  2 | A2-太宰二郎     |  2 | B2-紀行-by-A2   |         2 |
|  2 | A2-太宰二郎     |  3 | B3-評論-by-A2   |         2 |
|  1 | A1-芥川一郎     |  4 | B4-随筆-by-A1   |         1 |
|  4 | A4-夏目四郎     |  5 | B5-小説-by-A4   |         4 |
+----+-----------------+----+-----------------+-----------+
5 rows in set (0.007 sec)

INNER JOINとJOINは同じ、Railsのjoinsも同じ

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors INNER JOIN books ON authors.id = books.author_id;
+----+-----------------+----+-----------------+-----------+
| id | name            | id | title           | author_id |
+----+-----------------+----+-----------------+-----------+
|  1 | A1-芥川一郎     |  1 | B1-随筆-by-A1   |         1 |
|  2 | A2-太宰二郎     |  2 | B2-紀行-by-A2   |         2 |
|  2 | A2-太宰二郎     |  3 | B3-評論-by-A2   |         2 |
|  1 | A1-芥川一郎     |  4 | B4-随筆-by-A1   |         1 |
|  4 | A4-夏目四郎     |  5 | B5-小説-by-A4   |         4 |
+----+-----------------+----+-----------------+-----------+
5 rows in set (0.007 sec)

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors JOIN books ON authors.id = books.author_id;
+----+-----------------+----+-----------------+-----------+
| id | name            | id | title           | author_id |
+----+-----------------+----+-----------------+-----------+
|  1 | A1-芥川一郎     |  1 | B1-随筆-by-A1   |         1 |
|  2 | A2-太宰二郎     |  2 | B2-紀行-by-A2   |         2 |
|  2 | A2-太宰二郎     |  3 | B3-評論-by-A2   |         2 |
|  1 | A1-芥川一郎     |  4 | B4-随筆-by-A1   |         1 |
|  4 | A4-夏目四郎     |  5 | B5-小説-by-A4   |         4 |
+----+-----------------+----+-----------------+-----------+
5 rows in set (0.002 sec)

rails

irb(main):001> Author.joins(:books).all
  Author Load (0.5ms)  SELECT `authors`.* FROM `authors` INNER JOIN `books` ON `books`.`author_id` = `authors`.`id`
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
5 rows in set

LEFT OUTER JOIN（LEFT JOIN）

Authorを起点に全員が表示される
- Bookを持たないAuthor（ID: 3）も表示される
複数のBookを持つAuthor（ID: 1, 2）はその分だけ複数行表示される

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors LEFT OUTER JOIN books ON authors.id = books.author_id;
+----+-----------------+------+-----------------+-----------+
| id | name            | id   | title           | author_id |
+----+-----------------+------+-----------------+-----------+
|  1 | A1-芥川一郎     |    4 | B4-随筆-by-A1   |         1 |
|  1 | A1-芥川一郎     |    1 | B1-随筆-by-A1   |         1 |
|  2 | A2-太宰二郎     |    3 | B3-評論-by-A2   |         2 |
|  2 | A2-太宰二郎     |    2 | B2-紀行-by-A2   |         2 |
|  3 | A3-川端三郎     | NULL | NULL            |      NULL |
|  4 | A4-夏目四郎     |    5 | B5-小説-by-A4   |         4 |
+----+-----------------+------+-----------------+-----------+
6 rows in set (0.004 sec)

LEFT OUTER JOINとLEFT JOINは同じ

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors LEFT OUTER JOIN books ON authors.id = books.author_id;
+----+-----------------+------+-----------------+-----------+
| id | name            | id   | title           | author_id |
+----+-----------------+------+-----------------+-----------+
|  1 | A1-芥川一郎     |    4 | B4-随筆-by-A1   |         1 |
|  1 | A1-芥川一郎     |    1 | B1-随筆-by-A1   |         1 |
|  2 | A2-太宰二郎     |    3 | B3-評論-by-A2   |         2 |
|  2 | A2-太宰二郎     |    2 | B2-紀行-by-A2   |         2 |
|  3 | A3-川端三郎     | NULL | NULL            |      NULL |
|  4 | A4-夏目四郎     |    5 | B5-小説-by-A4   |         4 |
+----+-----------------+------+-----------------+-----------+
6 rows in set (0.004 sec)

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors LEFT JOIN books ON authors.id = books.author_id;
+----+-----------------+------+-----------------+-----------+
| id | name            | id   | title           | author_id |
+----+-----------------+------+-----------------+-----------+
|  1 | A1-芥川一郎     |    4 | B4-随筆-by-A1   |         1 |
|  1 | A1-芥川一郎     |    1 | B1-随筆-by-A1   |         1 |
|  2 | A2-太宰二郎     |    3 | B3-評論-by-A2   |         2 |
|  2 | A2-太宰二郎     |    2 | B2-紀行-by-A2   |         2 |
|  3 | A3-川端三郎     | NULL | NULL            |      NULL |
|  4 | A4-夏目四郎     |    5 | B5-小説-by-A4   |         4 |
+----+-----------------+------+-----------------+-----------+
6 rows in set (0.010 sec)

RIGHT OUTER JOIN（RIGHT JOIN）

Bookが起点になる
どのBookとも紐づかないAuthor（ID: 3）は表示されない

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors RIGHT OUTER JOIN books ON authors.id = books.author_id;
+------+-----------------+----+-----------------+-----------+
| id   | name            | id | title           | author_id |
+------+-----------------+----+-----------------+-----------+
|    1 | A1-芥川一郎     |  1 | B1-随筆-by-A1   |         1 |
|    2 | A2-太宰二郎     |  2 | B2-紀行-by-A2   |         2 |
|    2 | A2-太宰二郎     |  3 | B3-評論-by-A2   |         2 |
|    1 | A1-芥川一郎     |  4 | B4-随筆-by-A1   |         1 |
|    4 | A4-夏目四郎     |  5 | B5-小説-by-A4   |         4 |
+------+-----------------+----+-----------------+-----------+
5 rows in set (0.009 sec)

RIGHT OUTER JOINとRIGHT JOINは同じ

MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors RIGHT OUTER JOIN books ON authors.id = books.author_id;
+------+-----------------+----+-----------------+-----------+
| id   | name            | id | title           | author_id |
+------+-----------------+----+-----------------+-----------+
|    1 | A1-芥川一郎     |  1 | B1-随筆-by-A1   |         1 |
|    2 | A2-太宰二郎     |  2 | B2-紀行-by-A2   |         2 |
|    2 | A2-太宰二郎     |  3 | B3-評論-by-A2   |         2 |
|    1 | A1-芥川一郎     |  4 | B4-随筆-by-A1   |         1 |
|    4 | A4-夏目四郎     |  5 | B5-小説-by-A4   |         4 |
+------+-----------------+----+-----------------+-----------+
5 rows in set (0.009 sec)

MySQL [app_development]>
MySQL [app_development]> SELECT authors.id, authors.name, books.id, books.title, books.author_id FROM authors RIGHT JOIN books ON authors.id = books.author_id;
+------+-----------------+----+-----------------+-----------+
| id   | name            | id | title           | author_id |
+------+-----------------+----+-----------------+-----------+
|    1 | A1-芥川一郎     |  1 | B1-随筆-by-A1   |         1 |
|    2 | A2-太宰二郎     |  2 | B2-紀行-by-A2   |         2 |
|    2 | A2-太宰二郎     |  3 | B3-評論-by-A2   |         2 |
|    1 | A1-芥川一郎     |  4 | B4-随筆-by-A1   |         1 |
|    4 | A4-夏目四郎     |  5 | B5-小説-by-A4   |         4 |
+------+-----------------+----+-----------------+-----------+
5 rows in set (0.002 sec)

Railsの挙動

joinsはINNER JOINを行う

INNER JOINを行い、テーブルを結合させる
レコード数が変化するので注意！
- Author自体は4レコード。本を持たないID: 3が消え、本を2つ持つID: 1, 2が2回数えられる。

irb(main):020> Author.joins(:books)
  Author Load (4.7ms)  SELECT `authors`.* FROM `authors` INNER JOIN `books` ON `books`.`author_id` = `authors`.`id`
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
5 rows in set

irb(main):022> Author.joins(:books).count
  Author Count (1.2ms)  SELECT COUNT(*) FROM `authors` INNER JOIN `books` ON `books`.`author_id` = `authors`.`id`
=> 5

where句とも組み合わせられる

irb(main):023> Author.joins(:books).where(books: { title: "B2-紀行-by-A2" })
  Author Load (5.0ms)  SELECT `authors`.* FROM `authors` INNER JOIN `books` ON `books`.`author_id` = `authors`.`id` WHERE `books`.`title` = 'B2-紀行-by-A2'
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
1 row in set

includesはjoinsとは異なる（挙動が揺れるので特殊）

上記のjoins(:books)との比較
- レコード数はAuthor自体のままであるし、Bookを持たないID: 3もそのまま表示される。

irb(main):027> Author.includes(:books)
  Author Load (5.2ms)  SELECT `authors`.* FROM `authors`
  Book Load (0.9ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` IN (1, 2, 3, 4)
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 3  | A3-川端三郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
4 rows in set

irb(main):029> Author.includes(:books).count
  Author Count (9.2ms)  SELECT COUNT(*) FROM `authors`
=> 4

where句とも組み合わせられる。が、SQLが変化する。

irb(main):006> Author.includes(:books).where(books: { title: "B2-紀行-by-A2" })
  SQL (4.8ms)  SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` WHERE `books`.`title` = 'B2-紀行-by-A2'
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
1 row in set

where句がない場合のクエリ

Author Load (5.2ms)  SELECT `authors`.* FROM `authors`
  Book Load (0.9ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` IN (1, 2, 3, 4)

where句がある場合のクエリ

SQL (4.8ms)  SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` WHERE `books`.`title` = 'B2-紀行-by-A2'

詳細は後述

N+1問題の解決

親のレコードN件をまとめて取得（1回）+ N件それぞれのレコードに対してクエリを発行（N回）によって合計1+N回のクエリが発生する問題
includesを使うと解決すると言われる

N+1問題の発生と解決

普通に回すと発生

irb(main):051> Author.all.each { |a| puts a.books.first&.title }
  Author Load (0.9ms)  SELECT `authors`.* FROM `authors`
  Book Load (1.0ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 1 ORDER BY `books`.`id` ASC LIMIT 1
B1-随筆-by-A1
  Book Load (0.6ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 2 ORDER BY `books`.`id` ASC LIMIT 1
B2-紀行-by-A2
  Book Load (0.5ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 3 ORDER BY `books`.`id` ASC LIMIT 1

  Book Load (0.4ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 4 ORDER BY `books`.`id` ASC LIMIT 1
B5-小説-by-A4
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 3  | A3-川端三郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
4 rows in set

includesを用いて事前にBookを読み込んでおけば問題が発生しない

irb(main):054> Author.includes(:books).all.each { |a| puts a.books.first&.title }
  Author Load (1.0ms)  SELECT `authors`.* FROM `authors`
  Book Load (0.6ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` IN (1, 2, 3, 4)
B1-随筆-by-A1
B2-紀行-by-A2

B5-小説-by-A4
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 3  | A3-川端三郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
4 rows in set

preloadとeager_loadでも問題が発生しない
- 正確には、includesの方がpreloadかeager_loadのどちらかの挙動を取る
- この場合はwhere句が用いられてないので、includes = preload

irb(main):062> Author.preload(:books).all.each { |a| puts a.books.first&.title }
  Author Load (0.8ms)  SELECT `authors`.* FROM `authors`
  Book Load (0.8ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` IN (1, 2, 3, 4)
B1-随筆-by-A1
B2-紀行-by-A2

B5-小説-by-A4
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 3  | A3-川端三郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
4 rows in set

irb(main):066> Author.eager_load(:books).all.each { |a| puts a.books.first&.title }
  SQL (7.0ms)  SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id`
B4-随筆-by-A1
B3-評論-by-A2

B5-小説-by-A4
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 3  | A3-川端三郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
4 rows in set

joinsを用いると、結果が変わってしまうのでそもそもだめ。なおN+1問題も発生。

INNER JOINによりテーブルが結合されることで、そもそもAuthorでユニークにeachする形ではなくなる。
N+1問題も発生している。

irb(main):056> Author.joins(:books).all.each { |a| puts a.books.first&.title }
  Author Load (1.1ms)  SELECT `authors`.* FROM `authors` INNER JOIN `books` ON `books`.`author_id` = `authors`.`id`
  Book Load (0.8ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 1 ORDER BY `books`.`id` ASC LIMIT 1
B1-随筆-by-A1
  Book Load (0.9ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 1 ORDER BY `books`.`id` ASC LIMIT 1
B1-随筆-by-A1
  Book Load (0.7ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 2 ORDER BY `books`.`id` ASC LIMIT 1
B2-紀行-by-A2
  Book Load (0.6ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 2 ORDER BY `books`.`id` ASC LIMIT 1
B2-紀行-by-A2
  Book Load (0.4ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 4 ORDER BY `books`.`id` ASC LIMIT 1
B5-小説-by-A4
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 4  | A4-夏目四郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
5 rows in set

includesの挙動は、where句がない場合はpreload、ある場合はeager_load

「IDが[1, 2, 3, 4]であるBookのAuthorを取得したい」
- テストデータでは、A1-芥川一郎が2冊（book_id: [1, 4]）、A2-太宰二郎が2冊（book_id: [2, 3]）持っている。
whereを用いるとincludes=eager_loadとなる。
- preloadではwhereとの組み合わせは不可能

irb(main):005> Author.includes(:books).where(books: { id: [1, 2, 3, 4] }).each { |a| puts a.name }
  SQL (4.7ms)  SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` WHERE `books`.`id` IN (1, 2, 3, 4)
A1-芥川一郎
A2-太宰二郎
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
2 rows in set


irb(main):008> Author.preload(:books).where(books: { id: [1, 2, 3, 4] }).each { |a| puts a.name }
  Author Load (3.4ms)  SELECT `authors`.* FROM `authors` WHERE `books`.`id` IN (1, 2, 3, 4)
(irb):8:in `<main>': Mysql2::Error: Unknown column 'books.id' in 'where clause' (ActiveRecord::StatementInvalid)
/usr/local/bundle/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `_query': Unknown column 'books.id' in 'where clause' (Mysql2::Error)
	from /usr/local/bundle/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `block in query'
	from /usr/local/bundle/gems/mysql2-0.5.6/lib/mysql2/client.rb:150:in `handle_interrupt'
	from /usr/local/bundle/gems/mysql2-0.5.6/lib/mysql2/client.rb:150:in `query'
	...略...

irb(main):011> Author.eager_load(:books).where(books: { id: [1, 2, 3, 4] }).each { |a| puts a.name }
  SQL (0.9ms)  SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` WHERE `books`.`id` IN (1, 2, 3, 4)
A1-芥川一郎
A2-太宰二郎
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
| 2  | A2-太宰二郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
2 rows in set

なお、includesとeager_loadで発行されるSQLクエリではLEFT OUTER JOINを行っているので、結合されたテーブルは4行になるはずだが、Railsの出力では2行になっている（joinsのように行が増えることがない）
試しに、MySQLで同じSQLを叩くと、4行が出力される。

MySQL [app_development]> SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` WHERE `books`.`id` IN (1, 2, 3, 4);
+-------+-----------------+----------------------------+----------------------------+-------+-----------------+-------+----------------------------+----------------------------+
| t0_r0 | t0_r1           | t0_r2                      | t0_r3                      | t1_r0 | t1_r1           | t1_r2 | t1_r3                      | t1_r4                      |
+-------+-----------------+----------------------------+----------------------------+-------+-----------------+-------+----------------------------+----------------------------+
|     1 | A1-芥川一郎     | 2024-05-25 23:46:06.258529 | 2024-05-25 23:46:06.258529 |     1 | B1-随筆-by-A1   |     1 | 2024-05-25 23:46:06.285903 | 2024-05-25 23:46:06.285903 |
|     1 | A1-芥川一郎     | 2024-05-25 23:46:06.258529 | 2024-05-25 23:46:06.258529 |     4 | B4-随筆-by-A1   |     1 | 2024-05-25 23:46:06.300501 | 2024-05-25 23:46:06.300501 |
|     2 | A2-太宰二郎     | 2024-05-25 23:46:06.263497 | 2024-05-25 23:46:06.263497 |     2 | B2-紀行-by-A2   |     2 | 2024-05-25 23:46:06.290174 | 2024-05-25 23:46:06.290174 |
|     2 | A2-太宰二郎     | 2024-05-25 23:46:06.263497 | 2024-05-25 23:46:06.263497 |     3 | B3-評論-by-A2   |     2 | 2024-05-25 23:46:06.295014 | 2024-05-25 23:46:06.295014 |
+-------+-----------------+----------------------------+----------------------------+-------+-----------------+-------+----------------------------+----------------------------+
4 rows in set (0.002 sec)

さらにもう一つのテーブルを紐づけたときに例

ケース「rating 5のReviewがついたことのあるBookを書いたAuthorを取得したい」

irb(main):056> Author.includes(books: :reviews).where(reviews: { rating: 5 }).each { |a| puts a.name }
  SQL (5.7ms)  SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4, `reviews`.`id` AS t2_r0, `reviews`.`rating` AS t2_r1, `reviews`.`body` AS t2_r2, `reviews`.`user_id` AS t2_r3, `reviews`.`book_id` AS t2_r4, `reviews`.`created_at` AS t2_r5, `reviews`.`updated_at` AS t2_r6 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` WHERE `reviews`.`rating` = 5
A1-芥川一郎
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
1 row in set

irb(main):058> Author.preload(books: :reviews).where(reviews: { rating: 5 }).each { |a| puts a.name }
  Author Load (1.3ms)  SELECT `authors`.* FROM `authors` WHERE `reviews`.`rating` = 5
(irb):58:in `<main>': Mysql2::Error: Unknown column 'reviews.rating' in 'where clause' (ActiveRecord::StatementInvalid)
/usr/local/bundle/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `_query': Unknown column 'reviews.rating' in 'where clause' (Mysql2::Error)
	from /usr/local/bundle/gems/mysql2-0.5.6/lib/mysql2/client.rb:151:in `block in query'
	from /usr/local/bundle/gems/mysql2-0.5.6/lib/mysql2/client.rb:150:in `handle_interrupt'
  ..略..

irb(main):060> Author.eager_load(books: :reviews).where(reviews: { rating: 5 }).each { |a| puts a.name }
  SQL (1.0ms)  SELECT `authors`.`id` AS t0_r0, `authors`.`name` AS t0_r1, `authors`.`created_at` AS t0_r2, `authors`.`updated_at` AS t0_r3, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author_id` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4, `reviews`.`id` AS t2_r0, `reviews`.`rating` AS t2_r1, `reviews`.`body` AS t2_r2, `reviews`.`user_id` AS t2_r3, `reviews`.`book_id` AS t2_r4, `reviews`.`created_at` AS t2_r5, `reviews`.`updated_at` AS t2_r6 FROM `authors` LEFT OUTER JOIN `books` ON `books`.`author_id` = `authors`.`id` LEFT OUTER JOIN `reviews` ON `reviews`.`book_id` = `books`.`id` WHERE `reviews`.`rating` = 5
A1-芥川一郎
+----+-------------+-------------------------+-------------------------+
| id | name        | created_at              | updated_at              |
+----+-------------+-------------------------+-------------------------+
| 1  | A1-芥川一郎 | 2024-05-25 23:46:06 UTC | 2024-05-25 23:46:06 UTC |
+----+-------------+-------------------------+-------------------------+
1 row in set

preloadとeager_loadの実行速度について

Book/User/Reviewをそれぞれ、100/1000/10000/100000件ずつにして実験
- rails consoleでコマンドを打つことを合計3回行い、平均を取る
結果、両者の実行時間にあまり差は見られなかった（他の記事ではpreloadの方が速いと言っているのを見たので、何か自分の実験方法がデータセットに違いがあるのかも？）

余談 includesの関連付けの指定方法について

直接関連している複数のmodelを順に紐づける際は、.includes(model_a: :model_b)と、シンボルで繋げる
別々のmodelを自身に紐づけたい際は、.includes(model_a, model_bと、カンマで繋げる

直接関連しているモデルを紐づける

articlesから始まり、全部で3つの関連しているmodelを呼び出す。1つずつN+1問題を解決していく

# 全てincludesしないので、N+1問題が3つ発生
irb(main):096> Author.all.each { |a| puts a.books.first&.reviews&.first&.user&.nickname }
  Author Load (6.0ms)  SELECT `authors`.* FROM `authors`
  Book Load (1.4ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 1 ORDER BY `books`.`id` ASC LIMIT 1
  Review Load (0.8ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` = 1 ORDER BY `reviews`.`id` ASC LIMIT 1
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
U4-小林 ユキコ
  Book Load (0.3ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 2 ORDER BY `books`.`id` ASC LIMIT 1
  Review Load (0.2ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` = 2 ORDER BY `reviews`.`id` ASC LIMIT 1
  User Load (0.1ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
U2-高橋 ユキコ
  Book Load (0.3ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 3 ORDER BY `books`.`id` ASC LIMIT 1

  Book Load (0.2ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` = 4 ORDER BY `books`.`id` ASC LIMIT 1
  Review Load (0.2ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` = 5 ORDER BY `reviews`.`id` ASC LIMIT 1
  User Load (0.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
U1-渡辺 マサオ


# 1つincludes / N+1問題が2つ発生
irb(main):092> Author.includes(:books).each { |a| puts a.books.first&.reviews&.first&.user&.nickname }
  Author Load (1.3ms)  SELECT `authors`.* FROM `authors`
  Book Load (0.8ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` IN (1, 2, 3, 4)
  Review Load (0.6ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` = 1 ORDER BY `reviews`.`id` ASC LIMIT 1
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
U4-小林 ユキコ
  Review Load (0.6ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` = 2 ORDER BY `reviews`.`id` ASC LIMIT 1
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
U2-高橋 ユキコ

  Review Load (0.7ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` = 5 ORDER BY `reviews`.`id` ASC LIMIT 1
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
U1-渡辺 マサオ



# 2つincludes / N+1問題が1つ発生
irb(main):088> Author.includes(books: :reviews).each { |a| puts a.books.first&.reviews&.first&.user&.nickname }
  Author Load (6.1ms)  SELECT `authors`.* FROM `authors`
  Book Load (1.9ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` IN (1, 2, 3, 4)
  Review Load (0.8ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` IN (1, 2, 3, 4, 5)
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
U4-小林 ユキコ
  User Load (0.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
U2-高橋 ユキコ

  User Load (0.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
U1-渡辺 マサオ


# 全てincludesするので、N+1問題は発生しない
irb(main):081> Author.includes(books: { reviews: :user }).each { |a| puts a.books.first&.reviews&.first&.user&.nickname }
  Author Load (0.9ms)  SELECT `authors`.* FROM `authors`
  Book Load (0.8ms)  SELECT `books`.* FROM `books` WHERE `books`.`author_id` IN (1, 2, 3, 4)
  Review Load (0.5ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`book_id` IN (1, 2, 3, 4, 5)
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (4, 1, 2)
U4-小林 ユキコ
U2-高橋 ユキコ

U1-渡辺 マサオ

並列の関連付け

2つのmodelを関連させている。それぞれは独立。

# 2つともincludesしないので、N+1問題が2つ
irb(main):016> Review.all.each { |r| puts "#{r.user.nickname} #{r.book.title}" }
  Review Load (1.0ms)  SELECT `reviews`.* FROM `reviews`
  User Load (0.7ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
  Book Load (0.4ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 1 LIMIT 1
U4-小林 ユキコ B1-随筆-by-A1
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.5ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 5 LIMIT 1
U1-渡辺 マサオ B5-小説-by-A4
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
  Book Load (0.7ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 2 LIMIT 1
U2-高橋 ユキコ B2-紀行-by-A2
  User Load (0.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
  Book Load (0.2ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 3 LIMIT 1
U4-小林 ユキコ B3-評論-by-A2
  User Load (0.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
  Book Load (0.2ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 1 LIMIT 1
U1-渡辺 マサオ B1-随筆-by-A1


# 1つincludes / N+1問題が1つ発生
irb(main):020> Review.includes(:user).each { |r| puts "#{r.user.nickname} #{r.book.title}" }
  Review Load (1.5ms)  SELECT `reviews`.* FROM `reviews`
  User Load (1.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (4, 1, 2)
  Book Load (1.0ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 1 LIMIT 1
U4-小林 ユキコ B1-随筆-by-A1
  Book Load (0.5ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 5 LIMIT 1
U1-渡辺 マサオ B5-小説-by-A4
  Book Load (0.4ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 2 LIMIT 1
U2-高橋 ユキコ B2-紀行-by-A2
  Book Load (0.2ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 3 LIMIT 1
U4-小林 ユキコ B3-評論-by-A2
  Book Load (0.3ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` = 1 LIMIT 1
U1-渡辺 マサオ B1-随筆-by-A1


# 同じく、1つincludes / N+1問題が1つ発生
irb(main):024> Review.includes(:book).each { |r| puts "#{r.user.nickname} #{r.book.title}" }
  Review Load (3.8ms)  SELECT `reviews`.* FROM `reviews`
  Book Load (0.4ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` IN (1, 5, 2, 3)
  User Load (0.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
U4-小林 ユキコ B1-随筆-by-A1
  User Load (0.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
U1-渡辺 マサオ B5-小説-by-A4
  User Load (0.1ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
U2-高橋 ユキコ B2-紀行-by-A2
  User Load (0.1ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 4 LIMIT 1
U4-小林 ユキコ B3-評論-by-A2
  User Load (0.2ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 1 LIMIT 1
U1-渡辺 マサオ B1-随筆-by-A1


# 2つともincludesするので、N+1問題が解決する
irb(main):027> Review.includes(:book, :user).each { |r| puts "#{r.user.nickname} #{r.book.title}" }
  Review Load (2.3ms)  SELECT `reviews`.* FROM `reviews`
  Book Load (1.5ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` IN (1, 5, 2, 3)
  User Load (0.9ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (4, 1, 2)
U4-小林 ユキコ B1-随筆-by-A1
U1-渡辺 マサオ B5-小説-by-A4
U2-高橋 ユキコ B2-紀行-by-A2
U4-小林 ユキコ B3-評論-by-A2
U1-渡辺 マサオ B1-随筆-by-A1

並列と直列の組み合わせ

# authorsはincludesしていないので、N+1問題が発生している
irb(main):031> Review.includes(:book, :user).each { |r| puts "#{r.user.nickname} #{r.book.title} #{r.book.author.name}" }
  Review Load (5.4ms)  SELECT `reviews`.* FROM `reviews`
  Book Load (1.2ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` IN (1, 5, 2, 3)
  User Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (4, 1, 2)
  Author Load (0.6ms)  SELECT `authors`.* FROM `authors` WHERE `authors`.`id` = 1 LIMIT 1
U4-小林 ユキコ B1-随筆-by-A1 A1-芥川一郎
  Author Load (0.3ms)  SELECT `authors`.* FROM `authors` WHERE `authors`.`id` = 4 LIMIT 1
U1-渡辺 マサオ B5-小説-by-A4 A4-夏目四郎
  Author Load (0.3ms)  SELECT `authors`.* FROM `authors` WHERE `authors`.`id` = 2 LIMIT 1
U2-高橋 ユキコ B2-紀行-by-A2 A2-太宰二郎
  Author Load (0.3ms)  SELECT `authors`.* FROM `authors` WHERE `authors`.`id` = 2 LIMIT 1
U4-小林 ユキコ B3-評論-by-A2 A2-太宰二郎
U1-渡辺 マサオ B1-随筆-by-A1 A1-芥川一郎


# 全てincludesすることでN+1問題が解決
irb(main):048> Review.includes({ book: :author }, :user).each { |r| puts "#{r.user.nickname} #{r.book.title} #{r.book.author.name}" }
  Review Load (1.2ms)  SELECT `reviews`.* FROM `reviews`
  Book Load (0.6ms)  SELECT `books`.* FROM `books` WHERE `books`.`id` IN (1, 5, 2, 3)
  Author Load (0.5ms)  SELECT `authors`.* FROM `authors` WHERE `authors`.`id` IN (1, 2, 4)
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (4, 1, 2)
U4-小林 ユキコ B1-随筆-by-A1 A1-芥川一郎
U1-渡辺 マサオ B5-小説-by-A4 A4-夏目四郎
U2-高橋 ユキコ B2-紀行-by-A2 A2-太宰二郎
U4-小林 ユキコ B3-評論-by-A2 A2-太宰二郎
U1-渡辺 マサオ B1-随筆-by-A1 A1-芥川一郎

余談 dbのデータを一時的にstashし、またimportするコマンド

この記事の執筆時点のデータを保存しておきたいとき

// バックアップを取得
$ docker compose exec db mysqldump app_development -u root -ppassword | sed 1d > zenn-article-data-backup.sql

// バックアップからインポート
$ docker compose exec -T db sh -c 'mysql -u root -ppassword app_development' < zenn-article-data-backup.sql