CODEWITHSUNDEEP
ruby-on-railsactiverecord
pjammer
pjammer

Reputation: 9577

Sum associated tables using Active Record

How does one sum all of the "total" columns in an association?

My SQL-fu sucks, so I would like to learn how to do so using Active Record for my rails 2.3.5 app (so no fancy syntax just yet please ;-) And I'm on MySQL.

Let's say I have:


Shop 

has_many :customers
has_many :transactions, :through => :customers

So normal stuff.


shop = Shop.first
shop.transactions
=> 100

Ok, all that background for the question:

I want to SUM the total column in the transactions from the past year (Jan 1st 2010..Dec 31 2010) and display them by customer.

Whilst I know how to group transactions and find with conditions, it's the sum part where my lack of SQL lets me down.


first = Date.new(2010, 01, 01)
last = Date.new(2010, 12, 31)

shop.transactions(:conditions => {:created_at => first..last}, :group => :customer_id, :include => sum(:total))

I just took a stab, am i on the right track?

Upvotes: 3

Views: 3914

Answers (2)

pjammer
pjammer

Reputation: 9577


shop.transactions.sum(:total, :conditions => {:created_at => first..last}, :group => :customer_id)

This looks like an easier way. I now know that sum can take AR attributes too. cool.

Upvotes: 8

ablemike
ablemike

Reputation: 3394

Look into collect methods.

You can do things like:

transactions = Shop.transactions
total = 0
sum = transactions.collect{|i| total+=i.transaction.amount}

Replace amount with your property that contains the amount of the transaction.

You could also use .sum

sum = transactions.to_a.sum(&:amount)

Upvotes: 0

Related Questions