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

Rails
1

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

2

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)