Imagine that you need to put the number of likes for each post in a list, but avoiding n+1 queries.
posts.each do |post|
post.likes.count # n+1 queries
end
One way to avoid n+1 queries here, is to preload the association and the count the records in ruby. But if the association has too many records, you could introduce a performance problem.
posts.preload(:likes).each do |post|
post.likes.length
end
To solve it you can try to preload just the counts, here are four ways of doing it (and a bonus).
You will need to…
Here the example with posts and likes:
# Given a list of posts
posts = Post.limit(5)
# 1. Filter the likes for those posts
likes = Like.where(post_id: posts)
# 2. Group by :post_id and count
counts = likes.group(:post_id).count
puts counts
posts.each do |post|
# 3. Find the likes count for the current post
# 4. Fallback to zero if no count
puts "Post: #{post.id}, likes: #{counts[post.id] || 0}"
end
# Like Count (0.8ms) SELECT COUNT(*) AS "count_all", "likes"."post_id" AS "likes_post_id" FROM "likes" WHERE "likes"."post_id" IN (SELECT "posts"."id" FROM "posts" LIMIT $1) GROUP BY "likes"."post_id" [["LIMIT", 5]]
# {3=>20, 2=>20, 4=>20}
# Post Load (0.2ms) SELECT "posts".* FROM "posts" LIMIT $1 [["LIMIT", 5]]
# Post: 1, likes: 0
# Post: 2, likes: 20
# Post: 3, likes: 20
# Post: 4, likes: 20
# Post: 5, likes: 0
You will need to…
Here the example with posts and likes:
# Given a list of posts
posts = Post.limit(5)
# 1. Join the likes
# 2. Group by "posts.id" and count "likes.id"
counts = posts.joins(:likes).group("posts.id").count("likes.id")
puts counts
posts.each do |post|
# 3. Find the likes count for the current post
# 4. Fallback to zero if no count
puts "Post: #{post.id}, likes: #{counts[post.id] || 0}"
end
# Post Count (1.0ms) SELECT COUNT("likes"."id") AS "count_likes_id", "posts"."id" AS "posts_id" FROM "posts" INNER JOIN "likes" ON "likes"."post_id" = "posts"."id" GROUP BY "posts"."id" LIMIT $1 [["LIMIT", 5]]
# {2=>20, 3=>20, 4=>20, 8=>20}
# Post Load (0.3ms) SELECT "posts".* FROM "posts" LIMIT $1 [["LIMIT", 5]]
# Post: 1, likes: 0
# Post: 2, likes: 20
# Post: 3, likes: 20
# Post: 4, likes: 20
# Post: 5, likes: 0
You will need to…
You don’t need a fallback to zero, because the left join will return a count for all posts.
Here the example with posts and likes:
# Given a list of posts
posts = Post.limit(5)
# 1. Left join the likes
# 2. Group by "posts.id" and count "likes.id"
counts = posts.left_joins(:likes).group("posts.id").count("likes.id")
puts counts
posts.each do |post|
# 3. Find the likes count for the current post
# You don't need fallback
puts "Post: #{post.id}, likes: #{counts[post.id]}"
end
# Post Count (0.4ms) SELECT COUNT("likes"."id") AS "count_likes_id", "posts"."id" AS "posts_id" FROM "posts" LEFT OUTER JOIN "likes" ON "likes"."post_id" = "posts"."id" GROUP BY "posts"."id" LIMIT $1 [["LIMIT", 5]]
# {1=>0, 2=>20, 3=>20, 4=>20, 5=>0}
# Post Load (0.2ms) SELECT "posts".* FROM "posts" LIMIT $1 [["LIMIT", 5]]
# Post: 1, likes: 0
# Post: 2, likes: 20
# Post: 3, likes: 20
# Post: 4, likes: 20
# Post: 5, likes: 0
You will need to…
likes_count
)You don’t need a fallback to zero, because the left join will return a count for all posts.
Here the example with posts and likes:
# Given a list of posts
posts = Post.limit(5)
# 1. Join the likes
# 2. Select the posts and the likes count
# 3. Group by posts.id
posts = posts
.left_joins(:likes)
.select("posts.*, COUNT(likes.id) AS likes_count")
.group("posts.id")
posts.each do |post|
# 4. Use post.likes_count
# You don't need fallback
puts "Post: #{post.id}, likes: #{post.likes_count}"
end
# Post Load (1.0ms) SELECT posts.*, COUNT(likes.id) AS likes_count FROM "posts" LEFT OUTER JOIN "likes" ON "likes"."post_id" = "posts"."id" GROUP BY "posts"."id" LIMIT $1 [["LIMIT", 5]]
# Post: 1, likes: 0
# Post: 2, likes: 20
# Post: 3, likes: 20
# Post: 4, likes: 20
# Post: 5, likes: 0
If you don’t want to have this code in the view or controller, you can write a preload object to do it. It could be something like this:
class LikesCounts
attr_reader :posts
def initialize(posts)
@posts = posts
end
def [](post)
# 3. Find the likes count for the current post
# 4. Fallback to zero if no count
counts[post.id] || 0
end
def counts
# 1. Filter the likes for those posts
# 2. Group by :post_id and count
@counts ||= Like.where(post_id: posts).group(:post_id).count
end
end
posts = Post.limit(5)
counts = LikesCounts.new(posts)
posts.each do |post|
puts "Post: #{post.id}, likes: #{counts[post]}"
end
# Post Load (0.7ms) SELECT "posts".* FROM "posts" LIMIT $1 [["LIMIT", 5]]
# Like Count (0.4ms) SELECT COUNT(*) AS "count_all", "likes"."post_id" AS "likes_post_id" FROM "likes" WHERE "likes"."post_id" IN (SELECT "posts"."id" FROM "posts" LIMIT $1) GROUP BY "likes"."post_id" [["LIMIT", 5]]
# Post: 1, likes: 0
# Post: 2, likes: 20
# Post: 3, likes: 20
# Post: 4, likes: 20
# Post: 5, likes: 0
Making things work isn't enough for you any more? Now you need to consider performance and scalability?
... But you normally have troubles fixing n+1 queries and trying to find why active record is ignoring your "includes"?
Are you are worried because you feel unqualified to tackle tasks with complex data models?
Sign up to learn how to fix n+1 queries on Rails