Ruby on Rails: how do you write a find statement that sums up a bunch of values if an object belongs to another object?

Question

Lets say Users have BankAccounts which have a balance value.

So, how do I generate an array that Users and the total value of all their BankAccounts' values added together?

Answer 1

First you need to find the users you want so I'll just assume you want all users.

@users = User.all

Then you need to take the array and collect it with only the elements you want.

@users.collect! {|u| [u.name, u.bank_account_total_value]}

For this kinda attribute I would set it in the model assuming you have has_many :transactions as an association

Class User

 has_many :transactions 

 def bank_account_total_value
     total = 0
     self.transactions.each do |t|
        total += t.amount
     end 
 end   

end 

Answer 2

I'm not sure if this is quite what you want, but you can do something like:

ActiveRecord::Base.connection.select_all("select user_id, sum(balance) from accounts group by user_id;")

This will give you an array of user_ids and balances from the accounts table. The advantage of doing it this way is that it comes down to only one SQL query.

Answer 3

You'll want to do something like this. I don't believe it's possible to use #sum via an association.

class User
  def net_worth
    BankAccount.sum(:balance, :conditions => ["user_id = ?", self.id])
  end
end

Edit

I see a reference to a #sum in AssociationCollection, so try this:

class User
  def net_worth
    self.bank_accounts.sum(:balance)
  end
end

(I haven't tested this code)