ruby-on-rails - rails 4 中的多级连接

Question

我想在 rails 4 中做这个查询

select r.region_id, r.region_name from countries c, zones z, regions r where c.country_id = $country_id (pass as parameter) and c.country_id = z.zone_id and z.subzone_id = r.region_id

模型:

 #Country.rb
class Country < ActiveRecord::Base
  has_one :place, foreign_key: :place_id
  has_many :zones , foreign_key: :place_id
  has_many :subzones, :through => :zones
end

#Zone.rb
class Zone < ActiveRecord::Base
 belongs_to :place
 belongs_to :subzone, :class_name => 'Place' , :foreign_key => :subzone_id
end

#Region.rb
class Region < ActiveRecord::Base
  has_one :place , foreign_key: :place_id
end

#Place.rb
class Place < ActiveRecord::Base
  belongs_to :region, :foreign_key => :place_id
  has_many :zones, :foreign_key => :place_id
  has_many :subzones, :through => :zones
end

我试过这个:

Country.joins(:zones).where("zone.subzone_id = regions.region_id AND country_id = ?",$country_id )

但出现错误:

Java::JavaSql::SQLSyntaxErrorException: ORA-00904: "REGIONS"."REGION_ID": invalid identifier.....

不确定如何在上述查询中加载区域...

提前致谢:-)

Answer 1

Region.joins(zones: :country).where(country: {country_id: $country_id})

这只有在你有这样的模型时才有效:

#Country.rb
class Country < ActiveRecord::Base
  has_many :zones, as: :zone
end
#Zone.rb
class Zone < ActiveRecord::Base
  has_many :regions, as: :region
  belongs_to :country, foreign_key: :zone_id
end

#Region.rb
class Region < ActiveRecord::Base
  belongs_to :zone, foreign_key: :region_id
end