Eager loading with scope in Rails

Question

I found many questions with similar title but none of them could solve my question.

I have a model Program which has many Videos:

class Program < ActiveRecord::Base
  has_many :videos
  ...
end

Then I have scope in Video:

class Video < ActiveRecord::Base
  belongs_to :program

  scope :trailer, -> { where(video_type: 0) }
  ...
end

Firstly, when I have a list of programs and want to access videos, I have no N+1 program with include method:

> @programs.includes(:videos).map { |p| p.videos.size }
  Program Load (0.6ms)  SELECT  "programs".* FROM "programs"  ORDER BY "programs"."id" ASC LIMIT 10
  Video Load (0.5ms)  SELECT "videos".* FROM "videos" WHERE "videos"."program_id" IN (8, 9, 10, 11, 12, 13, 14, 15, 16, 17)

However, when I try to get the scope, it will touch Database again:

> @programs.includes(:videos).map { |p| p.videos.trailer }
  Program Load (0.6ms)  SELECT  "programs".* FROM "programs"  ORDER BY "programs"."id" ASC LIMIT 10
  Video Load (0.5ms)  SELECT "videos".* FROM "videos" WHERE "videos"."program_id" IN (8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
  Video Load (0.4ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 8], ["video_type", 0]]
  Video Load (0.4ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 9], ["video_type", 0]]
  Video Load (12.4ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 10], ["video_type", 0]]
  Video Load (0.3ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 11], ["video_type", 0]]
  Video Load (0.3ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 12], ["video_type", 0]]
  Video Load (0.3ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 13], ["video_type", 0]]
  Video Load (0.3ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 14], ["video_type", 0]]
  Video Load (0.3ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 15], ["video_type", 0]]
  Video Load (0.4ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 16], ["video_type", 0]]
  Video Load (0.4ms)  SELECT  "videos".* FROM "videos" WHERE "videos"."program_id" = $1 AND "videos"."video_type" = $2  ORDER BY "videos"."id" ASC LIMIT 1  [["program_id", 17], ["video_type", 0]]

You can see it will load DB many times which lead to a bad performance.

#<Benchmark::Tms:0x007f95faa8fab0 @label="", @real=0.02663199999369681, @cstime=0.0, @cutime=0.0, @stime=0.0, @utime=0.019999999999999574, @total=0.019999999999999574>

---

One solution I can think of is to convert videos to array and search the array:

> @programs.includes(:videos).map { |program| program.videos.to_ary.select { |v| v.video_type == 0 } }
  Program Load (0.5ms)  SELECT "programs".* FROM "programs" WHERE "programs"."id" IN (8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
  Video Load (0.4ms)  SELECT "videos".* FROM "videos" WHERE "videos"."program_id" IN (17, 16, 13, 12, 11, 9, 8, 15, 14, 10)

The performance is better but the code is complex.

#<Benchmark::Tms:0x007f95faac8720 @label="", @real=0.006901999993715435, @cstime=0.0, @cutime=0.0, @stime=0.0, @utime=0.010000000000000675, @total=0.010000000000000675>

---

Another solution I can think of, is to add a new has_many in Program for scope:

class Program < ActiveRecord::Base
  has_many :videos
  has_many :trailer_videos, -> { where(video_type: 0) }, class: 'Video'
  ...
end

Then if I includes and call the new relation directly, it will eager load as well.

> @programs.includes(:trailer_videos).map { |program| program.trailer_videos }
  Program Load (0.5ms)  SELECT "programs".* FROM "programs" WHERE "programs"."id" IN (8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
  Video Load (0.3ms)  SELECT "videos".* FROM "videos" WHERE "videos"."video_type" = $1 AND "videos"."program_id" IN (17, 16, 13, 12, 11, 9, 8, 15, 14, 10)  [["video_type", 0]]

The benchmark is below, which is super fast:

#<Benchmark::Tms:0x007f95fdea96c0 @label="", @real=0.004801000002771616, @cstime=0.0, @cutime=0.0, @stime=0.0, @utime=0.009999999999999787, @total=0.009999999999999787>

However, in this way, it will make the Program model so heavy. Because for each scope in Video, I need to added a related association in Program.

---

Therefore, I am looking for a better solution, which will keep the scope logic inside of Video, but without N+1 problem.

Cheers

Answer 1

As I said, IMO your approach of adding the has_many :trailer_videos, -> { where(video_type: 0) }, class: 'Video' is the simple and best way to counter your problem. I don't see any drawback in adding more such associations to the model.

Answer 2

A solution is to use merge with eager_load:

@programs.eager_load(:videos).merge(Video.trailer).map { |p| p.videos.size }

It produces only one query.