Rails: Self referential parent/child hierarchy without through table

Question

I have an Event model with parent_id and date attributes:

Event.rb

has_many :children, :class_name => "Event"
belongs_to :parent, :class_name => "Event" 

I have no issues calling event.parent or event.children. A child event never has a child itself.

I am trying to add a scope to this model so that I can return the child with the nearest future date for every parent. Something like:

scope :future, -> {
    where("date > ?", Date.today)
  }

scope :closest, -> {
    group('"parent_id"').having('date = MAX(date)')
 }

Event.future.closest ==> returns the closest child event from every parent

But the above :closest scope is returning more than one child per parent.

Answer 1

Ignoring Rails for a moment, what you are doing in SQL is the greatest-n-per-group problem. Here are lots of solutions. I would choose either DISTINCT ON or LEFT OUTER JOIN LATERAL. Here is how it might look in Rails:

scope :closest, -> {
  select("DISTINCT ON (parent_id) events.*").
    order("parent_id, date ASC")
}

This will give you the child objects. (You probably also want a condition to exclude rows with no parent_id.) From your own solutions, it sounds like that's what you want. If instead you want the parent objects, with an optional attached child object, then use a lateral join. That is a little trickier to translate into ActiveRecord though. If it's acceptable to do it in two queries, this looks like it should work (sticking with DISTINCT ON):

has_one :closest_child, -> {
  select("DISTINCT ON (parent_id) events.*").
    order("parent_id, date ASC")
}, class_name: Event, foreign_key: "parent_id"

Then you can say Event.includes(:closest_child). Again, you probably want to filter out all the non-parents though.