INNER JOIN で eager loading #Rails

INNER JOIN じゃないと不味いが joins だと N + 1 問題が発生してしまう場合にどうするかを調べた。

結論から言うと joins + preload または joins + eager_load を使うと INNER JOIN を使って結合した上で関連オブジェクトも即時フェッチしてくれる。

試したのは Rails 4.1.6。

joins + preload

INNER JOIN で結合
クエリ複数回(指定した関連オブジェクトの分)
即時フェッチ

> Post.joins(:comments).preload(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"
Comment Load (0.3ms)  SELECT "comments".* FROM "comments"  WHERE "comments"."post_id" IN (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)

joins + eager_load

INNER JOIN で結合
クエリ一発
即時フェッチ

> Post.joins(:comments).eager_load(:comments)
SQL (0.4ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."lock_version" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."post_id" AS t1_r1, "comments"."name" AS t1_r2, "comments"."body" AS t1_r3, "comments"."lock_version" AS t1_r4, "comments"."created_at" AS t1_r5, "comments"."updated_at" AS t1_r6 FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"

ちなみに他のメソッドだと以下のようになる。

joins

INNER JOIN で結合
遅延フェッチ

> Post.joins(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"

includes

JOIN 使わない
クエリ複数回(指定した関連オブジェクトの分)
即時フェッチ

> Post.includes(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts"
Comment Load (0.3ms)  SELECT "comments".* FROM "comments"  WHERE "comments"."post_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)

includes + references

LEFT OUTER JOIN で結合
クエリ一発
即時フェッチ

> Post.includes(:comments).references(:comments)
SQL (0.4ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."lock_version" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."post_id" AS t1_r1, "comments"."name" AS t1_r2, "comments"."body" AS t1_r3, "comments"."lock_version" AS t1_r4, "comments"."created_at" AS t1_r5, "comments"."updated_at" AS t1_r6 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

eager_load

includes + references と同じ

> Post.eager_load(:comments)
SQL (0.4ms)  SELECT "posts"."id" AS t0_r0, "posts"."title" AS t0_r1, "posts"."body" AS t0_r2, "posts"."lock_version" AS t0_r3, "posts"."created_at" AS t0_r4, "posts"."updated_at" AS t0_r5, "comments"."id" AS t1_r0, "comments"."post_id" AS t1_r1, "comments"."name" AS t1_r2, "comments"."body" AS t1_r3, "comments"."lock_version" AS t1_r4, "comments"."created_at" AS t1_r5, "comments"."updated_at" AS t1_r6 FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

preload

includes と同じ

> Post.preload(:comments)
Post Load (0.3ms)  SELECT "posts".* FROM "posts"
Comment Load (0.5ms)  SELECT "comments".* FROM "comments"  WHERE "comments"."post_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31)

参考

3 ways to do eager loading (preloading) in Rails 3 & 4 - Arkency Blog
http://blog.arkency.com/2013/12/rails4-preloading/

Preload, Eagerload, Includes and Joins · BigBinary Blog
http://blog.bigbinary.com/2013/07/01/preload-vs-eager-load-vs-joins-vs-includes.html

Rails - ActiveRecordのjoinsとpreloadとincludesとeager_loadの違い - Qiita
http://qiita.com/k0kubun/items/80c5a5494f53bb88dc58