CODEWITHSUNDEEP
ruby-on-railsrubydatabaseactiverecord
thisismydesign
thisismydesign

Reputation: 25122

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

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:

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?

Upvotes: 9

Views: 987

Answers (1)

thisismydesign
thisismydesign

Reputation: 25122

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)

Upvotes: 1

Related Questions