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…
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
You can get the code on: Tool to run the examples for the 5 ways to fetch the latest-n-of-each record
You can use it to run the examples and play with the code and seed values, to pick the right solution for you current case.
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
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
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
You can also represent the latest comments as an association…
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
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
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 %>
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 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.
Here are some useful articles if you want to learn more about window functions and how to use them in rails.
Window functions - If you want to learn how they work in postgres, this is a link to the docs.
ActiveRecord Eager Loading with Query Objects and Decorators - A post from Thoughtbot with a proposal to use query objects, decorators and window functions to model this problem.
Demo: “Last N of each” query with PostgreSQL and Rails ActiveRecord - A demo from vovimayhem to help you learn how to translate the SQL for the window functions to Arel and Rails.
Here are some useful articles if you want to learn more about the lateral join in postgres.
Lateral queries - If you want to learn how they work in postgres, this is a link to the docs.
Group by limit per group in PostgreSQL - A post that help me understand how you can use the lateral join in a “top-n-per-group” query
Use lateral join when finding latest City status - A pull request with a refactor from window functions to lateral join
Learn just enough fundamentals to be fluent preloading associations with ActiveRecord, and start helping your team to avoid n+1 queries on production.