CODEWITHSUNDEEP
ruby-on-railsactiverecordorm
Steve Zelaznik
Steve Zelaznik

Reputation: 616

Rails, use custom SQL query to populate ActiveRecord model

I have a "Loan" model in Rails I'm trying to build. There's a corresponding "Payment" model. The balance on the loan is the original amount of the loan minus the sum of all the payments. Calculating the balance is easy, but I'm trying to calculate the balance on lots of loans while avoiding an N+1 query, while at the same time making the "balance" a property of the "Loan" model.

When I call the index method of the Loans controller, I can run a custom select query, which allows me to return a "balance" property through a straight SQL query.

class LoansController < ApplicationController
  def index
    @loans = Loan
    .joins("LEFT JOIN payments on payments.loan_id = loan.id")
    .group("loans.id")
    .select("loans.*, loans.amount - SUM(payments.amount) as balance")
  end
  def index_002
    @loans = Loan.includes(:payments)
  end
  def index_003
    @loans = Loan.includes(:payments)
  end
end

class Loan < ActiveRecord::Base
  has_many :payments
  def balance=(value)
    # I'd like balance to load automatically in the Loan model.
    raise NotImplementedError.new("Balance of a loan cannot be set directly.")
  end
  def balance_002
    # No N+1 query, but iterating through each payment in Ruby
    # is grossly inefficient as well
    amount - payments.map(:amount).inject(0, :+)
  end
  def balance_003
    # Even with the "includes" in the controller, this is N+1
    amount - (payments.sum(:amount) || 0)
  end
end

Now my question is how to do this all the time with my Loan model. Normally ActiveRecord loads one or more models using the following query:

SELECT * FROM loans
--where clause optional
WHERE id IN (?)

Is there any way to override the Loan model so that it loads the following query:

SELECT
  loans.*, loans.amount - SUM(payments.amount) as balance
FROM
  loans
LEFT JOIN
  payments ON payments.loan_id = loans.id
GROUP BY
  loans.id

This way "balance" is a property of the model and only has to be declared in one place, but we also avoid the inefficiencies of N+1 queries.

Upvotes: 1

Views: 2059

Answers (2)

Steve Zelaznik
Steve Zelaznik

Reputation: 616

It looks like I finally answered my own question. Here it is. I overrode the default scope.

class Loan < ActiveRecord::Base
  validates :funded_amount, presence: true, numericality: {greater_than: 0}
  has_many :payments, dependent: :destroy, inverse_of: :loan
  default_scope {
    joins("LEFT JOIN payments as p ON p.loan_id = loans.id")
    .group("loans.id").select("loans.*, sum(p.amount) as paid")
  }
  def balance
    funded_amount - (paid || 0)
  end
end

Upvotes: 0

spike
spike

Reputation: 10004

I like to use database views for this, so that rails thinks it's talking to a regular database table (ensuring things like eager loading work normally) when in fact there are aggregations or complex joins going on. In your case, I might define a second loan_balances view: 

create view loan_balances as (
  select loans.id as loan_id, loans.amount - sum(payments.amount) as balance
  from loans
  left outer join payments on payments.loan_id = loans.id 
  group by 1
)

Then just do regular rails association stuff:

class LoanBalance < ActiveRecord::Base
  belongs_to :loan, inverse_of: :loan_balance
end

class Loan < ActiveRecord::Base 
  has_one :loan_balance, inverse_of: :loan
  delegate :balance, to: :loan_balance, prefix: false
end

This way in actions where you want the balance you can eager load it with includes(:loan_balance), but you won't get into thorny issues from violating rails conventions in all the standard CRUD stuff surrounding Loan itself.

Upvotes: 2

Related Questions