CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
user2950593
user2950593

Reputation: 9627

Sum active record with expression rails

I have this code in my sql view:

SELECT
    reports.`date`
    reports.book_title
    SUM( reports.net_sold_count * reports.avg_list_price / 10 ) AS revenue
FROM
    reports
GROUP BY
    reports.date,
    reports.book_title

I need to translate it to ruby code

I have active record model 

class Report  < ActiveRecord::Base

Right now I have this code:

Report.group(:date, :book_title)

But I don't know where to go next because .sum in all examples accepts only one argument.(http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-sum)

UPD: Right now, I am thinking of trying something like

Report.select('date, book_title, SUM( reports.net_sold_count * reports.avg_list_price / 10 ) AS revenue').group(:date, :book_title)

Upvotes: 0

Views: 302

Answers (1)

mu is too short
mu is too short

Reputation: 434615

The #sum documentation is misleading. Yes, #sum only accepts one argument but that argument doesn't have to be a symbol corresponding to a column name, that argument can also be an SQL expression that uses the available columns. In particular, the argument to #sum could be the Ruby string:

'reports.net_sold_count * reports.avg_list_price / 10'

and so you could say:

Report.group(:date, :book_title).sum('reports.net_sold_count * reports.avg_list_price / 10')

That will give you a Hash that looks like:

{
  [some_date, some_title] => some_sum,
  ...
}

that you can pull apart as needed back in Ruby land.

Upvotes: 1

Related Questions