Dudo
Dudo

Reputation: 4169

ActiveRecord sort model on attribute of last has_many relation

I've been digging around for this for awhile... I can't find a graceful solution. I have loans and loans has_many :decisions. decisions has an attribute that I care about, called risk_rating.

I'd like to sort loans based on the most recent decision (based on created_at, per usual), but by the risk_rating.

Loan.includes(:decisions).references(:decisions).order('decisions.risk_rating DESC') doesn't work...

I want loans... sorted by their most recent decision's risk_rating. This seems like it should be easier than it is.

I'm currently doing this outside of the database like this, but it's chewing up time and memory:

Loan.all.sort do |x,y| 
  x.decisions.last.try(:risk_rating).to_f <=> y.decisions.last.try(:risk_rating).to_f
end

I'd like to show the performance I'm getting with the proposed answer, along with an inaccuracy...

Benchmark.bm do |x|
  x.report{ Loan.joins('LEFT JOIN decisions ON decisions.loan_id = loans.id').group('loans.id').order('MAX(decisions.risk_rating) DESC').limit(10).map{|l| l.decisions.last.try(:risk_rating)} }
end

    user     system      total        real
0.020000   0.000000   0.020000 ( 20.573096)

=> [0.936775, 0.934465, 0.932088, 0.922352, 0.921882, 0.794724, 0.919432, 0.918385, 0.916952, 0.914938]

The order isn't right. That 0.794724 is out of place.

To that extent... I'm only seeing one attribute in the proposed answer. I don't see the connection =/

Upvotes: 1

Views: 255

Answers (2)

coreyward
coreyward

Reputation: 80041

Alright, it looks like I'm working late tonight because I couldn't help but jump in:

class Loan < ApplicationRecord
  has_many :decisions
  has_one :latest_decision, -> { merge(Decision.latest) }, class_name: 'Decision'
end

class Decision < ApplicationRecord
  belongs_to :loan

  def latest
    t1 = arel_table
    t2 = arel_table.alias('t2')

    # Self join based on `loan_id` prefer latest `created_at`
    join_on = t1[:loan_id].eq(t2[:loan_id]).and(
      t1[:created_at].lt(t2[:created_at]))

    where(t2[:loan_id].eq(nil)).joins(
      t1.create_join(t2, t1.create_on(join_condition), Arel::Nodes::OuterJoin)
    )
  end
end

Loan.includes(:latest_decision)

This doesn't sort, just provides the latest decision for each loan. Throwing an order that references access_codes messes things up because of the table aliasing. I don't have the time to work that kink out now, but I bet you can figure it out if you check out some of the great resources on Arel and how to use it with ActiveRecord. I really enjoy this one.

Upvotes: 2

Ilya Lavrov
Ilya Lavrov

Reputation: 2860

At first let's write sql-query which will select necessary data. SO contains a question which may helps here: Select most recent row with GROUP BY in MySQL. My best version:

SELECT loans.*
FROM loans
LEFT JOIN (
  SELECT loan_id, MAX(id) as id
  FROM decisions
  GROUP BY loan_id) d ON d.loan_id = loans.id
LEFT JOIN decisions ON decisions.id = d.id
ORDER BY decisions.risk_rating DESC

This code suppose MAX(id) gives id of the recent row in group.

You may do the same query by this Rails code:

sub_query = 
  Decision.select('loan_id, MAX(id) as id').
    group(:loan_id).to_sql

Loan.
  joins("LEFT JOIN (#{sub_query}) d ON d.loan_id = loans.id").
  joins("LEFT JOIN decisions ON decisions.id = d.id").
  order("decisions.risk_rating DESC")

Unfortunately, I don't have MySQL at hand and I can't try this code. Hope it will work.

Upvotes: 1

Related Questions