Rails Active Record: Joining single table by multiple custom associations pointing to same column

Question

I am trying to join to the same table by multiple custom associations.

My model has the following associations,

class Order < ApplicationRecord
  belongs_to :advertiser, class_name: 'Account'
  belongs_to :facilitator, class_name: 'Account'
  belongs_to :creator, class_name: 'Account'

how can I create the following SQL query using Active Record and/or Arel? (beyond just providing an SQL string directly)

INNER JOIN accounts ON accounts.id = orders.facilitator_id
OR accounts.id = orders.creator_id
OR accounts.id = orders.advertiser_id

I tried the following,

Order.joins(:advertiser, :facilitator, :creator)

but got the following result

SELECT `orders`.*
FROM `orders`
INNER JOIN `accounts` ON `accounts`.`id` = `orders`.`advertiser_id`
INNER JOIN `accounts` `facilitators_orders` ON `facilitators_orders`.`id` = `orders`.`facilitator_id`
INNER JOIN `accounts` `creators_orders` ON `creators_orders`.`id` = `orders`.`creator_id`

Answer 1

You need to specify foreign keys of has_many relations Account model.

class Order < ApplicationRecord
  belongs_to :advertiser, class_name: 'Account'
  belongs_to :facilitator, class_name: 'Account'
  belongs_to :creator, class_name: 'Account'
end

In Account model

class Account < ApplicationRecord
   has_many :advertisers,  foreign_key: 'advertiser_id', class_name: 'Order'
   has_many :facilitators, foreign_key: 'facilitator_id', class_name: 'Order'
   has_many :creators,     foreign_key: 'creator_id', class_name: 'Order'
end

This similar question might help too! Multiple Associations With the Same Table rails