Rails: Sum of values in all Transactions that belong_to an Activity

• Live site: http://iatidata.heroku.com
• Github: https://github.com/markbrough/IATI-Data
• Based on aid information released through the IATI Registry: iatiregistry.org

I'm a bit of a Rails n00b so sorry if this is a really stupid question.

There are two key Models in this app:

• Activity - which contains details such as recipient country, funding organisation
• Transaction - which contains details such as how much money (value) was committed or disbursed (transaction_type), when, to whom, etc.

All Transactions nest under an Activity. Each Activity has multiple Transactions. They are connected together by activity_id. has_many :transactions and belongs_to :activity are defined in the Activity and Transaction Models respectively.

So: all of this works great when I'm trying to get details of transactions for a single activity - either when looking at a single activity (activity->show) or looping through activities on the all activities page (activity->index). I just call

@activities.each do |activity|
    activity.transactions.each do |transaction|
        transa开发者_开发问答ction.value # do something like display it
    end
end

But what I now really want to do is to get the sum of all transactions for all activities (subject to :conditions for the activity).

What's the best way to do this? I guess I could do something like:

@totalvalue = 0
@activities.each do |activity|
    activity.transactions.each do |transaction|
        @totalvalue = @totalvalue + transaction.value
    end 
end

... but that doesn't seem very clean and making the server do unnecessary work. I figure it might be something to do with the model...?! sum() is another option maybe?

This has partly come about because I want to show the total amount going to each country for the nice bubbles on the front page :)

Thanks very much for any help!

Update:

Thanks for all the responses! So, this works now:

@thiscountry_activities.each do |a|
    @thiscountry_value = @thiscountry_value + a.transactions.sum(:value)
end

But this doesn't work:

@thiscountry_value = @thiscountry_activities.transactions.sum(:value)

It gives this error:

undefined method `transactions' for #<Array:0xb5670038>

Looks like I have some sort of association problem. This is how the models are set up:

class Transaction < ActiveRecord::Base
    belongs_to :activity
end

class Activity < ActiveRecord::Base
    has_and_belongs_to_many :policy_markers
    has_and_belongs_to_many :sectors
    has_many :transactions
end

I think this is probably quite a simple problem, but I can't work out what's going on. The two models are connected together via id (in Activity) and activity_id (in Transactions).

Thanks again!

Use Active Record's awesome sum method, available for classes:

Transaction.sum(:value)

Or, like you want, associations:

activity.transactions.sum(:value)

Let the database do the work:

@total_value = Transaction.sum(:value)

This gives the total for all transactions. If you have some activities already loaded, you can filter them this way:

@total_value = Transaction.where(:activity_id => @activities.map(&:id)).sum(:value)

You can do it with one query:

@total_value = Transaction.joins(:activity).where("activities.name" => 'foo').sum(:value)

My code was getting pretty messy summing up virtual attributes. So I wrote this little method to do it for me. You just pass in a collection and a method name as a string or symbol and you get back a total. I hope someone finds this useful.

def vsum collection, v_attr # Totals the virtual attributes of a collection
  total = 0
  collection.each { |collect| total += collect.method(v_attr).call } 
  return total
end

# Example use

total_credits = vsum(Account.transactions, :credit)

Of course you don't need this if :credit is a table column. You are better off using the built in ActiveRecord method above. In my case i have a :quantity column that when positive is a :credit and negative is a :debit. Since :debit and :credit are not table columns they can't be summed using ActiveRecord.

As I understood, you would like to have the sum of all values of the transaction table. You can use SQL for that. I think it will be faster than doing it the Ruby way.

select sum(value) as transaction_value_sum from transaction;

You could do

@total_value = activity.transactions.sum(:value)

http://ar.rubyonrails.org/classes/ActiveRecord/Calculations/ClassMethods.html