Skip to content

Instantly share code, notes, and snippets.

@seak0503

seak0503/rails_joins_includes.md

Created February 21, 2016 22:05
Show Gist options
  • Save seak0503/63cd694d55ce9fa6c1b7 to your computer and use it in GitHub Desktop.
Save seak0503/63cd694d55ce9fa6c1b7 to your computer and use it in GitHub Desktop.
Download ZIP
rails joinsとincludesの考察
Raw
rails_joins_includes.md
  • usersテーブル
+----+--------------------+------------------------------+
| id | user               | email                        |
+----+--------------------+------------------------------+
|  1 | 上長尾             | kelley.cartwright@willms.org |
|  2 | クズネツォフ       | marlene.paucek@stanton.info  |
|  3 | ラサール           | jennyfer@thompson.com        |
|  4 | 沢里               | arely.dietrich@batz.io       |
|  5 | 設楽               | rosina@champlinlesch.org     |
+----+--------------------+------------------------------+
5 rows in set (0.00 sec)
  • favorite_booksテーブル
+----+---------+---------+
| id | user_id | book_id |
+----+---------+---------+
|  2 |       1 |       1 |
|  1 |       1 |       5 |
|  3 |       1 |       6 |
|  5 |       1 |       8 |
|  4 |       1 |      15 |
|  8 |       2 |       2 |
|  6 |       2 |      12 |
|  9 |       2 |      15 |
|  7 |       2 |      16 |
| 11 |       3 |      10 |
| 13 |       3 |      12 |
| 12 |       3 |      17 |
| 10 |       3 |      18 |
| 17 |       4 |       1 |
| 16 |       4 |       6 |
| 18 |       4 |       7 |
| 14 |       4 |       8 |
| 15 |       4 |      11 |
| 21 |       5 |       3 |
| 22 |       5 |      10 |
| 20 |       5 |      15 |
| 19 |       5 |      16 |
+----+---------+---------+
22 rows in set (0.00 sec)
  • booksテーブル
+----+-----------------------------+----------------------+
| id | title                       | author               |
+----+-----------------------------+----------------------+
|  1 | Slicker Than Rain           | Helene Goldner       |
|  2 | Burning Water               | Greyson Weissnat     |
|  3 | Covered and Warm            | Emmalee Johnston Sr. |
|  4 | Next Day Previous Night     | Ms. Daisy Wilderman  |
|  5 | Stop Asking, Just Do        | Mack Smitham         |
|  6 | What Lives on Pluto         | Unique Wehner        |
|  7 | No Lonely Stars             | Reinhold Prosacco I  |
|  8 | The Zookeeper and Her Tiger | Oswaldo Hane         |
|  9 | Walking and Running         | Dovie Cummings PhD   |
| 10 | Same Way Through            | Jeff Lehner III      |
| 11 | Ready, Set, Die             | Rickie Turner        |
| 12 | A Dozen Heros               | Ms. Destin Crona     |
| 13 | Tomato in the Window        | Asia O'Conner        |
| 14 | Fishing With Chips          | Alvis Koch           |
| 15 | The Odd Sister              | Clara Nolan          |
| 16 | Free Parking                | Celia Watsica        |
| 17 | Candy For Two               | Nova Rice            |
| 18 | Falling Flags               | Keely Huels          |
+----+-----------------------------+----------------------+
18 rows in set (0.00 sec)
  • blogsテーブル
+----+---------+--------------------------------+
| id | user_id | title                          |
+----+---------+--------------------------------+
|  1 |       4 | Covered and Warm               |
|  2 |       4 | Falling Flags                  |
|  3 |       1 | Stop Asking, Just Do           |
|  4 |       2 | Next Day Previous Night        |
|  5 |       4 | A Dozen Heros                  |
|  6 |       3 | No Lonely Stars                |
|  7 |       2 | Free Parking                   |
|  8 |       3 | The Frog Catchers Field Manual |
|  9 |       3 | Candy For Two                  |
| 10 |       5 | Keyboard For Hire              |
| 11 |       4 | Tomato in the Window           |
| 12 |       3 | Future Discretions             |
| 13 |       1 | Walking and Running            |
| 14 |       3 | Fishing With Chips             |
| 15 |       2 | Same Way Through               |
+----+---------+--------------------------------+
15 rows in set (0.00 sec)

includes

includesはデータの先読みをしてくれる。その為、関連model(Book)に対する参照を持ちたい場合に使う。

例えば、userがFavorit Bookとして登録してる、本のタイトルを検索する場合、下記のようになる。

  • 実行
User.includes(:books).each { |user| p user.books.map(&:title) }
  • SQL
SELECT `users`.* FROM `users`
SELECT `favorite_books`.* FROM `favorite_books`  WHERE `favorite_books`.`user_id` IN (1, 2, 3, 4, 5)
SELECT `books`.* FROM `books`  WHERE `books`.`id` IN (5, 1, 6, 15, 8, 12, 16, 2, 18, 10, 17, 11, 7, 3)
  • 結果
["Slicker Than Rain", "Stop Asking, Just Do", "What Lives on Pluto", "The Zookeeper and Her Tiger", "The Odd Sister"]
["Burning Water", "A Dozen Heros", "The Odd Sister", "Free Parking"]
["Same Way Through", "A Dozen Heros", "Candy For Two", "Falling Flags"]
["Slicker Than Rain", "What Lives on Pluto", "No Lonely Stars", "The Zookeeper and Her Tiger", "Ready, Set, Die"]
["Covered and Warm", "Same Way Through", "The Odd Sister", "Free Parking"]

users, favorite_books, booksへのsqlの発行がそれぞれ1回ずつ行われている。

usersを取得し、取得したusersに相当するfavorite_booksを取得し、さらに取得した favorite_booksに相当するbooksを取得している。

includes(:books)しなかった場合にはこうはいかない。

usersからbooksへの参照が発生するたびに新規のsqlを発行してbooksを取得しようとしてしまうため、 いわゆる N+1問題 が発生する。

  • 実行
User.all.each { |user| p user.books.map(&:title) }
  • SQL
SELECT `users`.* FROM `users`
SELECT `books`.* FROM `books` INNER JOIN `favorite_books` ON `books`.`id` = `favorite_books`.`book_id` WHERE `favorite_books`.`user_id` = 1
SELECT `books`.* FROM `books` INNER JOIN `favorite_books` ON `books`.`id` = `favorite_books`.`book_id` WHERE `favorite_books`.`user_id` = 2
SELECT `books`.* FROM `books` INNER JOIN `favorite_books` ON `books`.`id` = `favorite_books`.`book_id` WHERE `favorite_books`.`user_id` = 3
SELECT `books`.* FROM `books` INNER JOIN `favorite_books` ON `books`.`id` = `favorite_books`.`book_id` WHERE `favorite_books`.`user_id` = 4
SELECT `books`.* FROM `books` INNER JOIN `favorite_books` ON `books`.`id` = `favorite_books`.`book_id` WHERE `favorite_books`.`user_id` = 5
  • 結果
["Slicker Than Rain", "Stop Asking, Just Do", "What Lives on Pluto", "The Zookeeper and Her Tiger", "The Odd Sister"]
["Burning Water", "A Dozen Heros", "The Odd Sister", "Free Parking"]
["Same Way Through", "A Dozen Heros", "Candy For Two", "Falling Flags"]
["Slicker Than Rain", "What Lives on Pluto", "No Lonely Stars", "The Zookeeper and Her Tiger", "Ready, Set, Die"]
["Covered and Warm", "Same Way Through", "The Odd Sister", "Free Parking"]

joins

joinsの仕組みはシンプルで、ただINNER JOINしてくれる。

  • 実行
User.joins(:books)
  • SQL
SELECT `users`.* FROM `users` INNER JOIN `favorite_books` ON `favorite_books`.`user_id` = `users`.`id` INNER JOIN `books` ON `books`.`id` = `favorite_books`.`book_id`

joinsを使えば、joins先のテーブル(books)のカラムの値を使って絞り込んだり出来る。

  • 実行
User.joins(:books).where(books: {id: [*1..3]})
  • SQL
SELECT `users`.* FROM `users` INNER JOIN `favorite_books` ON `favorite_books`.`user_id` = `users`.`id` INNER JOIN `books` ON `books`.`id` = `favorite_books`.`book_id` WHERE `books`.`id` IN (1, 2, 3)
  • 結果
#<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, #<User id: 4, user: "沢里", email: "arely.dietrich@batz.io", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 

#<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, #<User id: 5, user: "設楽", email: "rosina@champlinlesch.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">

joinsで注意しなければいけないのは、INNER JOINしたものとしていないもので返される結果が変わってくること。

当然、返ってくるレコードの数は違ってくる。

  • includesもjoinsもしていない普通のクエリ
User.all.size
   (0.4ms)  SELECT COUNT(*) FROM `users`
=> 5

User.all
  User Load (0.4ms)  SELECT `users`.* FROM `users`
=> #<ActiveRecord::Relation [
  #<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 3, user: "ラサール", email: "jennyfer@thompson.com", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 4, user: "沢里", email: "arely.dietrich@batz.io", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 5, user: "設楽", email: "rosina@champlinlesch.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">]>
  • includesした場合。includesしていない場合と変わりはない
User.includes(:books).all.size
   (0.4ms)  SELECT COUNT(*) FROM `users`
=> 5

User.includes(:books).all
  User Load (0.4ms)  SELECT `users`.* FROM `users`
  FavoriteBook Load (0.5ms)  SELECT `favorite_books`.* FROM `favorite_books`  WHERE `favorite_books`.`user_id` IN (1, 2, 3, 4, 5)
  Book Load (0.4ms)  SELECT `books`.* FROM `books`  WHERE `books`.`id` IN (5, 1, 6, 15, 8, 12, 16, 2, 18, 10, 17, 11, 7, 3)
=> #<ActiveRecord::Relation [
  #<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 3, user: "ラサール", email: "jennyfer@thompson.com", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 4, user: "沢里", email: "arely.dietrich@batz.io", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 5, user: "設楽", email: "rosina@champlinlesch.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">]>
  • joins(:books)した場合。返りの値に変化が起きた。
User.joins(:books).all.size
   (0.6ms)  SELECT COUNT(*) FROM `users` INNER JOIN `favorite_books` ON `favorite_books`.`user_id` = `users`.`id` INNER JOIN `books` ON `books`.`id` = `favorite_books`.`book_id`
=> 22

User.joins(:books).all
  User Load (0.6ms)  SELECT `users`.* FROM `users` INNER JOIN `favorite_books` ON `favorite_books`.`user_id` = `users`.`id` INNER JOIN `books` ON `books`.`id` = `favorite_books`.`book_id`
=> #<ActiveRecord::Relation [
  #<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 
  #<User id: 3, user: "ラサール", email: "jennyfer@thompson.com", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, ...以下略]>

それから、usersテーブルのカラム情報と一緒に、joinsしたテーブル(books)のカラム情報を表示するには、 明示的にselectをしてやらなければならない。

  • 実行
User.joins(:books).select('users.*, books.title').first.attributes
  • SQL
SELECT  users.*, books.title FROM `users` INNER JOIN `favorite_books` ON `favorite_books`.`user_id` = `users`.`id` INNER JOIN `books` ON `books`.`id` = `favorite_books`.`book_id`  ORDER BY `users`.`id` ASC LIMIT 1
  • 結果
{
  "id"=>1, 
  "user"=>"上長尾", 
  "email"=>"kelley.cartwright@willms.org", 
  "created_at"=>Sat, 20 Feb 2016 21:50:09 UTC +00:00, 
  "updated_at"=>Sat, 20 Feb 2016 21:50:09 UTC +00:00, 
  "title"=>"Slicker Than Rain"  <-- booksテーブルのtitleカラムの情報が入っている
}

1レコードが1つのオブジェクトにマッピングされるため、取得したオブジェクトのtitleプロパティにbooksのtitleが 格納されているのが特徴的。

includes + joinsで先読みしつつ絞り込み。

INNER JOINの場合でも、includes + joinsで先読みしつつJOIN先(books)の条件で絞り込みが出来る。

  • 実行
User.includes(:books).joins(:books).where(books: {id: [*1..3]})
  • SQL
SELECT `users`.`id` AS t0_r0, `users`.`user` AS t0_r1, `users`.`email` AS t0_r2, `users`.`created_at` AS t0_r3, `users`.`updated_at` AS t0_r4, `books`.`id` AS t1_r0, `books`.`title` AS t1_r1, `books`.`author` AS t1_r2, `books`.`created_at` AS t1_r3, `books`.`updated_at` AS t1_r4 FROM `users` INNER JOIN `favorite_books` ON `favorite_books`.`user_id` = `users`.`id` INNER JOIN `books` ON `books`.`id` = `favorite_books`.`book_id` WHERE `books`.`id` IN (1, 2, 3)
  • 結果
#<User id: 1, user: "上長尾", email: "kelley.cartwright@willms.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, #<User id: 4, user: "沢里", email: "arely.dietrich@batz.io", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, 

#<User id: 2, user: "クズネツォフ", email: "marlene.paucek@stanton.info", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">, #<User id: 5, user: "設楽", email: "rosina@champlinlesch.org", created_at: "2016-02-20 21:50:09", updated_at: "2016-02-20 21:50:09">

まとめ

  • ただ参照先(books)を利用したい場合はincludesを使う
  • INNER JOINして参照先(books)の条件where(books: {id: [*1..3]})を使って絞り込みたいならjoins
  • 参照先(books)の条件where(books: {id: [*1..3]})で絞り込みつつ、参照先(books)も利用したいならincludes + joins
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment