Rails joins through association

Question

In Ruby on Rails, I want to find employers in the city. Lets say the models are set up this way:

City has_many :suburbs has_many :households, :through => suburbs has_many :people, :through => suburbs  Suburb has_many :households has_many people, :through => households belongs_to :city   Household has_many :people belongs_to :suburb  People belongs_to :household belongs_to :employer   Employer has_many :people 

I feel like I want some sort of Employer joins some_city.people but I don't know how to do this. If people belonged directly to cities, I could join Employer to people where city_id is something, but I want to find the same data without that direct join and I am a little lost.

Thank you.

Answer 1

Use nested joins

Employer.joins({:people => {:household => {:suburb => :city}}})  

should give you the join table you're looking. If you were traversing the other direction you would use plural names

City.joins( :suburbs => {:households => {:people => :employers }}) 

Answer 2

You can do the join like jvans has illustrated. Or you can setup your relationships like the following:

class Employer < ActiveRecord::Base   has_many :people   has_many :households, through: :people   has_many :suburbs, through: :households   has_many :cities, through: :suburbs end  class Person < ActiveRecord::Base   belongs_to :household   belongs_to :employer end   class Household < ActiveRecord::Base   belongs_to :suburb   has_many :people end  class Suburb < ActiveRecord::Base   belongs_to :city   has_many :households   has_many :people, through: :households end  class City < ActiveRecord::Base   has_many :suburbs   has_many :households, through: :suburbs   has_many :people, through: :households   has_many :employers, through: :people end 

Then you can join City from Employer, and vice-versa, directly.

For example:

Employer.joins(:cities).where("cities.name = ?", "Houston").first  SELECT "employers".* FROM "employers"  INNER JOIN "people" ON "people"."employer_id" = "employers"."id"  INNER JOIN "households" ON "households"."id" = "people"."household_id"  INNER JOIN "suburbs" ON "suburbs"."id" = "households"."suburb_id"  INNER JOIN "cities" ON "cities"."id" = "suburbs"."city_id" WHERE (cities.name = 'Houston')  LIMIT 1