Take the 2-minute tour ×


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

  • The total debit over the chosen period e.g. SUM(debit) as total_debit
  • The total credit over the chosen period e.g. SUM(credit) as total_credit
  • The overall total e.g. total_credit - total_debit

  • The report must allow a dynamic date range e.g. where(date BETWEEN 'x' and 'y')

  • The date range would never be more than a year and will only be a max of say 1000 transactions/rows at a time

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

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?

share|improve this question
Too many questions in one. Please try to reduce them down to your top one or two to avoid confusing answers and diluting the overall question's worth. Stack Overflow wants concise and well defined questions and answers. –  the Tin Man Mar 17 '13 at 16:36
I'll have a go making this simpler later on, I'm going to do a bit more searching as I thought this would have been quite a common issue, maybe I'm going overboard and worrying too much about the skinny controller, fat model phrase... –  bensmithbwd Mar 17 '13 at 17:16
I would introduce a TransactionReporter object, it should execute the queries it needs and handle all of the calculations, when you execute ActiveRecord queries they basically return ruby Arrays, so you can store that data in instance variables on the reporter object and return those to the views after all calculations have been made –  house9 Mar 17 '13 at 18:04
@house9 I'm a little too new to Rails and it's lingo (as I come from non-framework PHP) to fully understand what you mean by a TransactionReporter object and how to implement that into my example. If you have the time and it's easy enough to give a quick example via an answer then I would be more than happy giving it a go and reporting on results, for now I'm going to pursue Andrea's answer. –  bensmithbwd Mar 17 '13 at 18:51
TransactionReporter would be a PORO (plain old ruby object) as opposed to a rails model or controller - just go with what Andrea Fiore suggests initially. If you find that the Transaction object starts to get too fat - business rules, validation, data access, etc and you want to move your reporting logic out into its own object do it then –  house9 Mar 18 '13 at 0:58
show 1 more comment

2 Answers

up vote 3 down vote accepted

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)

  def self.total_credit

  def self.total_debit

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

share|improve this answer
Hi Andrea, self.total_credit etc is very helpful, but I have a question regarding the self.within - How does this allow me to specify via a link the params start_date and end_date e.g. <a href='transactions/report?start_date=2013-03-17&end_date=2014-03-17'>link</a>. I also assume that defining, for example, 15 different variables is OK to do all of this in the controller (using the model definitions to make the SQL query read well), and maybe I'm worrying a little bit too much about putting as much as possible in the model? –  bensmithbwd Mar 17 '13 at 18:57
You can convert the date parameters from instances of String to Date objects and then feed them as argument into the 'within' method. In your controller, try something like: 'Transaction.within(Date.parse(params[:start_date], Date.parse(params[:end_date]))' –  Andrea Fiore Mar 17 '13 at 22:01
Excellent, that sounds perfect, thanks for your help @Andrea! –  bensmithbwd Mar 18 '13 at 1:42
add comment

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.

share|improve this answer
Hi cheers for the answer, don't worry I did mean using ? in the where() so that's at least correct. I feel the main problem is that I need to simplify my question. I really did think this would be much more of a common problem though, maybe I'm using the wrong terminology... –  bensmithbwd Mar 17 '13 at 17:14
@bensmithbwd I'd recommend breaking it out into very specific questions. You'll definitely get better responses that way. –  Beerlington Mar 17 '13 at 17:25
add comment

Your Answer


