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).

Method 1: Count from the associated model

You will need to…

1. Filter the associated models for the records on your list
2. Group and count by the foreign_key to obtain a hash with the form { id ⇒ count }
3. Use that hash on the list to get the count for each record
4. Fallback to zero if no count

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

Method 2: Count joining the associated model

You will need to…

1. Join the association
2. Group by the id of the base table and count the ids of the associated records to obtain a hash with the form { id ⇒ count }
3. Use that hash on the list to get the count for each record
4. Fallback to zero if no count

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

Method 3: Count left joining the associated model

You will need to…

1. Left join the association
2. Group by the id of the base table and count the ids of the associated records to obtain a hash with the form { id ⇒ count }
3. Use that hash on the list to get the count for each record

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

Method 4: Add the count to the selected values

You will need to…

1. Left join the association
2. Select the records table and the associations count
3. Group by the id of the base table
4. Use the associations count as a method in the records (Ej. 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

Bonus: Use a preload object with one of the first three methods

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

Related articles

• Fetching the top n per group with window functions
• 5 ways to fetch the latest-N-of-each record on Rails
• Benchmarks for the fixes to the latest-comment n+1 problem
• Why some people say that fixing some n+1 queries could hurt performance?