Joins in Ruby on Rails

Question

I'm working on generating quarterly royalty reports for authors. I have a Report model (not backed by a database table) that I'm using to display the report for specific authors based on the year and quarter. I'm routing that to author/:author_id/:reports/:year/:quarter

But I'm using this:

@line_items_totals = LineItem.where(:order_id => @orders, :product_id => @author_products).order("product_id ASC").sum(:quantity, :group => :product_id)

That returns a hash with the product id as the key and the total quantity sold for all that products line items, for the given time period, like this:

#<Enumerator: {1=>2865, 2=>4068, 4=>50, 5=>60, 9=>22}>

the query it produces is:

SELECT SUM("line_items"."quantity") AS sum_quantity, product_id AS product_id 
FROM "line_items" 
WHERE ("line_items"."order_id" IN (10, 12, 15, 16)) 
AND ("line_items"."product_id" IN (2, 4, 1, 5, 9)) 
GROUP BY product_id 
ORDER BY product_id ASC

What I need, though, is the product title instead of the ID. Not having much luck. I tried using .joins -- but it didn't seem to work, because of the sum method in the query.

Answer 1

Try the order by the product title like this:

Updated the code

@line_items_totals = LineItem.where(:order_id => @orders, :product_id => @author_products).joins(:product).order("product_id ASC").group('products.title').sum(:quantity)

This should be equal with this:

@line_items_totals = LineItem.where(:order_id => @orders, :product_id => @author_products).joins(:product).order("product_id ASC").sum(:quantity, :group => 'products.title')

Answer 2

I'd do:

ListItem.other_stuff.joins(:product).group(:product_id).select('sum(line_items.quantity) as total_quantity, products.title')

or

LineItem.other_stuff.group(:product_id).select('sum(quantity) as total_quantity, (select title from products where products.id == product_id) as title)')

then you can access title and total_quantity as LineItem properties:

item.title
item.total_quantity.to_i

ActiveRecord or adapter dynamically define properties according to selected columns, even though there are no such columns in the table. Also notice, that for unknown reason MySQL adapter creates this dynamic attributes with String type, instead of Fixnum as you would expect (while SQLite adapter uses proper types). So you have to cast it manually, like i shown above.