mysql - Ruby on Rails 使用 WHERE 子句丰富了许多 :many,

Question

我有一个现有数据库，正在尝试映射到 Ruby on Rails ActiveRecords。其中一张表是“丰富连接”表，其中预先计算了邮政编码和机场之间的距离。 “邮政编码”(字符)列是邮政编码数据库表的主键。表定义如下。

现在我有以下 Rails 模型(它们正在工作，因为我可以检索链接到邮政编码的所有机场):

邮政编码.rb:

class Zipcode < ActiveRecord::Base
    has_many :zipcode_airports, :foreign_key => :zipcode
    has_many :airports, :through => :zipcode_airports
end

邮政编码_机场.rb

class ZipcodeAirport < ActiveRecord::Base
    self.table_name = "airport_zip_distances"
    belongs_to :zipcode, :foreign_key => :zipcode
    belongs_to :airport
end

所以这有效:

irb(main):001:0> z = Zipcode.find("90210")
   Zipcode Load (0.7ms)  SELECT  `zipcodes`.* FROM `zipcodes` WHERE `zipcodes`.`zipcode` = '90210' LIMIT 1
=> #<Zipcode zipcode: "90210", latitude: 34.0901, longitude: -118.406>

就像这样:

irb(main):002:0> z.airports
  Airport Load (738.4ms)  SELECT `airports`.* FROM `airports` INNER JOIN `airport_zip_distances` ON `airports`.`airport_id` = `airport_zip_distances`.`airport_id` WHERE `airport_zip_distances`.`zipcode` = '90210'
=> #<ActiveRecord::Associations::CollectionProxy [#<Airport airport_id: 11, name: "RIO VISTA MUNI", latitude: 38.1933, longitude: -121.704>, #<Airport airport_id: 12, name: "MARBLE CANYON", latitude: 36.8108, longitude: -111.645>, #<Airport airport_id: 13, name: "MONTEREY RGNL", latitude: 36.587, longitude: -121.843>, #<Airport airport_id: 14, name: "HOOPA", latitude: 41.0415, longitude: -123.668>, #<Airport airport_id: 15, name: "LEACH", latitude: 37.785, longitude: -106.047>, #<Airport airport_id: 16, name: "MANTI–EPHRAIM", latitude: 39.3315, longitude: -111.613>, #<Airport airport_id: 17, name: "MEADOWS FLD", latitude: 35.4338, longitude: -119.058>, #<Airport airport_id: 18, name: "GRAVELLY VALLEY", latitude: 39.4507, longitude: -122.955>, #<Airport airport_id: 19, name: "GUSTINE", latitude: 37.2605, longitude: -120.964>, #<Airport airport_id: 20, name: "CALIFORNIA PINES", latitude: 41.4122, longitude: -120.684>, ...]>

现在我正在尝试找出如何将返回的记录限制在那些位置，例如 airport_zip_distances.distance_in_miles < 7 英里。

这个查询有效，我只是不确定如何在 Rails 中正确设置所有内容以使其制定:

mysql> SELECT `airports`.* FROM `airports` INNER JOIN `airport_zip_distances` ON `airports`.`airport_id` = `airport_zip_distances`.`airport_id` WHERE `airport_zip_distances`.`zipcode` = '90210' AND `airport_zip_distances`.`distance_in_miles` < 7;
+------------+-------------------+----------+-----------+
| airport_id | name              | latitude | longitude |
+------------+-------------------+----------+-----------+
|         37 | SANTA MONICA MUNI |  34.0158 |  -118.451 |
+------------+-------------------+----------+-----------+

这是数据库表布局，如果重要的话......

mysql> describe airports;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| airport_id | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | YES  |     | NULL    |                |
| latitude   | float        | YES  |     | NULL    |                |
| longitude  | float        | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

mysql> describe zipcodes;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| zipcode   | char(5) | NO   | PRI | NULL    |       |
| latitude  | float   | YES  |     | NULL    |       |
| longitude | float   | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+

mysql> describe airport_zip_distances;
+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| id                | int(11) | NO   | PRI | NULL    | auto_increment |
| airport_id        | int(11) | NO   | MUL | NULL    |                |
| zipcode           | char(5) | YES  | MUL | NULL    |                |
| distance_in_miles | int(11) | YES  | MUL | NULL    |                |
+-------------------+---------+------+-----+---------+----------------+

帮忙？!谢谢!

Answer 1

这很简单，如果我们在 Airport 中定义一些范围，如下所示:

class Airport < ActiveRecord::Base
  scope :with_zipcode, -> (zipcode) { where(zipcode: zipcode) }
  scope :in_distance, -> (miles) {
    joins(:airport_zip_distances).where('airport_zip_distances.distance_in_miles < ?', miles)
}
end

之后，我们可以:

1. 查找带有邮政编码的机场

   Airport.with_zipcode(12345)
   

2. 查找距离机场

   Airport.in_distance(5)
   

3. 查找机场及其邮政编码和距离

   Airport.with_zipcode(12345).in_distance(5)