Help us understand the problem. What is going on with this article?

includes を利用しても n+1 が発生する場合があるのでそれの抑制方法

More than 1 year has passed since last update.

結論

includesを利用してN+1を抑制して関連するオブジェクトの個数を取得したい時はsizeを使おう。

具体例

下記のような構造を持ったモデルクラスがある。

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

class Book < ApplicationRecord
  belongs_to :series
end

複数のSeriesと、それに紐づくBookの数を取得しようとするとN+1が発生する。

irb(main):013:0> Series.where('id < 20').select{|s|s.books.size < 20}
D, [2018-02-14T17:15:43.433778 #4] DEBUG -- :   Series Load (1.2ms)  SELECT "series".* FROM "series" WHERE (id < 20)
D, [2018-02-14T17:15:43.436488 #4] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 1]]
D, [2018-02-14T17:15:43.438897 #4] DEBUG -- :    (1.2ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 2]]
D, [2018-02-14T17:15:43.440871 #4] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 3]]
...
...
...
D, [2018-02-14T17:15:43.469686 #4] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 17]]
D, [2018-02-14T17:15:43.471588 #4] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 18]]
D, [2018-02-14T17:15:43.473640 #4] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 19]]

N+1を抑制するためにincludesを利用する。

irb(main):014:0> Series.where('id < 20').includes(:books).select{|s|s.books.size < 20}
D, [2018-02-14T17:15:46.393076 #4] DEBUG -- :   Series Load (1.2ms)  SELECT "series".* FROM "series" WHERE (id < 20)
D, [2018-02-14T17:15:46.398703 #4] DEBUG -- :   Book Load (3.8ms)  SELECT "books".* FROM "books" WHERE "books"."series_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19)

しかし、Bookを数えるのにcountメソッドを利用すると、COUNTのクエリが発行されてしまい、N+1が解消できない。

irb(main):015:0> Series.where('id < 20').includes(:books).select{|s|s.books.count < 20}
D, [2018-02-14T17:15:59.374879 #4] DEBUG -- :   Series Load (1.0ms)  SELECT "series".* FROM "series" WHERE (id < 20)
D, [2018-02-14T17:15:59.379237 #4] DEBUG -- :   Book Load (2.8ms)  SELECT "books".* FROM "books" WHERE "books"."series_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19)
D, [2018-02-14T17:15:59.415829 #4] DEBUG -- :    (0.8ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 1]]
D, [2018-02-14T17:15:59.417230 #4] DEBUG -- :    (0.6ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 2]]
...
...
...
D, [2018-02-14T17:15:59.437119 #4] DEBUG -- :    (0.7ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 17]]
D, [2018-02-14T17:15:59.438352 #4] DEBUG -- :    (0.6ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 18]]
D, [2018-02-14T17:15:59.439596 #4] DEBUG -- :    (0.6ms)  SELECT COUNT(*) FROM "books" WHERE "books"."series_id" = $1  [["series_id", 19]]

考察

sizeメソッドは内部的にArrayのメソッドを呼び出している。
対してcountメソッドはcountクエリを発行するような処理となっている。
よって、countメソッドを呼び出すとincludesを無視してそれぞれのクエリが発行されてしまい、N+1となる。

参考

https://github.com/rails/rails/blob/6df9b69b2363734175d8869393e83e6ce8b4d7b6/activerecord/lib/active_record/associations/collection_association.rb#L209
https://github.com/rails/rails/blob/6df9b69b2363734175d8869393e83e6ce8b4d7b6/activerecord/lib/active_record/relation/calculations.rb#L39

color_box
帰ってください
esm
We build applications which work well and make customers happy.
http://www.esm.co.jp
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away