MySQL / Ruby on Rails - How to "SUM" in a :has_many case

I have the following tables:

User :has_many Purchases

Item :has_many Purchases

Item has a column "amount" (can be + or -) and I need to find all Users that have a positive SUM of "Item.amounts" (over all Purchases each one has made).

How does this quer开发者_StackOverflow社区y look like? (I'm not sure how to handle "SUM" correctly, in this case.)

I started out with the following, but obviously, it's wrong... (it wouldn't "include" Purchases that have an Item with a negative Item.amount...)

@users = User.find(:all,

:include => {:purchases => :item},

:select => "SUM(item.amount)",

:order => "...",

:conditions => "...",

:group => "users.id",

:having => "SUM(item.amount) > 0" )

Thanks for your help with this!

Tom

---

Try this:

User.all(:joins => items, :group => "users.id", 
          :having => "SUM(items.amount) > 0")

---

It sounds like this is a good case for some model methods.

I didn't test this but I think you want to do something similar to the following:

class User < ActiveRecord::Base

has_many :purchases
has_many :items, :through => :purchases

def items_total
  #get all the items iterate over them to get the amount, 
  #compact to get rid of nils
  #and reduce with a sum function to total and return
  items.all.each{|item| item.amount}.compact.reduce(:+)
end

then

User.items_total