Forum: Ruby on Rails Best find for the job...

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Neal L (Guest)
on 2009-01-28 17:04
(Received via mailing list)
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!
Jeff E. (Guest)
on 2009-01-28 17:29
(Received via mailing list)
In my opinion, the best way is the first way that works.  If that
method proves to be too inefficient, then optimize afterwards.
Robert W. (Guest)
on 2009-01-28 23:33
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.
This topic is locked and can not be replied to.