Multiple Joins with Foreign Keys

models.rb

class User < ApplicationRecord
  has_many :created_budgets, class_name: "Budget", foreign_key: "creator_id"
  has_many :client_budgets, class_name: "Budget", foreign_key: "client_id"
end

class Bugdet < ApplicationRecord
  belongs_to :creator, class_name: "User"
  belongs_to :client, class_name: "User"
end

queries.rb

# The joins clause requires the ActiveRecord name of the relation, whereas
# the where clause requires the actual reference to the table
Budget.joins(:client).where(users: { state: "active" } )

# As you can see in the second example below, using the AR name results in incorrect SQL:
Budget.joins(:client).where(users: { state: "active" } ).to_sql
# => "SELECT \"budgets\".* FROM \"budgets\" INNER JOIN \"users\" ON \"users\".\"id\" = \"budgets\".\"client_id\" WHERE \"users\".\"state\" = 'active'"
Budget.joins(:client).where(clients: { state: "active" } ).to_sql
# => "SELECT \"budgets\".* FROM \"budgets\" INNER JOIN \"users\" ON \"users\".\"id\" = \"budgets\".\"client_id\" WHERE \"clients\".\"state\" = 'active'"


# When joining on the same table twice (using different foreign keys), Rails will alias the second reference for you
# The alias will be the pluralized AR relation (`clients`) prepended to the pluralized class name of the table being joined on (`budgets`)
# e.g. `clients_budgets`
Budget.joins(:creator, :client).to_sql
# => "SELECT \"budgets\".* FROM \"budgets\" INNER JOIN \"users\" ON \"users\".\"id\" = \"budgets\".\"creator_id\" INNER JOIN \"users\" \"clients_budgets\" ON \"clients_budgets\".\"id\" = \"budgets\".\"client_id\""

# To run where queries, use the the aliased table names:
Budget.joins(:creator, :client).where(users: { state: "active" }, clients_budgets: { state: "active" } )

Budget.joins(:creator, :client).where(users: { state: "active" }, clients_budgets: { state: "active" } ).to_sql
# => "SELECT \"budgets\".* FROM \"budgets\" INNER JOIN \"users\" ON \"users\".\"id\" = \"budgets\".\"creator_id\" INNER JOIN \"users\" \"clients_budgets\" ON \"clients_budgets\".\"id\" = \"budgets\".\"client_id\" WHERE \"users\".\"state\" = 'active' AND \"clients_budgets\".\"state\" = 'active'"