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

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.