Allen Stone Allen Stone - 2 years ago 185
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"

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). { |b| b.child_bookings.empty? }

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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download