Benito Serna Tips and tools for Ruby on Rails developers

Mistakes we make with counts in rails

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.

1. Using slow sql counts in views

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 %>

2. Count via sql an association you will use later

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

3. N+1 queries for counting associations inside a list

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:

4. Preload associations for counting inside a list

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:

5. Forget fragment caching

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.

Related articles

Do you want to solve n+1 queries with confidence?

Sign up to download free ebook, where I will show the basics on how to deal with n+1 problems.

  • Understand the methods (joins, includes, etc...) to work with associations.
  • Identify when active record will execute a query.
  • And the tools that can help you detect n+1 queries (like the bullet gem).