Benito Serna
Ruby/Rails, TDD, Software...

5 ways to fetch the latest-N-of-each record on Rails

A common cause of n+1 queries is fetching the “latest-N-of-each” record on a list of records.

Some examples of this problem are trying to get…

The problem is that once you have detected the n+1 queries, is not that easy to avoid them. Here I will show you 5 ways to solve this problem avoiding the n+1 queries.

But first, let’s explain the problem with an example…

Example problem

Fetch the latest 3 comments for each post

Imagine, that you have a model like this…

class Post < ActiveRecord::Base
  has_many :comments

  def latest_comments
    comments.order(id: :desc).limit(3)
  end
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

How do you render the latest comments for each post, without n+1 queries?…

Post.all.each do |post|
  post.latest_comments.each do |comment|
    puts comment.body
  end
end

… you can’t just includes(:comments) and call latest_comments, because active record will “ignore your includes”.

Well, here are 5 ways to do it…

Note: If you want to fetch just the “latest-for-each”. Maybe you want to take a look to this post:
5 ways to fix the latest-comment n+1 problem

1. An object to fetch all and select just the latest N

You can create a new object to fetch all the comments, group them by post and then return just the latest 3 comments for each post.

class Post < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

class LatestCommentsForPosts
  def initialize(posts)
    @comments = scope_for(posts).group_by(&:post_id)
  end

  def for_post(post)
    (@comments[post.id] || []).first(3)
  end

  private

  def scope_for(posts)
    Comment.order(id: :desc).where(post_id: posts)
  end
end

posts = Post.limit(10)
comments = LatestCommentsForPosts.new(posts)

posts.each do |post|
  comments.for_post(post).each do |comment|
    puts comment.body
  end
end

2. An object to fetch just the latest N for each record

Using window functions

If fetching all the comments for each post is causing you memory or performance problems, you can use “Window functions” to ask the database for just the latest 3 comments for each post, group them by post and then return the comments for each post.

class Post < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

class LatestCommentsForPosts
  def initialize(posts)
    @comments = scope_for(posts).group_by(&:post_id)
  end

  def for_post(post)
    @comments[post.id] || []
  end

  private

  def scope_for(posts)
    ranked_comments = Comment.where(post_id: posts).select(<<-SQL)
      comments.*,
      dense_rank() OVER (
        PARTITION BY comments.post_id
        ORDER BY comments.id DESC
      ) AS comment_rank
    SQL

    Comment.from(ranked_comments, "comments").where("comment_rank <= 3")
  end
end

posts = Post.limit(10)
comments = LatestCommentsForPosts.new(posts)

posts.each do |post|
  comments.for_post(post).each do |comment|
    puts comment.body
  end
end

Using lateral join

Other approach that you could try is using a “Lateral join” to fetch just the latest 3 comments for each post, group them by post and then return the comments for each post.

class Post < ActiveRecord::Base
  has_many :comments
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

class LatestCommentsForPosts
  def initialize(posts)
    @comments = scope_for(posts).group_by(&:post_id)
  end

  def for_post(post)
    @comments[post.id] || []
  end

  private

  def scope_for(posts)
    latest_comments = Post
      .select("latest_comments.*")
      .where(id: posts)
      .joins(<<-SQL)
        JOIN LATERAL (
          SELECT * FROM comments
          WHERE post_id = posts.id
          ORDER BY id DESC LIMIT 3
        ) AS latest_comments ON TRUE
      SQL

    Comment.from(latest_comments, "comments").order(id: :desc)
  end
end

posts = Post.limit(10)
comments = LatestCommentsForPosts.new(posts)

posts.each do |post|
  comments.for_post(post).each do |comment|
    puts comment.body
  end
end

3. An association to fetch just the latest N for each record

You can also represent the latest comments as an association…

Using window functions

You can use “"Window functions” to build a scope for just the latest 3 comments for each post, and pass that scope to the association’s definition.

class Post < ActiveRecord::Base
  has_many :comments
  has_many :latest_comments,
    -> { Comment.latest_comments_for_posts },
    class_name: "Comment"
end

class Comment < ActiveRecord::Base
  belongs_to :post

  def self.latest_comments_for_posts
    ranked_comments = Comment.select(<<-SQL)
      comments.*,
      dense_rank() OVER (
        PARTITION BY comments.post_id
        ORDER BY comments.id DESC
      ) AS comment_rank
    SQL

    from(ranked_comments, "comments").where("comment_rank <= 3")
  end
end


posts = Post.limit(10).includes(:latest_comments)

posts.each do |post|
  post.latest_comments.each do |comment|
    puts comment.body
  end
end

Using lateral join

You can use a “Lateral join” to build an scope for just the latest 3 comments for each post, and pass that scope to the association’s definition.

class Post < ActiveRecord::Base
  has_many :comments
  has_many :latest_comments,
    -> { Comment.latest_comments_for_posts },
    class_name: "Comment"
end

class Comment < ActiveRecord::Base
  belongs_to :post

  def self.latest_comments_for_posts
    latest_comments = Post
      .select("latest_comments.*")
      .joins(<<-SQL)
        JOIN LATERAL (
          SELECT * FROM comments
          WHERE post_id = posts.id
          ORDER BY id DESC LIMIT 3
        ) AS latest_comments ON TRUE
      SQL

    Comment.from(latest_comments, "comments").order(id: :desc)
  end
end


posts = Post.limit(10).includes(:latest_comments)

posts.each do |post|
  post.latest_comments.each do |comment|
    puts comment.body
  end
end

4. Russian doll caching

If you are already using fragment cache on your app, you can embrace the “Russian doll caching” and think of n+1 as a feature, let the n+1 queries run just the first time and use the cache after that.

class Post < ActiveRecord::Base
  has_many :comments

  def latest_comments
    comments.order(id: :desc).limit(3)
  end
end

class Comment < ActiveRecord::Base
  belongs_to :post
end

In your view…

<% #posts/index.html.erb %>
<%= render partial: 'posts/post', collection: @posts, cached: true %>
<% #posts/_post.html.erb %>
<% cache post %>
  <%= post.title %>
  <% post.latest_comments.each do |comment| %>
    <%= comment.body %>
  <% end %>
<% end %>

5. A new record to cache the latest N for each record

If the other solutions are not enough for you, you can try to add a new record to keep a cache of the latest comments for each post using a has_many :through association and keep the latest comments in sync “manually” (maybe with an after_commit callback).

class Post < ActiveRecord::Base
  has_many :comments
  has_many :latest_comment_caches, class_name: "LatestCommentCache"
  has_many :latest_comments, -> { order(id: :desc) },
    through: :latest_comment_caches,
    source: :comment

  def delete_latest_comment_caches
    latest_comment_caches.delete_all
  end

  def calculate_latest_comments
    comments.order(id: :desc).limit(3)
  end
end

class Comment < ActiveRecord::Base
  belongs_to :post
  after_commit -> { LatestCommentCache.update_for_post(post) }
end

class LatestCommentCache < ActiveRecord::Base
  belongs_to :post
  belongs_to :comment

  def self.update_for_post(post)
    post.delete_latest_comment_caches

    post.calculate_latest_comments.each do |comment|
      create(post: post, comment: comment)
    end
  end
end

posts = Post.limit(10).includes(:latest_comments)

posts.each do |post|
  post.latest_comments.each do |comment|
    puts comment.body
  end
end

Now you can pick one

Now you know 5 difference ways to solve the problem, and you can pick the one that better fits your needs =)

If you are not sure, I think you can use the first one, an object to fetch all and select just the latest N default order, unless your metrics are telling you that you need something different.

More about window functions

Here are some useful articles if you want to learn more about window functions and how to use them in rails.

More about lateral join

Here are some useful articles if you want to learn more about the lateral join in postgres.