MySQL - How to “SUM” in a :has_many case

Hi there,

I have the following tables...

  User :has_many Purchases
  Item :has_many Purchases

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

How does this query 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
"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

Tom Ha wrote:

Hi there,

I have the following tables...

  User :has_many Purchases
  Item :has_many Purchases

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

How does this query look like? (I'm not sure how to handle "SUM"
correctly, in this case.)

@user # assume this exists
total = Purchase.sum(:amount, :conditions => { 'user_id' => @user })
=> SELECT sum("purchases".amount) AS sum_amount FROM "purchases" WHERE
("purchases"."user_id" = 1)