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?

Asparagus

Answer 1

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

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 object. After that, you should be good to go.

Foray

@Mike Lewis - how do I implement the conditions? – Asparagus

@Mike Lewis - thanks a lot! I'm going to try this out. I'll get back to you as soon as I can. – Asparagus

@Mike - shouldn't we include the conditions in this migration? Otherwise the initial values for projects_count will be the unconditional total, right? – Asparagus

Yes, so you would add a new column and do something like: Employee.all.each do{|e| Employee.update_counters(e.id, :completed_projects_count => e.projects.where(:complete => true).count – Foray

Thanks a lot Mike. Working on it now. – Asparagus

It's worth noting that this approach, while it works, is less safe than the out of the box Rails implementation because the count is happening in memory (at the application level) whereas the vanilla counter cache does a SQL UPDATE command incrementing the count at the DB level. So, you could run into stale data with concurrent updates. I unfortunately don't have a better solution but would be cautious of the callbacks. – Eberly

I tried around with the linked approach and noticed there is a problem when moving projects from one employee to another. Only the employee the project was newly assigned to would get its count updated. The employee that had the project before would still have the old (now incorrect) project count. The example in the link worked with comments and posts, where moving is unlikely. Make sure you don't move associations around when using this approach. – Disclose

Might be worth noting that the linked blog post is from 2006. – Melchior

I would add, that you should not add code to migrations. Add your migration, then create a rake task to run any additional code you need. – Wellbeing

It may be a good idea to use find_each instead of all.each in case there are more than 1000 records in the table, to use both the server memory and the database more efficiently – Buckeen

Answer 2

Check counter_culture gem:

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

Cown

Buried at the bottom of the readme is that it doesn't work with polymorphic relations. – Promiscuous

counter_culture now supports polymorphic associations of one level. – Oenone

Answer 3

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')")

Dialytic

Do you know of any advantages/disadvantages to your solution in comparison to update_counters? – Asparagus

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

Answer 4

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

Hulburt

Be careful when using this approach when changing assignments between projects and employees. Only the new assignment will get registered this way. The old assignment won't get updated leaving you're count off by one. – Disclose

@Disclose If project gets a new employee_id, then the refresh_employee_completed_projects_count method will be run, hence update the employee. – Specialize