Display the count of an association could look like a simple task, but in some cases it can give you real troubles.
It is very easy to end with n+1 queries. Counting with ruby sometimes can be ok, but sometimes could be impossible. Counting via sql is usually ok, but sometimes could be slow or maybe unnecessary.
Here I will share five mistakes with counts I have made and seen. It could help you to at least be aware of them.
Sometimes counting via SQL using count or size to get a single value or by preloading counts can be slow depending of the number of records you are counting.
For example, imagine you need to put the number of likes in a post and you could have posts with thousands of likes. It could make your page slow.
# Somtimes an SQL count can make your
# page slow when the count is to large
post = Post.find(1)
post.likes.count #...
# Sometimes one count is ok but
# preloading a group of counts can be slow
posts = Post.limit(5)
likes = Like.where(post_id: posts).group(:post_id).count
posts.each do |post|
puts "Post: #{post.id}, likes: #{likes[post.id] || 0}"
end
In those cases you can try a counter cache:
# You can use a counter cache
class Like < ApplicationRecord
belongs_to :post, counter_cache: :true
end
class Post < ApplicationRecord
has_many :likes
end
post = Post.find(1)
post.likes.size #... No query here
Or maybe fragment caching:
<p><%= post.abstract %></p>
<span>Likes: <%= posts.likes.count %></span>
<%= render partial: 'posts/post', collection: posts, cached: true %>
Sometimes in a page we show a count calculated via SQL using count or size, and later in the page we load the full collection we have just counted to display the information.
Something like this:
posts = Post.all
# Count post via sql (1 query)
puts posts.count
# Load posts (other query)
posts.each do |post|
puts post.title
puts post.body
end
In cases like this, you can load and count the collection with length or load.size to avoid making two calls to the database:
posts = Post.all
# Load posts and count with ruby (1 query)
puts posts.load.size
# Posts already loaded (no more queries)
posts.each do |post|
puts post.title
puts post.body
end
I think that this is the most common problem, asking the database for a count inside an each loop.
posts = Post.all
# Load posts (1 query)
posts.each do |post|
# Count comments via sql (1 query per post)
puts post.comments.count
end
To solve it you can try one of these three options:
When you need to put a count inside an each loop and avoid n+1 sometimes we first preload the association and then count the elements in ruby.
posts = Post.preload(:comments)
# Load posts and comments (2 queries)
posts.each do |post|
# Count with ruby (no queries)
puts post.comments.size
end
This could be good enough in a lot of cases, but if the association have too many records and specially if you are not paginating the list, it could cause some performance and memory problems.
To solve it you can also try one of these three options:
If you can enable caching in your project you can also try to avoid the n+1 queries or slow counts in the list with fragment caching.
You can think of n+1 as a feature, let the n+1 queries run just the first time and use the cache after that.
For example if you have a posts/_post partial like this:
<p><%= post.abstract %></p>
<span>Likes: <%= posts.likes.count %></span>
And then in the index view call the partial with something like this:
<%= render partial: 'posts/post', collection: posts, cached: true %>
You will have n+1 queries on the first render, but after that rails will read the cache.
You can go to the Caching with Rails: An Overview to learn more about rails caching.
Learn just enough fundamentals to be fluent preloading associations with ActiveRecord, and start helping your team to avoid n+1 queries on production.