INNER JOIN で eager loading(Rails 5)


そろそろ Rails 5 使おうと思って[以前調べた奴](http://qiita.com/akishin/items/36dba4b2ccdaefb53dc6)が Rails 5 で何か変わってないか試してみた。

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

## 環境

試した環境は以下。

```shell-session
% 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 プロジェクト作成。

```shell-session
% rails new example_app && cd example_app
```

適当なモデル作成。

```shell-session
% 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 追加。

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

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

```ruby
% 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