select sum of grouped rows in activerecord

Question

Having trouble translating a SQL query to ActiveRecord. Here's a simplified version of my domain modeling:

User # has_many :baskets

Basket # date:date, user_id:integer - has many :line_items, belongs_to :user

LineItem # quantity:integer - belongs_to :basket

I can do the following query in SQL:

SELECT baskets.date, SUM(line_items.quantity) from baskets
INNER JOIN line_items ON line_items.basket_id = basket.id
WHERE baskets.user_id = 2
GROUP BY baskets.id
ORDER BY baskets.date DESC

When running this query in PGAdmin, I get the two columns I want: basket dates and the sum of all the line_item quantities associated with that particular basket.

However, when I try to compose an activerecord query for the same data:

User.find(2).baskets
  .select('baskets.date,'SUM(line_items.quantity)')
  .joins(:line_items)
  .group('baskets.id')
  .order('baskets.date desc')

It returns the basket dates, but not the sums of the grouped line_item quantities. Ideally I'd want a result in the following format like { date=>quantity, date=>quantity ... }, but not sure how to get there.

Answer 1

Single-quotes are messing this up for you, this should work

User.find(2).baskets
  .joins(:line_items)
  .group('baskets.id')
  .order('baskets.date desc')
  .select("baskets.date, sum(line_items.quantity) as quantity_sum")

You can also use pluck which will return an array of date and sum

User.find(2).baskets
  .joins(:line_items)
  .group('baskets.id')
  .order('baskets.date desc')
  .pluck("baskets.date, sum(line_items.quantity) as quantity_sum")