Reduce Your SQL Queries With a Counter Cache

BY: Charlie Massry


Counter caching is a technique to reduce the number or SQL queries when attempting to call count on a resources' association. For example, on my blog, I have posts which have many comments, and on the index page, I am displaying the number of comments on each post. Originally it was firing off ten SQL queries for this plus one for all the posts, but I was able to reduce it to just one because I implemented a counter cache.


This past weekend I taught at Railsbridge and towards the end the students seemed very enthusiastic about implementing this counter cache in order to add sorting functionality and performance. The app was called Suggestotron which was a simple app that lets a user add a topic such as "fruit" and then the user can vote on it and any other topics. While this app was simple, we were able to finish early and begin the extra suggested exercises. One was to sort the topics by the votes count. One idea that was tossed around was to implement a counter cache on the association. To add a counter cache to the Topic model we would need to generate a migration.



rails generate migration AddVotesCountToTopic votes_count:integer


This generates a migration with the appropriate timestamp.



class AddVotesCountToTopic < ActiveRecord::Migration


def change


add_column :topics, :votes_count, :integer


end


end



This will add the right column, but if we think about it, this is not exactly what we want. Two problems come to mind. First, the column will default to nil which is bad as we can't perform an operation like nil + 1. If we do, we will get something like NoMethodError: undefined method '+' for nil:NilClass. Ideally we'd like to start a zero. Also we would like to update the column for records that already have votes on them. To do this we will have to execute Ruby code to take the result of topic.votes.count and set it to topic.votes_count. We can do this with an iterator like #each



class AddVotesCountToTopic < ActiveRecord::Migration


def change


add_column :topics, :votes_count, :integer, default: 0



topics = Topic.all



topics.each do topic


topic.update!(votes_count: topic.votes.count)


end


end


end



The code default: 0 will automatically set the votes_count column to 0 instead of nil, and the iterator will make sure votes_count accurately reflects the number of votes. Also we want to use #update! instead of #update because it will raise an error if it fails and undo the migration instead of a half finished migration.



Now you can run that migration.


$ rake db:migrate



Now that you have your database set up, how will Rails know to increment the counter every time? You could add it into your controller on vote creation, or you can use this belongs_to option, counter_cache: true. In the model that is on the belongs_to end of the relationship, in this case Votes, we can add the counter cache association.



class Vote < ActiveRecord::Base


belongs_to :topic, counter_cache: true


end



Now every time the Topic receives a vote it increments the votes_count column. Also if you were to use the destroy method, it would decrement the votes_count column. Now we are ready to sort by votes.



Adding the code for this is now easier and more efficient. In the controller to display topics by the number of votes, we can change the index method to finalize this feature.



class TopicsController < ApplicationController


def index


@topics = Topic.order(votes_count: :desc)


end


end



Now that your topics have their votes counted and ordered, you can display them in the view using embedded Ruby.



Charlie Massry


Current Company & Position: Ondo Corp., Software Engineer


Location: New York, NY


Favorite piece of technology: My iPhone


Favorite band: Motion City Soundtrack


Favorite Color: Red


Twitter Handle: @charliemassry


Website: http://www.charliemassry.com/