Rails 4 ActiveRecord query for empty association on self referential has_many association

Question

Here's the setup:

# app/models/booking.rb

class Booking < ActiveRecord::Base
  # Associations
  belongs_to :parent_booking, class_name: "Booking"
  has_many :child_bookings, class_name: "Booking", foreign_key: "parent_booking_id"
end

This is not a required relationship, so not all Bookings have a parent_booking or child_bookings.

Trying to create a query which returns an ActiveRecord::Relation of all Bookings which do NOT have any child_bookings, regardless of whether parent_booking is present or not.

The following example (based on this SO question) works for scenarios where the relationship is between different models. However, it does not work with the self referential relationship due to the fact that the records are in the same table. Instead, it returns an ActiveRecord::Relation of all Bookings which do not have a parent_booking.

Booking.includes(:child_bookings).where(bookings: { parent_booking_id: nil })

The following returns the correct objects, but is extremely inefficient as it has to query the database for every record. Also, it returns it as an array instead of an ActiveRecord::Relation (more concerned with the inefficiency).

Booking.select { |b| b.child_bookings.empty? }

I'm quite possibly overlooking a simple solution, but extensive searching hasn't turned up anything yet. Thanks!

I think this should work : `Booking.where({ parent_booking_id: nil })` — Muhammad Ali, Apr 28 '16 at 21:01
@MuhammadYawarAli That returns Bookings that have no **parent**; I need to return Bookings that have no **children**. — Allen Stone, Apr 28 '16 at 21:08

score 2 · Accepted Answer · answered Apr 28 '16 at 21:34

2

I'd add a counter cache and then query Booking.where(child_booking_count: 0)

answered Apr 28 '16 at 21:34

Brian

5,300
2
26
32

1

Thanks! This worked perfectly. Marked as solution as it solved both the efficiency issue AND returned as ActiveRecord::Relation. This will also help other areas querying the size of the association. Good stuff. – Allen Stone Apr 28 '16 at 21:59

score 2 · Answer 2 · answered Apr 28 '16 at 21:42

2

I think the inefficiency comes from the eager loading. If you just do a standard left join I think you'll find performance to be fine:

Booking.joins("LEFT JOIN bookings child_bookings ON child_bookings.parent_booking_id = bookings.id").where(child_bookings: {id: nil})

I know you lose the ability to use the AR association, but ActiveRecord has not yet decided to include left joins. If you want them, I've made a gist here that you could use to add it and the additional method :without that was made with this type of thing in mind: then you could write Booking.without(:child_bookings)

answered Apr 28 '16 at 21:42

Andrew Schwartz

4,440
3
25
58

Makes sense. Another answer kept the ability to use the AR association so I went with that, however I'll probably use this in other scenarios where the AR association isn't important. Thanks! – Allen Stone Apr 28 '16 at 22:00
Sure! The counter cache is a good solution and will be even quicker than this after it's set up since it only has to query one table without any joins. – Andrew Schwartz Apr 28 '16 at 22:39

score 1 · Answer 3 · answered Apr 28 '16 at 21:42

1

One way to query 0 child records is :

parent_ids = Booking.parent_booking_ids
Booking.where('id NOT IN (?)',parent_ids)

Hopefully it will return the required records.

answered Apr 28 '16 at 21:42

Muhammad Ali

2,173
15
20

Rails 4 ActiveRecord query for empty association on self referential has_many association

3 Answers3