ActiveRecord Query for Detail and Summary Data

Question

Using Rails3, I have a downloads table with download_date and credits columns. What I want to generate is a table like:

 Date        Credits
 2010-11-01  25
 2010-11-01  27
*2010-11-01  52  <= Sum of previous 2 rows
 2010-11-02  32
*2010-11-02  32  <= Sum of previous row

This can be accomplished using something like:

u.downloads.group_by(&:download_date).each do |date, downloads|
  downloads.each do |d|
    puts " %10s  %3d" % [d.download_date, d.credits]
  end
  puts "*%10s  %3d" % [date, downloads.sum(&:credits)]
end

This solution, while it works, is not too Rails-like and results in a fairly large number of SQL queries being issued. Assuming 100 users x 10,000 downloads / year and the query count by the end of a year is on the order of 1,000,000 for every one of these pages served.

Any solution I come up with should be database-agnostic if at all possible. I know I'll be using PostgreSQL on Heroku for deployment, and my development version, however misguided, is still running on MySQL.

I hope I've provided sufficient information about the problem domain and the issues involved. Any opinions or suggestions?

Answer 1

What you're doing is actually pretty optimized already. The only problem — you are fetching one download at a time. Use u.downloads.all.group_by (add all in there) in order to load all downloads at once. This way you're pretty much down to one query.

Update: Although on the second look, in Rails 3 this shouldn't even produce multiple queries. There are numerous reasons why you may experience N+1 issues. For example, if you back-reference a particular download from some associated object somewhere in your view - rails may not know that this download was already fetched from database as part of your array, and re-fetch it again. If you see many queries — this code shouldn't be causing them.