How to count the sum of Invoices by last year using SQL in Rails?

I need to count all amount of invoices for last year. I do it using helper method but, as far as I know, there is better way using only one query to database with SQL function SUM().

My invoices table:

create_table "invoices", force: :cascade do |t|
t.string "title"
  t.text "description"
  t.string "category"
  t.decimal "amount"
  t.bigint "user_id", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["user_id"], name: "index_invoices_on_user_id"
end

My current method with multiple database queries and sum amount using Ruby:

# invoice.rb model has invoices_for_year scope

scope :invoices_for_year, -> { where('extract(year from created_at) = ?', Time.now.strftime("%Y").to_i) }

# invoices_controller.rb in index method has @invoices_for_year

invoices = Invoice.all.available_for(current_user)    
@invoices_for_year = invoices.invoices_for_year

# and inside helper I calculate the all amount
def count_year_spend
  @invoices_for_year.reduce(0) { | sum, invoice | sum + invoice.amount }
end

How to calculate the amount in one request using SQL inside Ruby?

Answer

I would try

Invoice
  .where(created_at: (Time.current.beginning_of_year..Time.current.end_of_year))
  .sum(:amount)

Or you could define a scope in your model like

scope :by_year, -> (year) { where('extract(year from created_at) = ?', year) }

and use it like this

Invoice.by_year(Date.today.year).sum(:amount)

Advertisement

Answer