Rails. Add "count" column with sum of values from associated table

Question

I have Category table and Issue table.

In Category model:

has_many :issues

In Issue model:

belongs_to :category

I am able to write a query that will return the issue_count as a virtual attribute on the collection:

Category.left_outer_joins(:issues).select('categories.*, COUNT(issues.*) AS issues_count').group('categories.id')

However, I want an actual column on the table that stores the issues_count as a value in the categories table, like this:

id | name | created_at | updated_at | description | tags | issues_count

Answer 1

You can add an issues_count column in your Issues model:

# app/models/issue.rb
class Issue < ApplicationRecord
  belongs_to :category, counter_cache: true
end

This will cause the category.issues_count field to be updated automatically by ActiveRecord.

You must also create a migration to add this field and update existing records:

class AddIssuesCountToCategory < ActiveRecord::Migration[5.0]
  def up
    add_column :categories, :issues_count, :integer, default: 0
    Category.reset_column_information
    Category.all.each do |c|
      Category.update_counters c.id, issues_count: c.issues.length
    end
  end
  def down
    remove_column :categories, :issues_count
  end
end

Ryan Bates has a good RailsCasts about this: http://railscasts.com/episodes/23-counter-cache-column

Also see the Rails Guide: http://edgeguides.rubyonrails.org/association_basics.html#options-for-belongs-to-counter-cache