How do I build a query in Ruby on Rails that joins on the max of a has_many relation only and includes a select filter on that relation?

Question

I'm struggling how to have Ruby on Rails do this query right... in short: to join on a has_many relation but only via the most recent record in that relation and then can apply a filter/select on that relation.

Here's a super simple variant that captures my struggle:

---

Let's say I have a table of Employees and a table of Employments. An employee has_many employments. An employment has a status of :active or :inactive.

class Employee < ActiveRecord::Base
  has_many :employments
end

class Employment < ActiveRecord::Base
  belongs_to :employee
end

To keep it simple, let's say there is one employee: Dan and he has two employments: an old one (by created_at) that is :inactive and a new one that is :active.

dan = Employee.create(name: 'Dan')
Employment.create(employee: dan, created_at: 2.years.ago, status: :inactive)
Employment.create(employee: dan, created_at: 3.months.ago, status: :active)

So in effect, you could say: "Dan has worked twice and is currently actively employed."

What I want is the Rails query for saying: "find me the employees who are inactive". And that should return an empty set because Dan's latest employment is :active. So I can't just do: Employee.joins(:employments).where(employments: { status: :inactive }) because it would match the old employment and thus return the Dan employee record.

I need a way to say: "find the employees who are inactive based on the most recent employment record only".

But I don't know how to do that in Rails.

I feel like I'm missing something... that it should be quite simple... but I can't figure it out.

Thanks!

Answer 1

I struggled quite a bit with the exact same issue in an application with a huge amount of rows and after trying various novel solutions like lateral joins and subqueries the best performing and by far simplest solution was just to add a foreign key to the table that points to the latest row and use an association callback (or a db trigger) to set the foreign key.

class AddLatestEmploymentToEmployees < ActiveRecord::Migration[6.0]
  def change
    add_reference :employees, :latest_employment, foreign_key: { to_table: :employments }
  end
end

class Employee < ActiveRecord::Base
  has_many :employments, after_add: :set_latest_employment
  belongs_to :latest_employment, 
    class_name: 'Employment',
    optional: true

  private
  def set_latest_employment(employment)
    update_column(:latest_employment_id, employment.id)
  end 
end

---

Employee.joins(:latest_employment)
        .where(employments: { status: :active })

It really shines if the amount of associated records is huge like it was in my case as you can eager load the latest record without the memory issues which occur if you load the entire has_many association.