'select sum of grouped rows in activerecord

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.

sqlruby-on-railsrails-activerecord


Solution 1:[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")

Solution 2:[2]

I had the same problem with group and joins, but my specific problem was that the sum column did not appear within my console. This confused me a lot.

Just as a simple example

irb(main):011:0> User.select("SUM(id) AS sum_id").first
  Activity Load (41.7ms)  SELECT SUM(id) AS sum_id FROM `activities` LIMIT 1
=> #<Activity >
irb(main):012:0> User.select("SUM(id) AS sum_id").first.sum_id
  Activity Load (0.4ms)  SELECT SUM(id) AS sum_id FROM `activities` LIMIT 1
=> 3111821699

So in fact sum_id is not shown within the console because it is not a property of the User model.

Check this if you are wondering, why you don't see the summed up column.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Eyeslandic
Solution 2 OuttaSpaceTime

Related Questions