Rails: how to eager load limited records of ordered association without N+1 queries

user_2771889 0
Viewed 471

I know there're many questions about some of these topics, but I didn't find one covering all aspects.

Consider User, Activity and Like models. When I query an activity I would like to eager load the first Like for each activity in the collection without making N+1 queries and without loading more than necessary records. My code looks something like this:

class User < ActiveRecord::Base
  has_many :likes, as: :liker
end

class Activity < ActiveRecord::Base
  has_many :likes
  has_one :first_like, -> { order(created_at: :asc) }, class_name: "Like"
end

class Like < ActiveRecord::Base
  belongs_to :activity
  belongs_to :liker, polymorphic: true
end

I made a comprehensive gist to test different loading strategies and methods: https://gist.github.com/thisismydesign/b08ba0ee3c1862ef87effe0e25386267

Strategies: N+1 queries, left outer join, single extra query

Methods: eager_load, includes, includes & references, includes & preload (these will result in either left outer join or single extra query)

Here're the problems I discovered:

  • Left outer join doesn't respect order(created_at: :asc) in the association scope nor default_scope { order(created_at: :asc) } (see: rails issue). It does respect explicit ordering i.e. .order("likes.created_at asc").
  • Left outer join should nevertheless be avoided because it "could result in many rows that contain redundant data and it performs poorly at scale" (see: apidoc rubydoc, rails api). This is a real issue with lots of data even with indexed searches on both sides.
  • Single extra query will create a query without limit, potentially fetching huge amounts of data (see: rails issue)
  • Adding an explicit limit(1) to the association in hope of constraining the single extra query will break things

The preferred method would be a single extra query that only queries the required records. All in all, I couldn't find a native solution with Rails. Is there any?

activerecord ruby-on-rails ruby database
1 Answers

In my question, I'm looking for a native way using Rails. However, here's a solution using SQL and virtual attributes:

class Activity < ApplicationRecord
  has_one :first_like, class_name: "Like", primary_key: :first_like_id, foreign_key: :id

  scope :with_first_like, lambda {
    select(
      "activities.*,
      (
        SELECT like_id as first_like_id
        FROM likes
        WHERE activity_id = activities.id
        ORDER BY created_at ASC
        LIMIT 1
      )"
    )
  }
end

Activity.with_first_like.includes(:first_like)
user_27718890
Related