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

rubyonrails-talk
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)