CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4
Jeremy Thomas
Jeremy Thomas

Reputation: 6674

Return calculated value with rails query

I have a query:

@clients = Client.where(location_id: location).includes(:check_ins, :payments, :purchases).where("check_ins.created_at > ?", start_date).references(:check_ins)

that returns an ActiveRecord::Relation array of clients. Each client has_many :payments and each payment has an :amount.

For each client in the ActiveRecord::Relation, I'd like to include the sum of their payment amounts client.payments.sum(:amount). How can I get that returned in the array?

Upvotes: 0

Views: 483

Answers (1)

Jay-Ar Polidario
Jay-Ar Polidario

Reputation: 6603

Direct Solution

You can just always write a helper method:

class Client
  has_many: payments

  def total_payment_amount
    payments.sum(:amount)
  end
end

Then in your given code, you could just call it like the following or whereever you are gonna call it (like in the views for example):

@clients = Client.where(location_id: location).includes(:check_ins, :payments, :purchases).where("check_ins.created_at > ?", start_date).references(:check_ins)

@clients.each do |client|
  puts client.total_payment_amount
end

SQL Query Solution

Or, if you actually instead want this value as part of the SQL so that you can even query on this further then:

class Client < ApplicationRecord
  has_many :payments
end

Then in your given code:

@clients = Client.select('SUM(payments.amount) AS total_payment_amount').joins(:payments)

@clients.each do |client|
  puts client.total_payment_amount
end

Upvotes: 1

Related Questions