ActiveRecord query for detailed and summary data

Question

Using Rails3, I have a table downloadswith columns download_dateand credits. What I want to create is a table, for example:

 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 done 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, although it works, is not very similar to Rails and leads to a fairly large number of SQL query releases. Assuming 100 users x 10,000 downloads per year, and the number of requests by the end of the year is about 1,000,000 for each of these pages.

Any solution I came up with should be database aggregated, if at all possible. I know that I will use PostgreSQL for Heroku for deployment, and my development version, however, is erroneous, still works on MySQL.

, . ?

Answer 1

, , . - . u.downloads.all.group_by ( all ), . , .

: , Rails 3 . , N + 1. , - - - , . - .

Answer 2

, :

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