Monday, October 10, 2016

Rails: Self referential parent/child hierarchy without through table

By Hường Hana 11:00 AM , Leave a Comment

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.

3 Answers

Answers 1

I ended up using:

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

Answers 2

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 }) }

Answers 3

Ignoring Rails for a moment, what you are doing in SQL is the 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.

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment