title: Generating reports efficiently with ActiveRecord
body: <p>We need to generate reports that pull in lots of data, run some calculations and spit them out as part of a larger table. Doing this isn't difficult. However, making it so that existing methods can be used and not generating 1000's of SQL queries is a lot harder.</p>
  
  <p>For instance I might have an <code>Account</code> class with a method like this:</p>
  
  <pre><code>def balance_at(time=Time.now)
    payments_out = self.payments.where("created_at &lt;= ?",time).sum("amount")
    payments_in  = self.payments_on_account.where("created_at &lt;= ?",time).sum("amount")
    payments_in - payments_out
  end
  </code></pre>
  
  <p>This could be used to get an accounts balance say at the beginning of the month, and at the end. It works great.</p>
  
  <p>However, if I want a table of all <code>Account</code> balances for the beginning and end of the month things get silly. So for example:</p>
  
  <pre><code>Account.includes(:payments, :payments_on_account)
  </code></pre>
  
  <p>Will get all of the data I'd need if I wanted to crunch this all purely in Ruby, but my nice little method <code>balance_at</code> doesn't do all of the number crunching in Ruby (that would be slow for the individual case).</p>
  
  <p>I could solve it with something that does it in Ruby and SQL depending on what is cached like so:</p>
  
  <pre><code>def balance_at(time=Time.now)
    payments_out, payments_in = [payments, payments_on_account].map{|payments|
      if payments.loaded?
        payments.find_all{|p| p.created_at &lt; time }.inject(0){|a,p| p.amount + a }
      else
        payments.where("created_at &lt;= ?",time).sum("amount")
      end
    }
    payments_in - payments_out
  end
  </code></pre>
  
  <p>However, that isn't terrible readable or easy to test either.</p>
  
  <p>How would you solve it?</p>