Allen Stone Allen Stone - 7 months ago 73
Ruby Question

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

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!

Answer

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