Rails, use custom SQL query to populate ActiveRecord model

Question

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.

spike · Accepted Answer · 2015-11-21 22:30:48Z

2

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.

answered Nov 21, 2015 at 22:30

spike

10.1k9 gold badges57 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Steve Zelaznik Over a year ago

The problem with this approach is that most database views cannot be indexed. This means that to find the record you have to perform a linear scan on the view. I believe Microsoft SQL Server allows indexed views. Postgres, as far as I know, does not.

spike Over a year ago

@SteveZelaznik that's not totally true. You can't create an index on a view unless it's materialized, true, but postgres should use indices on the underlying tables in a normal view.

Steve Zelaznik · Accepted Answer · 2015-11-22 05:32:41Z

0

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

answered Nov 22, 2015 at 5:32

Steve Zelaznik

6167 silver badges16 bronze badges

Collectives™ on Stack Overflow

Rails, use custom SQL query to populate ActiveRecord model

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related