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.

Do you need more than the immediate children (grandchildren, grand-grandchildren etc)? — max, Sep 30 '16 at 02:20
The rails guides explain how to do this: http://guides.rubyonrails.org/association_basics.html#self-joins. There is also a railscast. — baron816, Oct 03 '16 at 18:29
But what does the scope look like to get the nearest future child of each parent? I keep getting SQL errors. — Jackson Cunningham, Oct 03 '16 at 18:32

score 1 · Answer 1 · answered Oct 07 '16 at 15:03

Your own answer looks good, but I would refine it the following way:

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

And very important or else in production you would always get the deployment date as Date.today because it will only reload in development:

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

score 1 · Accepted Answer · edited May 23 '17 at 11:48

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.

score 0 · Answer 3 · answered Oct 03 '16 at 19:48

0

I ended up using:

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

answered Oct 03 '16 at 19:48

Jackson Cunningham

4,973
3
30
80

Rails: Self referential parent/child hierarchy without through table

3 Answers3