INNER JOIN で eager loading(Rails 5) #Rails

そろそろ Rails 5 使おうと思って以前調べた奴が Rails 5 で何か変わってないか試してみた。

試した範囲では同じ使い方なら特に SQL は変わってないっぽい。

環境

試した環境は以下。

% bin/rake about
Running via Spring preloader in process 7063
About your application's environment
Rails version             5.0.1
Ruby version              2.3.1-p112 (x86_64-darwin15)
RubyGems version          2.5.1
Rack version              2.0.1
JavaScript Runtime        Node.js (V8)
Middleware                Rack::Sendfile, ActionDispatch::Static, ActionDispatch::Executor, ActiveSupport::Cache::Strategy::LocalCache::Middleware, Rack::Runtime, Rack::MethodOverride, ActionDispatch::RequestId, Sprockets::Rails::QuietAssets, Rails::Rack::Logger, ActionDispatch::ShowExceptions, WebConsole::Middleware, ActionDispatch::DebugExceptions, ActionDispatch::RemoteIp, ActionDispatch::Reloader, ActionDispatch::Callbacks, ActiveRecord::Migration::CheckPending, ActionDispatch::Cookies, ActionDispatch::Session::CookieStore, ActionDispatch::Flash, Rack::Head, Rack::ConditionalGet, Rack::ETag
Application root          /Users/akishin/src/ruby/example_app
Environment               development
Database adapter          sqlite3
Database schema version   20161223010846

準備

適当な Rails プロジェクト作成。

% rails new example_app && cd example_app

適当なモデル作成。

% bin/rails g scaffold post title:string body:text lock_version:integer
% bin/rails g scaffold comment post:references name:string body:text lock_version:integer
% bin/rake db:create db:migrate

app/models/post.rb に has_many 追加。

class Post < ApplicationRecord
  has_many :comments, dependent: :destroy
end

rails console 起動して適当なデータを作成。

% bin/rails c

>> post1 = Post.create(title: 'test1', body: 'Test1')
>> post1.comments.create(name: 'test1 comment1', body: 'Test1 Comment1')
>> post1.comments.create(name: 'test1 comment2', body: 'Test1 Comment2')

>> post2 = Post.create(title: 'test2', body: 'Test2')
>> post2.comments.create(name: 'test2 comment1', body: 'Test2 Comment1')
>> post2.comments.create(name: 'test2 comment2', body: 'Test2 Comment2')

joins + preload

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

>> Post.joins(:comments).preload(:comments)
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"
  Comment Load (0.4ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2)

joins + eager_load

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

>> Post.joins(:comments).eager_load(:comments)
  SQL (0.2ms)  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.1ms)  SELECT "posts".* FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"

includes

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

>> Post.includes(:comments)
  Post Load (0.1ms)  SELECT "posts".* FROM "posts"
  Comment Load (0.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2)

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.2ms)  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.1ms)  SELECT "posts".* FROM "posts"
  Comment Load (0.2ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN (1, 2)

left_outer_joins

Rails 5 から追加された。
LEFT OUTER JOIN を使いつつ関連オブジェクト自体はロードしたくない場合に使える。

LEFT OUTER JOIN で結合
遅延フェッチ

>> Post.left_outer_joins(:comments)
  Post Load (0.2ms)  SELECT "posts".* FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

left_joins

left_outer_joins のエイリアス。

>> Post.left_joins(:comments)
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"

参考

Support for left outer join in Rails 5 | BigBinary Blog
http://blog.bigbinary.com/2016/03/24/support-for-left-outer-joins-in-rails-5.html