ruby on rails - How do you SUM two fields from two tables, even when the field in the second table could be null? -

i have following tables:

products.rb

# has_many :sales             +----+----------+----------+-------+ | id | name     | quantity | price | +----+----------+----------+-------+ | 1  | pencil   | 30       | 1.0   | | 2  | pen      | 50       | 1.5   | | 3  | notebook | 100      | 2.0   | +----+----------+----------+-------+ 

sales.rb

# belongs_to :product  +----+----------+------------+ | id | quantity | product_id | +----+----------+------------+ | 1  | 10       | 1          | | 2  | 2        | 1          | | 3  | 5        | 1          | | 4  | 2        | 2          | | 5  | 10       | 2          | +----+----------+------------+ 

i'd know, first, how many items have left, regardless of type. answer of course 151, that'd cheating. make sum of both tables individually, put them know final number, i'm wondering if done via activerecord in single command.

i tried following:

product.includes(:sales).group('products.id').sum('products.quantity - sales.quantity') 

but get:

=> {1=>73, 2=>88, 3=>0} 

which understandable, going through each 1 sum this:

+-------------------+----------------+-----+ | products.quantity | sales.quantity | sum | +-------------------+----------------+-----+ |        30         |       10       |  20 | |        30         |        2       |  28 | |        30         |        5       |  25 | +-------------------+----------------+-----+ 

which equals 73.

anyway, how achieved activerecord? want know total number of items, i'd know total of each type.

i'm not familiar of activerecord way achieve want can try mixing little sql in there

product   .group('products.id')   .sum('products.quantity - (select sum(sales.quantity) sales_quantity sales sales.product_id = products.id)')