CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4
Dobabeswe
Dobabeswe

Reputation: 103

Query across multiple associations

I have the following classes: 

class School
  belongs_to :zipcode
end

class Zipcode
  has_many :schools
  belongs_to :city
end

class City
  has_many :zipcodes
  belongs_to :county
end

class County
  has_many :cities
  belongs_to :state
end

class State
  has_many :counties
end

Given a state id, how do I find all the schools located within said state?

Upvotes: 0

Views: 58

Answers (4)

usha
usha

Reputation: 29349

Another possible solution. If you are using Rails 3.1 or higher, you can try nested has_many :through

class School
  belongs_to :zipcode
end

class Zipcode
  has_many :schools
  belongs_to :city
end

class City
  has_many :zipcodes
  has_many :schools, :through => :zipcodes
  belongs_to :county
end

class County
  has_many :cities
  belongs_to :state
  has_many :schools, :through => :cities
end

class State
  has_many :counties
  has_many :schools, :through => :counties
end

Now you can just say

State.find(<id>).schools

Which internally fires this query

SELECT "schools".* FROM "schools" INNER JOIN "zipcodes" ON "schools"."zipcode_id" = "zipcodes"."id" INNER JOIN "cities" ON "zipcodes"."city_id" = "cities"."id" INNER JOIN "counties" ON "cities"."county_id" = "counties"."id" WHERE "counties"."state_id" = 1

Upvotes: 1

CDub
CDub

Reputation: 13354

I'd recommend using joins... Something like:

School.joins(zipcode: { city: { county: :state } }).where("state.id" => <state_id>)

Upvotes: 0

meatherly
meatherly

Reputation: 1861

Or you can make a scope to make things easier if you're doing this query a lot. Something:

scope :where_state_is, ->(state_name) { joins(:zipcode, :city, :county, :state).where("state.name = ?", state_name ) } 

School.where_state_is('NY')

Upvotes: 0

Carlos Ramirez III
Carlos Ramirez III

Reputation: 7434

You will need to use a long joins with a where clause like this

School.joins(zipcode: { city: { county: :state } }).where(states: { id: <STATE_ID> })

Upvotes: 1

Related Questions