CODEWITHSUNDEEP
ruby-on-railsactiverecord
Theozaurus
Theozaurus

Reputation: 963

Generating reports efficiently with ActiveRecord

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.

For instance I might have an Account class with a method like this:

def balance_at(time=Time.now)
  payments_out = self.payments.where("created_at <= ?",time).sum("amount")
  payments_in  = self.payments_on_account.where("created_at <= ?",time).sum("amount")
  payments_in - payments_out
end

This could be used to get an accounts balance say at the beginning of the month, and at the end. It works great.

However, if I want a table of all Account balances for the beginning and end of the month things get silly. So for example:

Account.includes(:payments, :payments_on_account)

Will get all of the data I'd need if I wanted to crunch this all purely in Ruby, but my nice little method balance_at doesn't do all of the number crunching in Ruby (that would be slow for the individual case).

I could solve it with something that does it in Ruby and SQL depending on what is cached like so:

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 < time }.inject(0){|a,p| p.amount + a }
    else
      payments.where("created_at <= ?",time).sum("amount")
    end
  }
  payments_in - payments_out
end

However, that isn't terrible readable or easy to test either.

How would you solve it?

Upvotes: 0

Views: 3239

Answers (3)

dcunited001
dcunited001

Reputation: 501

i've worked on a few projects that need reporting. the web application stack isn't the best place to do your reporting, but it seems that the open source reporting options are fairly limited. but not every organization can use SSRS or Crystal and in my experience those products are painful and introduce more problems than necessary.

I'm using views to get these done. SQL is designed for grouping and aggregating data and it's more equipped to handle this stuff than ruby. however, most of the time, views are going to be executed on the fly, so it's not like you're getting a performance gain here. ideally, after you get a basic implementation, you can set up some cron tasks or means of precalculating the data. and if your reports are going to be accessed often and during the day, you will want a dedicated reporting db. if the reports have to have live data, you will need to set up replication.

messing with SQL within Ruby/Rails is messy and frowned upon, I know. So I wrote a gem called Skiima and it helps you manage extraneous SQL objects that you may have in your project. And by loading them with your migrations, it becomes easier to test these.

http://github.com/dcunited001/skiima

aside from that, this is what i've been doing:

class AccountsReport < ActiveModel
  attr_accessor :items
  def initialize(attr = {})
    # read in params, set attrs
  end

  def execute
    get_report_items
    group_report_items
    summarize_report_groups # if this needs to occur outside of sql
  end
end

class AccoutsReportItem < ActiveRecord::Base
  # you can hook into a view here, you will want the view to return an id col
  set_table_name :view_accounts_report_items
end

# yay for arel and activerecord methods.  
# you can even set up relationships on these.  use sparingly.
# AccountsReportItem.where(:blah => 'balah')

Upvotes: 1

apchester
apchester

Reputation: 1144

Assuming that you have 1000s of accounts, my first question would be do you really need to display them all at once? Is this really useful to the user?

If not, then you can continue with your first method- just limit the number of accounts per page to an acceptable level. You'll still be doing two queries per function call, but its testable and reliable.

In the case where you're rendering a page for a report printout- then simple explain to the user that it may take some time.

I understand your need for a faster solution, but sometimes faster isn't necessarily more user friendly.

Upvotes: 1

John Paul Ashenfelter
John Paul Ashenfelter

Reputation: 3143

Your best bet, assuming you're staying inside Rails (as opposed to another tool) is simply using find_by_sql().

Its certainly going to be ugly, but it will be READABLE -- and no more ugly than raw SQL ever is.

I've worked on a number of Rails apps where "calculations in Ruby" were replaced with far more performant find_by_sql for reports specifically. It always feels a little dirty, but I also like taking 5m reports and making them run in 30s with some decent SQL.

Upvotes: 0

Related Questions