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

Question

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 - 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…

Accepted Answer

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

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

Solution

Problem