CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3.2reporting
bensmithbwd
bensmithbwd

Reputation: 343

What is the 'Rails Way' to implement a dynamic reporting system on data

Intro

I'm doing a system where I have a very simple layout only consisting of transactions (with basic CRUD). Each transaction has a date, a type, a debit amount (minus) and a credit amount (plus). Think of an online banking statement and that's pretty much it.

The issue I'm having is keeping my controller skinny and worrying about possibly over-querying the database.

A Simple Report Example

So in the controller I create:

def report
  @d = Transaction.select("SUM(debit) as total_debit").where("date BETWEEN 'x' AND 'y'")
  @c = Transaction.select("SUM(credit) as total_credit").where("date BETWEEN 'x' AND 'y'")
  @t = @c.credit_total - @d.debit_total
end

Additional Question Info

My actual report has closer to 6 or 7 database queries (e.g. pulling out the total credit/debit as per type == 1 or type == 2 etc) and has many more calculations e.g totalling up certain credit/debit types and then adding and removing these totals off other totals.

I'm trying my best to adhere to 'skinny model, fat controller' but am having issues with the amount of variables my controller needs to pass to the view. Rails has seemed very straightforward up until the point where you create variables to pass to the view. I don't see how else you do it apart from putting the variable creating line into the controller and making it 'skinnier' by putting some query bits and pieces into the model.

Is there something I'm missing where you create variables in the model and then have the controller pass those to the view?

Upvotes: 4

Views: 1479

Answers (2)

Andrea Fiore
Andrea Fiore

Reputation: 1638

A more idiomatic way of writing your query in Activerecord would probably be something like:

class Transaction < ActiveRecord::Base
  def self.within(start_date, end_date)
    where(:date => start_date..end_date)
  end

  def self.total_credit
    sum(:credit)
  end

  def self.total_debit
    sum(:debit)
  end
end

This would mean issuing 3 queries in your controller, which should not be a big deal if you create database indices, and limit the number of transactions as well as the time range to a sensible amount:

@transactions = Transaction.within(start_date, end_date)
@total = @transaction.total_credit - @transaction.total_debit

Finally, you could also use Ruby's Enumerable#reduce method to compute your total by directly traversing the list of transactions retrieved from the database.

@total = @transactions.reduce(0) { |memo, t|  memo + (t.credit - t.debit) }

For very small datasets this might result in faster performance, as you would hit the database only once. However, I reckon the first approach is preferable, and it will certainly deliver better performance when the number of records in your db starts to increase

Upvotes: 4

Peter Brown
Peter Brown

Reputation: 51707

I'm putting in params[:year_start]/params[:year_end] for x and y, is that safe to do?

You should never embed params[:anything] directly in a query string. Instead use this form:

where("date BETWEEN ? AND ?", params[:year_start], params[:year_end])

My actual report probably has closer to 5 database calls and then 6 or 7 calculations on those variables, should I just be querying the date range once and then doing all the work on the array/hash etc?

This is a little subjective but I'll give you my opinion. Typically it's easier to scale the application layer than the database layer. Are you currently having performance issues with the database? If so, consider moving the logic to Ruby and adding more resources to your application server. If not, maybe it's too soon to worry about this.

I'm really not seeing how I would get the majority of the work/calculations into the model, I understand scopes but how would you put the date range into a scope and still utilise GET params?

Have you seen has_scope? This is a great gem that lets you define scopes in your models and have them automatically get applied to controller actions. I generally use this for filtering/searching, but it seems like you might have a good use case for it.

If you could give an example on creating an array via a broad database call and then doing various calculations on that array and then passing those variables to the template that would be awesome.

This is not a great fit for Stack Overflow and it's really not far from what you would be doing in a standard Rails application. I would read the Rails guide and a Ruby book and it won't be too hard to figure out.

Upvotes: 0

Related Questions