CODEWITHSUNDEEP
ruby-on-railsactiverecordrails-activerecord
leemour
leemour

Reputation: 12003

How to query has_one through relationship?

My models:
metro_station.rb

class MetroStation < ActiveRecord::Base
  belongs_to :metro_line
  has_one :city, through: :metro_line, autosave: false
end

metro_line.rb`

class MetroLine < ActiveRecord::Base
  belongs_to :city
  has_many :metro_stations
end

city.rb

class City < ActiveRecord::Base
  has_many :metro_lines
end

When I run:
MetroStation.where(city: City.first)
I get

PG::UndefinedColumn: ERROR:  column metro_stations.city_id does not exist
: SELECT "metro_stations".* FROM "metro_stations" WHERE "metro_stations"."city_id" = 1
(pry) output error: #<ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:  column metro_stations.city_id does not exist
LINE 1: ...CT "metro_stations".* FROM "metro_stations" WHERE "metro_sta...

While this query works:
MetroStation.joins(:metro_line).where(metro_lines: {city_id: 1})

Upvotes: 1

Views: 3166

Answers (2)

smile2day
smile2day

Reputation: 1665

To find out why your first approach doesn't work type in

MetroStation.where(city: City.first).to_sql

You should see something like

... WHERE metro_stations.city_id = 1

forming part of the query. The MetroStation model simply doesn't have the city_id attribute, as a result your first approach forms an invalid SQL statement.

The join works since it is filtering on the MetroLine table which has the relationship to the City model in form of the city_id field.

Nothing wrong with your models it is just the way Rails generates the SQL statements which in the world of SQL makes sense.

A has_many relationship on City to MetroStation through MetroLine delivers the desired results for your question (which metro stations are in a city).

Example

class City < ActiveRecord::Base
  has_many :metro_lines
  has_many :metro_stations, through: :metro_lines
end

class MetroLine < ActiveRecord::Base
  belongs_to :city
  has_many :metro_stations
end

class MetroStation < ActiveRecord::Base
  belongs_to :metro_line
  has_one :city, through: :metro_line
end


# Return all metro stations for a city
# * assuming City has name 'name' field
london_stations = City.find_by_name('London').metro_stations

Hope this helps!

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52356

City should also:

has_many :metro_stations, :through => :metro_lines

And then you write:

City.first.metro_stations

Upvotes: 1

Related Questions