Advanced Rails Active Record Query

Question

Calling Active Record query experts! I'm having problem debugging an N+1 query. Here's a summary of my models and the page that's causing the issue. I'd love to find a good way to create a query for this page, and figure out the best way to cache this page. Any help is much appreciated!!

class Brief < ApplicationRecord

  belongs_to :account
  has_one :user, through: :account
  has_many :submissions

class Account < ApplicationRecord

  belongs_to :user, touch: true
  has_many :briefs
end

class Submission < ApplicationRecord

  belongs_to :brief, touch: true
  belongs_to :user

I have a page of @briefs where I'm looking to display for each brief:

Brief account name -> brief.account.name
Brief's submission status for the current_user -> brief.submissions.where(user: current_user).status ** Note this could return nil if a User has not created a Submission for a Brief


class BriefsController < BaseController
  def index
    @briefs = Brief.includes([:account, :submissions]).order(expiration_date: :desc).live
  end


class Brief < ApplicationRecord
 ### CLASS METHODS
 def self.live
    Brief.all.select { |brief| brief.active? == true }
  end

Currently this is how I'm caching the page:

live_briefs.html.erb

<% cache @briefs, expires_in: 1.hour do %> 
  <% @briefs.each do |brief| %>
    <% cache brief do %>
      <%= render 'live_briefs/components/brief', brief: brief %>
    <% end %>
  <% end %>
<% end %>

_.brief.html.erb

<%= brief.account.name %>
<%= live_brief_submission_status(brief, current_user) %>

brief_helper.rb (this is what's causing the added queries)

def live_brief_submission_status(brief, user)
    submission = brief.submissions.where(user: user).first
    if submission.present?
      case submission.status
      when "approved"
        tag.div "Approved", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-indigo-100 text-indigo-800"
      when "invited"
        tag.div "Invited", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-green-400 text-white"
      when "pending"
        tag.div "Pending", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-gray-100 text-gray-800"
      when "declined"
        tag.div "Declined", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-red-100 text-red-800"
      else
      end
    else
      tag.div "Apply Now!", class: "inline-flex items-center pl-3 py-0.5 rounded-full text-sm font-medium leading-5 text-gray-800"
    end
  end

And this is the output when I load the page:

Started GET "/briefs/live" for ::1 at 2020-06-01 15:34:11 -0700
Processing by LiveBriefsController#index as HTML
  User Load (5.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 1], ["LIMIT", 1]]
  Brief Load (10.8ms)  SELECT "briefs".* FROM "briefs" ORDER BY "briefs"."expiration_date" DESC
  ↳ app/models/brief.rb:32:in `live'
  Account Load (7.5ms)  SELECT "accounts".* FROM "accounts" WHERE "accounts"."id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)  [["id", 10], ["id", 4], ["id", 9], ["id", 3], ["id", 6], ["id", 5], ["id", 7], ["id", 2], ["id", 1], ["id", 8]]
  ↳ app/models/brief.rb:32:in `live'
  Submission Load (46.7ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30)  [["brief_id", 27], ["brief_id", 9], ["brief_id", 21], ["brief_id", 22], ["brief_id", 10], ["brief_id", 29], ["brief_id", 24], ["brief_id", 3], ["brief_id", 1], ["brief_id", 6], ["brief_id", 25], ["brief_id", 7], ["brief_id", 2], ["brief_id", 23], ["brief_id", 5], ["brief_id", 28], ["brief_id", 26], ["brief_id", 30], ["brief_id", 8], ["brief_id", 4], ["brief_id", 14], ["brief_id", 17], ["brief_id", 16], ["brief_id", 11], ["brief_id", 13], ["brief_id", 20], ["brief_id", 12], ["brief_id", 15], ["brief_id", 19], ["brief_id", 18]]
  ↳ app/models/brief.rb:32:in `live'
  Rendering layouts/application.html.erb
  Rendering live_briefs/index.html.erb within layouts/application
  Rendered live_briefs/components/_title.html.erb (Duration: 0.1ms | Allocations: 55)
  Submission Load (1.2ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 9], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 32.0ms | Allocations: 3088)
  Submission Load (3.8ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 10], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 11.7ms | Allocations: 2119)
  Submission Load (27.0ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 3], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 49.2ms | Allocations: 2028)
  Submission Load (26.0ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 1], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 34.2ms | Allocations: 2034)
  Submission Load (0.7ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 6], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 8.2ms | Allocations: 2028)
  Submission Load (0.9ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 7], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 12.0ms | Allocations: 2028)
  Submission Load (12.5ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 2], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 18.2ms | Allocations: 2027)
  Submission Load (136.9ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 5], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 157.4ms | Allocations: 2405)
  Submission Load (168.4ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 8], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 177.6ms | Allocations: 2035)
  Submission Load (31.7ms)  SELECT "submissions".* FROM "submissions" WHERE "submissions"."brief_id" = $1 AND "submissions"."user_id" = $2 ORDER BY "submissions"."id" ASC LIMIT $3  [["brief_id", 4], ["user_id", 1], ["LIMIT", 1]]
  ↳ app/helpers/brief_helper.rb:20:in `live_brief_submission_status'
  Rendered live_briefs/components/_brief.html.erb (Duration: 39.5ms | Allocations: 2029)
  Rendered live_briefs/index.html.erb within layouts/application (Duration: 581.8ms | Allocations: 23083)
[Webpacker] Everything's up-to-date. Nothing to do
  Rendered layouts/_sidebar.html.erb (Duration: 9.3ms | Allocations: 1433)
  Rendered layouts/_notifications.html.erb (Duration: 1.8ms | Allocations: 86)
  Rendered application/_css_overrides.html.erb (Duration: 0.2ms | Allocations: 56)
  Rendered layouts/application.html.erb (Duration: 631.4ms | Allocations: 28281)
Completed 200 OK in 994ms (Views: 227.1ms | ActiveRecord: 484.8ms | Allocations: 93326)

max · Accepted Answer

First make sure the method is chainable by calling it on self so that you can add it to an existing relation:

class Brief < ApplicationRecord
  def self.live
   where(active: true)
  end
end

Then make sure you eager load the needed data in the controller:

@briefs = Brief.live
               .eager_load(submissions: :user)
               .order(expiration_date: :desc)

Instead of using .where which will always create a query use the methods from enumerable to traverse the association which is loaded in memory:

def live_brief_submission_status(brief, user)
  submission = brief.submissions.detect {|s| s.user = user }
  if submission.present?
    case submission.status
    when "approved"
      tag.div "Approved", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-indigo-100 text-indigo-800"
    when "invited"
      tag.div "Invited", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-green-400 text-white"
    when "pending"
      tag.div "Pending", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-gray-100 text-gray-800"
    when "declined"
      tag.div "Declined", class: "inline-flex items-center px-3 py-0.5 rounded-full text-sm font-medium leading-5 bg-red-100 text-red-800"
    end
  else
    tag.div "Apply Now!", class: "inline-flex items-center pl-3 py-0.5 rounded-full text-sm font-medium leading-5 text-gray-800"
  end
end

But that query should really be refactored out of the helper method to reduce its cyclic complexity and redestribute the responsibilites.

Advanced Rails Active Record Query

Answers (1)

Related Questions