Counter Cache for a column with conditions?

Question

I am new to the concept of counter caching and with some astronomical load times on one of my app's main pages, I believe I need to get going on it.

Most of the counter caches I need to implement have certain (simple) conditions attached. For example, here is a common query:

@projects = employee.projects.where("complete = ?", true).count

I am stumbling into the N+1 query problem with the above when I display a form that lists the project counts for every employee the company has.

Approach

I don't really know what I'm doing so please correct me!

# new migration
add_column :employees, :projects_count, :integer, :default => 0, :null => false

# employee.rb
has_many :projects

# project.rb
belongs_to :employee, :counter_cache => true

After migrating... is that all I need to do?

How can I work in the conditions I mentioned so as to minimize load times?

Answer 1

Instead of update_counters i use update_all

You don't need the Employee.reset_column_information line AND it's faster because you are doing a single database call

Employee.update_all("projects_count = (
   SELECT COUNT(projects.id) FROM projects 
   WHERE projects.employee_id = employees.id AND projects.complete = 't')")

Answer 2

With regards to the conditions with counter_cache, I would read this blog post:

http://douglasfshearer.com/blog/custom-counter_cache-with-conditions

The one thing you should do is add the following to the migration file:

 add_column :employees, :projects_count, :integer, :default => 0, :null => false

 Employee.reset_column_information

 Employee.all.each do |e|
   Employee.update_counters e.id, :projects_count => e.projects.length
 end

So you current projects count can get migrated to the new projects_count that are associated with each Employee. After that, you should be good to go.

Answer 3

You should not use "counter_cache" but rather a custom column :

rails g migration AddCompletedProjectsCountToEmployees completed_projects_count:integer

(add , :default => 0 to the add_column line if you want)

rake db:migrate

then use callbacks

class Project < ActiveRecord::Base
  belongs_to :employee

  after_save :refresh_employee_completed_projects_count
  after_destroy :refresh_employee_completed_projects_count

  def refresh_employee_completed_projects_count
    employee.refresh_completed_projects_count
  end
end

class Employee
  has_many :projects

  def refresh_completed_projects_count
    update(completed_projects_count:projects.where(completed:true).size)
  end
end

After adding the column, you should initialize in the console or in the migration file (in def up) :

Employee.all.each &:refresh_completed_projects_count

Then in your code, you should call employee.completed_projects_count in order to access it

Answer 4

Check counter_culture gem:

counter_culture :category, column_name: Proc.new {|project| project.complete? ? 'complete_count' : nil }