What is the Rails Way for implementing a dynamic data reporting system?

Question

Introduction

I am making a system where I have a very simple layout consisting only of transactions (with basic CRUD). Each transaction has a date, type, debit amount (minus) and loan amount (plus). Think of an online banking application and more.

The problem I am facing is that my controller is accurate and worried about possibly over querying the database.

Simple report example

• Total debit for the selected period, for example. SUM(debit) as total_debit
• The total loan for the selected period, for example. SUM(credit) as total_credit

• Total total, e.g. total_credit - total_debit

• The report should contain a dynamic date range, for example. where(date BETWEEN 'x' and 'y')

• The date range will never be more than a year and will be maximum, for example, 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 end 

Additional information about the question

My actual report is closer to 6 or 7 database queries (for example, pulling a total credit / debit according to type == 1 or type == 2, etc.) and has many other calculations, for example, to summarize certain types of credit / debit cards and then adding and removing these totals from other totals.

I try to stick with the "lean model, fat controller", but I am having problems with the number of variables that my controller must pass to the view. The rails seemed very simple until you create variables to jump to the view. I don’t understand how to do this, except that you add a variable that creates a string to the controller and makes it "skinnier", placing several request fragments in the model.

Is there something that I am missing when you create variables in the model and then pass the controller to these views?

Answer 1

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

 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 problem if you create database indexes and also limit the number of transactions and the time range to a reasonable amount:

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

Finally, you can also use the Ruby Enumerable # reduce method to calculate your total by directly sorting through the list of transactions received from the database.

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

For very small datasets, this can lead to higher performance, since you would only get into the database once. However, I believe that the first approach is preferable, and it will certainly provide better performance when the number of records in your db starts to increase.

Answer 2

I put in params [: year_start] / params [: year_end] for x and y, is it safe to do this?

You should never embed params[:anything] directly in the query string. Use this form instead:

 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 these variables, should I just query the date range once, and then do all the work on the array / hash, etc.

This is a bit subjective, but I will give you my opinion. It is generally easier to scale the application level than the database level. Are you currently experiencing database performance issues? If so, consider porting the logic to Ruby and adding more resources to your application server. If not, perhaps it's too early to worry about it.

I really don't see how I get most of the work / calculations in the model, I understand the scope, but how would you put the date range in scope and still use the GET parameters?

Have you seen has_scope ? This is a great stone that allows you to identify areas in your models and automatically apply them to controller actions. I usually use this for filtering / searching, but it looks like you might have a good use case.

If you could give an example of creating an array using a wide database call, then perform various calculations on that array and then pass these variables to a template that would be awesome.

This is not very suitable for, and it is really not far from what you will do in a standard Rails application. I would read the Rails manual and the Ruby book, and it won't be too hard to understand.