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:
x
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?
Advertisement
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)