Counter Cache Column in Rails 5

Steps

Step 1

The project model has many tasks.

class Project < ApplicationRecord
  has_many :tasks  
end

The index action in projects controller retrieves all projects.

def index
  @projects = Project.all
end

Step 2

The projects/index.html.erb view lists all the project names and the number of tasks for that project.

<% @projects.each do |project| %>
  <tr>
    <td><%= project.name %></td>
    <td><%= pluralize(project.tasks.size, 'task') %></td>
  </tr>
<% end %>

The pluralize view helper will pluralize the string 'task' if the project tasks size is > 1.

Step 3

Browse to http://localhost:3000/projects.

Started GET "/projects" for ::1 at 2016-03-16 23:47:08 -0700
  ActiveRecord::SchemaMigration Load (0.2ms)  SELECT "schema_migrations".* FROM "schema_migrations"
Processing by ProjectsController#index as HTML
  Project Load (0.1ms)  SELECT "projects".* FROM "projects"
   (0.2ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."project_id" = ?  [["project_id", 1]]
   (0.1ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."project_id" = ?  [["project_id", 2]]
   (0.1ms)  SELECT COUNT(*) FROM "tasks" WHERE "tasks"."project_id" = ?  [["project_id", 3]]
  Rendered projects/index.html.erb within layouts/application (25.4ms)
Completed 200 OK in 202ms (Views: 190.5ms | ActiveRecord: 1.3ms)

You can see three + 1 SQL queries in the development log file. One for projects and three for tasks.

Step 4

We want to reduce count(*) to one query. Eager loading is one way. It retrieves more columns than just the count. This uses more bandwidth and time. Create the migration to add_tasks_count column to the projects table.

rails g migration add_tasks_count

Modify the migration file like this:

class AddTasksCount < ActiveRecord::Migration[5.0]
  def change
    add_column :projects, :tasks_count, :integer, default: 0
  end
end

A new project created will have 0 tasks by default. Migrate the database.

rails db:migrate

Step 5

Create a rake task counter_cache.rake in lib/tasks:

desc 'Counter cache for project has many tasks'

task task_counter: :environment do
  Project.reset_column_information
  Project.pluck(:id).find_each do |p|
    Project.reset_counters p.id, :tasks
  end
end

I have created a rake task for back filling task count for the existing projects in the database. The reset_counters method avoids the readonly error if you use update_attribute. This will update the old projects to have the right task count. From the Rails Guides:

The reset_column_information method resets all the cached information about columns, which will cause them to be reloaded on the next request. The most common usage pattern for this method is probably in a migration, when just after creating a table you want to populate it with some default values.

Step 6

Run task_counter rake task.

rake task_counter

We can check whether the tasks_count has been populated or not in rails console.

p = Project.first
  Project Load (0.2ms)  SELECT  "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT ?  [["LIMIT", 1]]
 => #<Project id: 1, name: "Wealth Building", created_at: "2016-03-16 19:07:26", updated_at: "2016-03-16 20:55:29", tasks_count: 2> 
> Project.last
  Project Load (0.2ms)  SELECT  "projects".* FROM "projects" ORDER BY "projects"."id" DESC LIMIT ?  [["LIMIT", 1]]
 => #<Project id: 3, name: "Cooking", created_at: "2016-03-16 20:06:26", updated_at: "2016-03-16 20:06:26", tasks_count: 0> 

The first project has 2 tasks and the last project has 0 tasks.

Step 7

In schema.rb, you can see the tasks_count column for projects table.

create_table "projects", force: :cascade do |t|
  t.string   "name"
  t.datetime "created_at",              null: false
  t.datetime "updated_at",              null: false
  t.integer  "tasks_count", default: 0
end

Step 8

Change the projects/index.html.erb to use the tasks_count field:

<td><%= pluralize(project.tasks_count, 'task') %></td>

Step 9

Reload project index page and view the log for SQL queries.

Started GET "/projects" for ::1 at 2016-03-16 24:00:00 -0700
Processing by ProjectsController#index as HTML
  Project Load (0.1ms)  SELECT "projects".* FROM "projects"
  Rendered projects/index.html.erb within layouts/application (1.6ms)
Completed 200 OK in 48ms (Views: 45.6ms | ActiveRecord: 0.1ms)

You see that there is only one query now instead of 3+1 queries before using the counter cache.

Step 10

Let's add a new task to the last project in the rails console.

Project.last
  Project Load (0.2ms)  SELECT  "projects".* FROM "projects" ORDER BY "projects"."id" DESC LIMIT ?  [["LIMIT", 1]]
 => #<Project id: 3, name: "Cooking", created_at: "2016-03-16 20:06:26", updated_at: "2016-03-16 20:06:26", tasks_count: 0> 
 > p.tasks.create(name: 'Add counter cache')
   (0.1ms)  begin transaction
  SQL (0.7ms)  INSERT INTO "tasks" ("name", "created_at", "updated_at", "project_id") VALUES (?, ?, ?, ?)  [["name", "Add counter cache"], ["created_at", 2016-03-16 21:09:25 UTC], ["updated_at", 2016-03-16 21:09:25 UTC], ["project_id", 1]]
   (0.7ms)  commit transaction
 => #<Task id: 11, name: "Add counter cache", complete: nil, created_at: "2016-03-16 21:09:25", updated_at: "2016-03-16 21:09:25", project_id: 1, priority: nil> 

Reload the page. The Cooking project still has 0 tasks. Let's fix this problem.

Step 11

Add counter cache to the Task model.

belongs_to :project, counter_cache: true

This will update tasks_count in projects table when the task is deleted or created.

Step 12

In the console, retrieve the first project and delete a task that belongs to that project.

f = Project.first
  Project Load (0.1ms)  SELECT  "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT ?  [["LIMIT", 1]]
 => #<Project id: 1, name: "Wealth Building", created_at: "2016-03-16 19:07:26", updated_at: "2016-03-16 20:55:29", tasks_count: 2> 
  f.tasks.map(&:id)
  Task Load (0.1ms)  SELECT "tasks".* FROM "tasks" WHERE "tasks"."project_id" = ?  [["project_id", 1]]
=> [1, 2, 11] 
 > f.name
 => "Wealth Building" 
> Task.destroy(1)
  Task Load (0.1ms)  SELECT  "tasks".* FROM "tasks" WHERE "tasks"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
   (0.0ms)  begin transaction
  SQL (0.6ms)  DELETE FROM "tasks" WHERE "tasks"."id" = ?  [["id", 1]]
   (0.5ms)  commit transaction
 => #<Task id: 1, name: "Get rich quick", complete: false, created_at: "2016-03-16 19:07:26", updated_at: "2016-03-16 19:07:26", project_id: 1, priority: 4> 

Reload the project index page http://localhost:3000/projects. You will now see the number of tasks decremented for Wealth Building project.

Correction

In step 5, you will get:

NoMethodError: undefined method `find_each' for []:Array

You have two options. You can either use the batch method find_each if you want to process projects in batches of 1000 as shown below:

Project.find_each do |x|
      p x
    end
  Project Load (0.1ms)  SELECT  "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT ?  [["LIMIT", 1000]]

Or, you can use each method if you want to get only specific column from the projects table and you don't have too many projects in your database.

Project.pluck(:id).each do |x|
      p x
    end
   (0.2ms)  SELECT "projects"."id" FROM "projects"

You can read the docs for batch processing in Rails 5. Thanks to Roman Storm for this correction.

Making Error Message Meaningful

If you forget to define the counter_cache: true in the model, you will get this error:

~/videos/rcac $rake task_counter
rake aborted!
NoMethodError: undefined method `counter_cache_column' for nil:NilClass
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/counter_cache.rb:38:in `block in reset_counters'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/counter_cache.rb:22:in `each'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/counter_cache.rb:22:in `reset_counters'
/Users/bparanj/videos/rcac/lib/tasks/counter_cache.rake:6:in `block (2 levels) in <top (required)>'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:59:in `block (2 levels) in find_each'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:59:in `each'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:59:in `block in find_each'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:121:in `block in find_in_batches'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:214:in `block in in_batches'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:198:in `loop'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:198:in `in_batches'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:120:in `find_in_batches'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/relation/batches.rb:58:in `find_each'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/querying.rb:9:in `find_each'
/Users/bparanj/videos/rcac/lib/tasks/counter_cache.rake:5:in `block in <top (required)>'
/Users/bparanj/.rvm/gems/ruby-2.3.1@r5/gems/rake-12.0.0/exe/rake:27:in `<top (required)>'
Tasks: TOP => task_counter
(See full trace by running task with --trace)

when you run the rake task. If you open the file /Users/user-name/.rvm/gems/ruby-2.3.1@r5/gems/activerecord-5.0.1/lib/active_record/counter_cache.rb and look at line 38:

      def reset_counters(id, *counters)
        object = find(id)
        counters.each do |counter_association|
          has_many_association = _reflect_on_association(counter_association)
          unless has_many_association
            has_many = reflect_on_all_associations(:has_many)
            has_many_association = has_many.find { |association| association.counter_cache_column && association.counter_cache_column.to_sym == counter_association.to_sym }
            counter_association = has_many_association.plural_name if has_many_association
          end
          raise ArgumentError, "'#{self.name}' has no association called '#{counter_association}'" unless has_many_association

          if has_many_association.is_a? ActiveRecord::Reflection::ThroughReflection
            has_many_association = has_many_association.through_reflection
          end

          foreign_key  = has_many_association.foreign_key.to_s
          child_class  = has_many_association.klass
          reflection   = child_class._reflections.values.find { |e| e.belongs_to? && e.foreign_key.to_s == foreign_key && e.options[:counter_cache].present? }
          counter_name = reflection.counter_cache_column

          unscoped.where(primary_key => object.id).update_all(
            counter_name => object.send(counter_association).count(:all)
          )
        end
        return true
      end

Just before the line 38:

counter_name = reflection.counter_cache_column

we need to raise an exception with a helpful error message like this:

raise ArgumentError, "'counter_cache: true declaration is missing in the model" if reflection.nil?

This is an opportunity to contribute to Rails. Go ahead and submit a pull request with a test case for this problem.

Summary

In this article, you learned how to use counter cache in Rails 5 apps.

References

Select vs Pluck


Related Articles

Watch this Article as Screencast

You can watch this as a screencast Counter Cache Column in Rails 5


Create your own user feedback survey