Rails SQL: How to join belongs_to association in query that is referenced twice with two foreign keys

Question

In my app I have an Event model that basically represents a booking between two users. It has some basic attributes about the event as well as a booker_id and bookee_id which both represent foreign keys from the User table.

Basically I am trying to put together a search method for Events that allows me to search by the booker or bookee's first and last name. But I can't figure out how to reference BOTH the bookee user and the booker user in one single query. Below is my Event model showing the associations and a basic version of the Event search class method I've set up. Unfortunately, it only seems to reference the User table for Event bookers not bookees. I probably just need to review my SQL and write something a little uglier, but if anyone has a straightforward way to adjust that query so that it can include and reference the User table for both bookee users and booker users that would be super helpful.

event.rb

belongs_to :booker, :foreign_key => :booker_id, class_name: 'User'
belongs_to :bookee, :foreign_key => :bookee_id, class_name: 'User'

def self.search(search)
  if search
    search_term = "%#{search}%"
    includes(:booker, :bookee).where(["users.first_name ILIKE :term OR users.last_name ILIKE :term OR event_title ILIKE :term", {term: search_term}]).references(:booker, :bookee)
  end
 end

user.rb

  has_many :booker_events, :foreign_key => :booker_id, class_name: 'Event', dependent: :nullify
  has_many :bookee_events, :foreign_key => :bookee_id, class_name: 'Event', dependent: :nullify

Answer 1

You can try with a custom join trying to match those rows from the users table where their id is the same as the booker_id and/or bookee_id in the events table:

Event
  .joins('INNER JOIN users ON users.id IN (events.booker_id, events.bookee_id)')
  .where(
    'users.last_name  ILIKE :term OR
     users.first_name ILIKE :term OR
     event_title      ILIKE :term',
    term: term)