Rails: self-referencing parent / child hierarchy without 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 problem calling event.parent or event.children . A children's event never has a child.

I am trying to add an area for this model so that I can return a child with the nearest future date for each parent. Sort of:

 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 area :closest returns more than one child per parent.

Answer 1

Ignoring Rails for a moment, what you do in SQL is a lot of solutions . I would choose either DISTINCT ON or LEFT OUTER JOIN LATERAL . Here's what it looks like in Rails:

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

This will give you child objects . (You probably also want a condition to exclude strings without parent_id .) From your own decisions, this looks like what you want. If instead you want parent objects , with an extra child attached, use a side join. It is a little trickier to switch to ActiveRecord. If this is acceptable for this in two queries, it looks like it should work (adhering to 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 non-parents.

Answer 2

I ended up using:

  scope :closest, -> { where(id: Event.group(:parent_id).minimum(:date).keys) } 

Answer 3

Your own answer looks good, but I would clarify it as follows:

 scope :closest, -> { where.not(parent_id: nil).group(:parent_id).minimum(:date) } 

And it’s very important whether in production you always get the deployment date as Date.today , because it will only be reloaded in development:

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