CODEWITHSUNDEEP
ruby-on-rails
Stephen
Stephen

Reputation: 3992

Eager loading with scope in Rails

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

Upvotes: 12

Views: 5011

Answers (4)

Evmorov
Evmorov

Reputation: 1219

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.

Upvotes: 4

max
max

Reputation: 101911

If the video types are known to the programmer you can use an ActiveRecord::Enum and some simple metaprogrammng to create assocations programatically for each possible value in the enum.

class Video < ActiveRecord::Base
  enum video_type: [:trailer, :promo, :foo, :bar]
end

class Program < ActiveRecord::Base
  # this creates trailer_videos etc assocations
  Video.video_types.each do |key, int| 
    # eval is needed since we need to dynamically create 
    # the lamba for each type
    has_many "#{key}_videos".to_sym, eval "->{ Video.send(#{key}) }"
  end
end

Upvotes: 2

Ilya Konyukhov
Ilya Konyukhov

Reputation: 2781

In Rails Associations have an optional scope parameter that accepts a lambda that is applied to the Relation (see https://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html#method-i-has_many-label-Scopes)

So you could write your models as:

# app/models/video.rb
class Video < ActiveRecord::Base
  belongs_to :program
  scope :trailer, -> { where(video_type: 0) }
  ...
end

# app/models/program.rb
class Program < ActiveRecord::Base
  has_many :videos
  has_many :trailer_videos, -> { trailer }, class: 'Video'
  ...
end

This way you could keep the definition of the scope in Video and reuse it from Program.

Upvotes: 6

Pavan
Pavan

Reputation: 33542

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.

Upvotes: 9

Related Questions