Best find for the job


#1

Hi all,

I’m trying to find the most effective way to generate an aggregation
report. The models involved are:

Customer --(has_many)–> Charges
Charges --(have_one)–>Account

What I’m trying to do is find the total charges during the year for
each customer by account. In other words, a function like:

customer.charges_for_year()

in the customer model that will return a hash of each account charged
and the customer’s total charges to that account.

From what I’ve read I don’t think I should be using named_scope, since
this involves associations…

Any ideas?

Thanks!


#2

In my opinion, the best way is the first way that works. If that
method proves to be too inefficient, then optimize afterwards.


#3

Neal L wrote:

What I’m trying to do is find the total charges during the year for
each customer by account. In other words, a function like:

I’m guessing that the most efficient way to accomplish this (that is
without doing some data caching), would be to let the database handle
it.

If you had a database view configured something like this:

customer_charges (DB View)

select t0.customer_name, sum(t1.amount) as amount
from customers t0 join charges t1 on t0.id=t1.customer_id
group by t0.id, t1.account_id
having year(t1.created_at)=year(current_date());

Then you could create a model that manages this report data:

CustomerCharge << ActiveRecord::Base
def readonly?
true
end
end

Note: This is all written off of top of my head with absolutely not
tested.

Another option might be to create a class in Ruby that mimics a database
view by executing some raw SQL.