Rails deeply nested joins

Question

I have the following models

class Doctor < ApplicationRecord
  has_many :practices
  has_many :facilities, through: :practices
  has_one :address, as: :addressable
  ...
end

class Facility < ApplicationRecord
  has_many :practices
  has_many :doctors, through: :practices
  has_one  :address, as: :addressable
end

class Practice < ApplicationRecord
  belongs_to :doctor
  belongs_to :facility
end

class Address < ApplicationRecord
  belongs_to :addressable, polymorphic: true
  belongs_to :city
  belongs_to :state
  belongs_to :country
end

A doctor can practice out of more than once facility in a cidy. I want to use joins (unless there is a more efficient way) to find a list of all doctors practicing in a city. I have written a nested joins as under

Doctor.joins(facilities: [address: :city]) # Works

I can't wrap my head around the problem of writing the where clause for specifying city name.

Doctor.joins(facilities: [address: [:city]]).where({facilities: {address: {city: {name: "Sydney"}}})

I get an error which says

Mysql2::Error: Unknown column 'address.name' in 'where clause'

Please help!

Answer 1

You have loaded city through address, you have the namespace in memory:

Doctor.joins(facilities: { address: :city }).where(cities: { name: city_name })