Sum table column if an associated tables field meets condition

Question

I am trying to learn database associations with Rails 4.

In my application I have two models, sales and products.

class Sale < ActiveRecord::Base

    belongs_to :customer
    belongs_to :product

end

class Product < ActiveRecord::Base

    self.primary_key = :product_id

    has_many :sales

end

I have set up associations with a has_many and belongs_to and in the Rails Console I can successfully do:

@s = Sale.find_by_product_id("10RB1236").product.product_group_id

  Sale Load (1.5ms)  SELECT "sales".* FROM "sales" WHERE "sales"."product_id" =     '10RB1236' LIMIT 1
  Product Load (0.8ms)  SELECT "products".* FROM "products" WHERE "products"."product_id" = $1 ORDER BY "products"."product_id" ASC LIMIT 1  [["product_id", "10RB1236"]]
 => "30" 

This successfully returns that the product_group_id is "30". A milestone for my learning associations :) Happy with that.

But now I want to be able to return the sum of net_amount where the product_id's associated product_group_id is equal to 30. So that I get the total net_amount of all rows in the database that meets this condition.

I have tried:

@c = Sale.sum(:net_amount, :conditions => {:product_id.product_group_id => "30"})
@c = Sale.sum(:net_amount, :conditions => {:product_id.product.product_group_id => "30"})

Just trying to get some more ideas or a solution on how to do this? If I was doing this in pure SQL I think I would have an inner join happening somewhere.

Any ideas would be greatly appreciated.

Answer 1

Sale.includes(:product).where("products.product_group_id = ?", "30").sum(:net_amount)