Howto optimize an ActiveRecord query with sums on several categories

Question

Models:

User has_many Categories, has_many Entries
Category has_many Entries
Entry belongs_to both Category and User

Let's assume, there is a name and an amount on entries. If I have a view that needs to display a particular month (let's take created_at for now) for a specific user and want to display a table with all entries in that particular month grouped by categories (empty categories are not displayed) and with a sum and count of the entries within a category.

My question is: What is the most efficient way to query the database (and make best use of caching)? This view will be rendered quite often and each time a new entry is created by the user, naturally the sum of one category changes but not those of the others.

score 2 · Accepted Answer · answered Apr 08 '11 at 16:03

The query part can be accomplished quite simply with a scope:

class Category
  scope :grouped_entries, lambda { |user, date|
    select(['categories.*', 'COUNT(entries.id) as count_entries'])
      .joins(:entries)
      .where('entries.user_id = ?', user.id)
      .where('MONTH(entries.created_at) = ?', date.month)
      .group('categories.id')
  }
end

Which can then be looped over:

<% Category.grouped_entries(current_user, Date.today).each do |category| %>
  <%= category.name %> with <%= category.count_entries %> entries this month.
<% end %>

Of course, caching this requires that you refresh the cache anytime an entry is created this month. For example, you could cache the query like this:

@categories = Rails.cache.fetch("/grouped_entries/#{current_user.id}/#{Date.today.month}") do
  Category.grouped_entries(current_user, Date.today).all
end

And then simply expire it when a new entry is created by using the user_id and the entry created_at month. I would say that you should use this approach first, before trying to cache every single category's entries individually. This query should perform quite quickly, so you shouldn't have to delve into caching each row individually. It will also perform a single query rather than one for each category.

Here is why I wouldn't cache each row individually:

You still have to query the database to get a list of categories or category ids for a user, so you have to perform one query anyways.
Cachine expiration is more complicated because there are more cases where you have to expire two caches, for example when a category for an entry changes you have to expire the old category cache and the new category cache.
You may end up running more queries against your database to grab expired cache information and the latency to the database will probably end up taking longer than the actual query.
You don't need to cache every row because the query is simple and uses indexes. You should have an index on user_id and category_id for entries.

Great Post! Thank you very much! I experience one minor issue: delelopment is in SQLite, production on PostgreSQL. On SQLite, there is no MONTH function. Without this line, the output is fine (and returns all entries), if I change it to `where('strftime(%m, created_at)= ?', date.month)` also the categories with zero entries are in the results. Is there a version that works on both SQLite and Postgre (Heroku deployment)? — Markus Proske, Apr 09 '11 at 10:19
Yeah, you could use some kind of between query. For example: 'first_day_of_the_month <= created_at AND created_at <= last_day_of_the_month'. That should work in both DB languages. — Pan Thomakos, Apr 09 '11 at 16:39

score 1 · Answer 2 · answered Apr 10 '11 at 09:58

Pan's answer was perfect and of great help. For the archives: this is the query I used in my app:

scope :grouped_entries, lambda { |user, date|
  select(['categories.*', 'COUNT(entries.id) as count_entries']).
    joins(:entries).
    where('entries.user_id = ?', user.id).
    where(':first_day <= entries.created_at AND entries.created_at <= :last_day', { 
       :first_day => date.at_beginning_of_month,
       :last_day => date.at_end_of_month
    } ).
    group('categories.id')
}

Howto optimize an ActiveRecord query with sums on several categories

2 Answers2