Preventing N+1 in Rails query with associated model

Question

I have these models:

class KlassOccurrence < ApplicationRecord
  belongs_to :klass
end

class Klass < ApplicationRecord
  has_many :klass_occurrences
  belongs_to :user

  def next_occurrence_date
    self.klass_occurrences.order(scheduled_at: :desc).first
  end
end

And I have this in a controller:

@klasses = Klass.includes(:klass_occurrences).where(user_id: current_user.id)

And in a view I have this:

   <% @klasses.each do |klass| %>
      <tr>
        <td><%= klass.next_occurrence_date %></td>
      </tr>
    <% end %>

I would expect that because of the includes(:klass_occurrences) I wouldn't be seeing an N+1, but this is what I see in the logs:

User Load (0.4ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 3], ["LIMIT", 1]]
  ↳ app/controllers/klasses_controller.rb:4:in `index'
  Rendering klasses/index.html.erb within layouts/dashboard_layout
  Klass Load (0.2ms)  SELECT "klasses".* FROM "klasses" WHERE "klasses"."user_id" = $1  [["user_id", 3]]
  ↳ app/views/klasses/index.html.erb:13
  KlassOccurrence Load (4.3ms)  SELECT "klass_occurrences".* FROM "klass_occurrences" WHERE "klass_occurrences"."klass_id" IN ($1, $2)  [["klass_id", 1], ["klass_id", 5]]
  ↳ app/views/klasses/index.html.erb:13
  KlassOccurrence Load (1.8ms)  SELECT "klass_occurrences".* FROM "klass_occurrences" WHERE "klass_occurrences"."klass_id" = $1 ORDER BY "klass_occurrences"."scheduled_at" DESC LIMIT $2  [["klass_id", 1], ["LIMIT", 1]]
  ↳ app/models/klass.rb:6:in `next_occurrence_date'
  KlassOccurrence Load (0.3ms)  SELECT "klass_occurrences".* FROM "klass_occurrences" WHERE "klass_occurrences"."klass_id" = $1 ORDER BY "klass_occurrences"."scheduled_at" DESC LIMIT $2  [["klass_id", 5], ["LIMIT", 1]]
  ↳ app/models/klass.rb:6:in `next_occurrence_date'

Why is it doing three queries for klass occurrences? I would expect just one query to load all the occurrences for all the klasses, but then I see two extra queries.

You're doing some ordering in `next_occurrence_date` and Rails is calling these selects from there. Calling `klass.occurrences` would not make extra calls — Eyeslandic, May 14 '20 at 17:41
Besides the point that @Eyeslandic makes `.includes` actually is lazy-loading (well sort of a least) and the result is usually two queries. This lets it try to be smart and just load the associated records when needed. If you for example use `.refereces(:klass_occurrences)` .includes will actually defer to `.eager_load` which always does one query. https://scoutapm.com/blog/activerecord-includes-vs-joins-vs-preload-vs-eager_load-when-and-where — max, May 14 '20 at 17:42
This is actually a pretty common scenario and you can see quite a lot of different solutions on https://stackoverflow.com/questions/60551675/how-do-i-build-a-query-in-ruby-on-rails-that-joins-on-the-max-of-a-has-many-rela/60568914 — max, May 14 '20 at 17:55

max · Accepted Answer · 2020-05-14T18:32:31.923

1

If you just need the highest value of that column you can also just select an aggregate off the join table:

class Klass < ApplicationRecord
  has_many :klass_occurrences
  belongs_to :user

  def self.with_next_occurrence_date
    self.select(
         'klass.*',
         'MAX(klass_occurrences.scheduled_at) AS next_occurrence_date'
        )
        .left_joins(:klass_occurrences)
        .group(:id)
  end 
end

@klasses = Klass.with_next_occurrence_date.where(user_id: current_user.id)

<% @klasses.each do |klass| %>
  <tr>
    <td><%= klass.next_occurrence_date %></td>
  </tr>
<% end %>

If you really need a model object you can do it by sorting the records in Ruby:

class Klass < ApplicationRecord
  has_many :klass_occurrences
  belongs_to :user
  def next_occurrence_date
    self.klass_occurrences.sort(&:scheduled_at).last
  end
end

And there are also a bunch of novel tricks to get ActiveRecord to load just one record off the association that are somewhat out of scope for this question.

edited May 14 '20 at 18:32

answered May 14 '20 at 18:26

max

96,212
14
104
165

This, but just because I abhor SQL strings in ActiveRecord. `Klass.arel_attribute(Arel.star),KlassOccurence.arel_attribute(:scheduled_at).max.as(:next_occurrence_date)` and `self.klass_occurrences.sort(&:scheduled_at).last` can be `self.klass_occurrences.order(scheduled_at: :desc).limit(1)` rather than loading all the records – engineersmnky May 14 '20 at 18:59
I would agree with you @engineersmnky but SQL strings make it a lot easier to show what's going on this kind of example. I'm kind of split on the topic - Arel can help clean up messy queries but it can also just make it much harder to read. – max May 14 '20 at 19:03
but far more dynamic and sanitary (not really applicable in this context though). – engineersmnky May 14 '20 at 19:04
@engineersmnky also the reason I used `sort` is because it avoids the n+1 query. `self.klass_occurrences.order(scheduled_at: :desc).limit(1)` will just result in the exact same queries as in the question. Yes its really hacky but if the number of assocatied items is small it will do. – max May 14 '20 at 19:24
Very true I guess it just depends on the number of records if load and sort is better than short hit queries. There are definitely a lot of more performant options that fall into *"And there are also a bunch of novel tricks to get ActiveRecord to load just one record off the association that are somewhat out of scope for this question."* including scopes and complex joins (which is where Arel really shines) but I can concede that this is definitely not the place to start addressing those. – engineersmnky May 14 '20 at 19:37

Preventing N+1 in Rails query with associated model

1 Answers1