Rails order by attribute of last record from has_many association

Question

I have two models with belongs_to and has_many relationship. BreedCycle has breed_start_date datetime attribute. What I am trying to do is to order boxes by newest(last created) breed cycle's breed_start_date attribute. Long story short, my goal is to write query that pulls out all newest breed_cycles for the boxes and order boxes by breed_cycles.breed_start_date attribute. Also breed_start_date can be nil so I need NULLS LAST option. Any help(link to docs, examples etc.) is greatly appreciated.

class Box < ApplicationRecord
  has_many :breed_cycles
end

class BreedCycle < ApplicationRecord
  belongs_to :box
end

Answer 1

Box.left_joins(:breed_cycles)
   .group(:id) 
   .order('MAX(breed_cycles.breed_start_date) DESC NULLS LAST')

Or with Arel in Rails 6.1+:

Box.left_joins(:breed_cycles)
   .group(:id)
   .order(BreedCycle.arel_table[:breed_start_date].maximum.desc.nulls_last)

Answer 2

Adding a lateral-join option as an extension to Max's answer.

So - a lateral join is type of join supported by postgreSQL that's like a correlated sub-query, but more performant. I most commonly use it for situations like this one - where i have a "has many" but I only want to join a single one. Functionally they run just like how you'd expect a "for-loop" to operate - for each row in the table you're joining from, execute the lateral join and add in any rows returned.

Anyway, the lateral join SQL for this situation would look something like:

SELECT * from boxes 
LEFT JOIN LATERAL ( 
  SELECT breed_cycles.breed_start_date 
  FROM breed_cycles 
  ORDER BY breed_start_date DESC 
  LIMIT 1
) AS last_breed_cycle ON true
ORDER BY last_breed_cycle.breed_start_date DESC NULLS LAST

Turning that into activerecord would be something like:

lateral_join_sql = <<-SQL-
LEFT JOIN LATERAL ( 
  SELECT breed_cycles.breed_start_date 
  FROM breed_cycles 
  ORDER BY breed_start_date DESC 
  LIMIT 1
) AS last_breed_cycle ON true
-SQL
Box.joins(lateral_join_sql).order('last_breed_cycle.breed_start_date DESC')

I almost never use Arel so I'll leave that one as an exercise for the reader...

Answer 3

try this. I don't have PG running to I can't test the nulls last piece, but I'm sure you can extrapolate...

Box.joins(:breed_cycles).
    select("boxes.*, max(breed_cycles.created_at) as most_recent_breed_cycle").
    group("breed_cycles.box_id").
    order("most_recent_breed_cycle")

does it give you what you need?