CODEWITHSUNDEEP
sqlruby-on-railsrails-activerecord
user7280963
user7280963

Reputation: 115

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.

Upvotes: 8

Views: 13212

Answers (2)

OuttaSpaceTime
OuttaSpaceTime

Reputation: 976

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.

Upvotes: 0

Eyeslandic
Eyeslandic

Reputation: 14900

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")

Upvotes: 17

Related Questions