Sum of counts for nested models

Question

I have two models: Category and Ad. Category has_many :ads + I added counter cache :ads_count. I use gem awesome_nested_set to make nested categories, so Category1 can be parent/child of Category2 which can be parent of Category3 etc. What I need for my categories#index is to calculate total sum of ads which belong to certain category or child category(or "grandchild" etc). My solution for now is: some_nested_categories.sum(:ads_count). But let's say if I have many many categories in my index page, it makes many queries to retrieve all that data and it takes too long. How can I make this more efficiently? Thanks for help!

Answer 1

You could extend the idea of the counter_cache manually for a sort of sum_cache, lets call it nested_ads_count.

Then there would be 4 general cases that need to be handled

• a category gets a new add
• a category is "Moved/added" to a new parent category"
• a category gets deleted (or removed from a parent)"

An after_update callback that updates the parent's nested_ads_count when either the current's nested_ads_count or ads_count updates. solves the first case.

awesome_nested_set solves the other 2 cases

using an after_add and after_remove callbacks recalculate the nested_ads_count.

The method to calculate and cache the nested_ads_count would look like this

def reset_nested_ads_count
  self.nested_ads_count = some_nested_categories.sum(:nested_ads_count) + self.ads_count
end

This method is optimal when the frequency that the count is used is greater than the frequency that it is updated, as it only does the time expensive queries when the number would be updated, and not when the number needs to be seen.

There is one possible pitfall that can happen and that is if you have a loop in your nesting (a > b > a, or even more insidious a > b > c > ... > a) you can get yourself in an infinite loop. I have not seen anything explicitly stating that awesome_nested_set blocks this case.