N+1 in has_many :through

Artem G Source

I ran into problem N + 1 in association :

class Category < ApplicationRecord
  has_many :categories_designs, dependent: :destroy
  has_many :designs, through: :categories_designs
  has_many :templates, ->{ where(is_template: true) }, through: :categories_designs, class_name: 'Design', source: :design

  def marked_designs_as_new?
    designs.select(:mark_design_as_new_until).where("mark_design_as_new_until >= ?", Time.now.in_time_zone.beginning_of_day).exists?
  end
end

And I want to use the marked_designs_as_new? method in the view.

- @categories.each do |category|
  = category.title.titleize
  - if category.marked_designs_as_new?
    .design-type-marked
      NEW

In my controller I call:

@categories = Category.includes(categories_designs: :design).visible

And I'm faced with the problem of N + 1.

Category Load (0.4ms)  SELECT  "categories".* FROM "categories" WHERE "categories"."hidden" = $1 ORDER BY "categories"."position" ASC LIMIT $2 OFFSET $3  [["hidden", false], ["LIMIT", 100], ["OFFSET", 0]]
  CategoriesDesign Load (0.4ms)  SELECT "categories_designs".* FROM "categories_designs" WHERE "categories_designs"."category_id" IN (1, 3, 4, 5, 6, 7, 8)
  Design Load (0.5ms)  SELECT "designs".* FROM "designs" WHERE "designs"."id" IN (1, 4, 3, 6)
   (0.7ms)  SELECT COUNT(*) FROM "designs" INNER JOIN "categories_designs" ON "designs"."id" = "categories_designs"."design_id" WHERE "categories_designs"."category_id" = $1 AND "designs"."is_template" = $2  [["category_id", 1], ["is_template", true]]
  Design Exists (0.7ms)  SELECT  1 AS one FROM "designs" INNER JOIN "categories_designs" ON "designs"."id" = "categories_designs"."design_id" WHERE "categories_designs"."category_id" = $1 AND (mark_design_as_new_until >= '2018-03-13 00:00:00') LIMIT $2  [["category_id", 1], ["LIMIT", 1]]
   (0.5ms)  SELECT COUNT(*) FROM "designs" INNER JOIN "categories_designs" ON "designs"."id" = "categories_designs"."design_id" WHERE "categories_designs"."category_id" = $1 AND "designs"."is_template" = $2  [["category_id", 3], ["is_template", true]]
............. etc.

why?

ruby-on-railsruby-on-rails-5

Answers

answered 3 months ago Kox #1

Ok, your .select(:mark_design_as_new_until) performs another query to the database. What you should do is use an array select method in the following way:

.select(&:mark_design_as_new_until)

This gives you an array of designs loaded in the memory on which you can perform .any? method to check your condition:

.select(&:mark_design_as_new_until).any? { |design| design.mark_design_as_new_until >= Time.now.in_time_zone.beginning_of_day }

And of course, include designs in your Category.

Category.includes(:designs, ...)

answered 3 months ago Pragash #2

Did you try Category.includes([:categories_designs, :design]) Also, you can change the marked_designs_as_new? method as follows,

def marked_designs_as_new?
  designs.select{ |x| x.marked_designs_as_new? }.any?
end

design.rb

class Design
  def marked_designs_as_new?
    mark_design_as_new_until >= Time.now.in_time_zone.beginning_of_day
  end
end

comments powered by Disqus