Rails的加入限制了关联

max 发表于 Dev

最高

我正在尝试查询所有测站，并加入测度

但是我只想要最近的度量（由created_at DESC排序），因为一个台站有成千上万的度量。

我试过了

Station.joins(:measures).limit(1)

但这仅限制了站点。

附加信息：

车站有很多措施

措施属于车站

我已经阅读了Active Records文档，只有关于在关联上使用where条件的信息。

该应用程序仅针对Postgres，接受SQL。

编辑：添加从schema.rb除外：

  create_table "measures", force: true do |t|
    t.integer  "station_id"
    t.float    "speed"
    t.float    "direction"
    t.float    "max_wind_speed"
    t.float    "min_wind_speed"
    t.float    "temperature"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.float    "speed_calibration"
  end

  add_index "observations", ["created_at"], name: "index_observations_on_created_at", using: :btree
  add_index "observations", ["station_id"], name: "index_observations_on_station_id", using: :btree

  create_table "stations", force: true do |t|
    t.string   "name"
    t.string   "hw_id"
    t.float    "latitude"
    t.float    "longitude"
    t.float    "balance"
    t.boolean  "offline"
    t.string   "timezone"
    t.integer  "user_id"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.string   "slug"
    t.boolean  "show",                         default: true
    t.float    "speed_calibration",            default: 1.0
    t.datetime "last_observation_received_at"
  end

另外，这是当前正在使用的非常简单的代码：

def all_with_latest_measure
  if user_signed_in? && current_user.has_role?(:admin)
    stations = Station.all.load
  end
  stations ||= Station.where(show: true).load

  if stations.size
    ids = stations.map { |s| s.id }.join(',')
    where = "WHERE m.station_id IN(#{ids})" unless ids.empty?
    measures = Measure.find_by_sql(%Q{
    SELECT DISTINCT ON(m.station_id, m.created_at)
      m.*
    FROM measures m
    #{where}
    ORDER BY m.created_at DESC
    })

    stations.each do |station|
      # Setup has_many relationship between station and Measure
      # Prevents n+1 queries
      measure = Measures.find { |m| m.station_id == station.id  }
      if measure
        measure.station = station
        station.latest_measure = measure
      end
    end
  end
end

联合收割机

我相信在Rails 4中，您可以在关联上应用范围：

class Stations
  has_many :measures, -> { order('created_at DESC').limit(1)  }
end

然后：

2.0.0-p353 :008 > Station.first.measures
  Station Load (0.1ms)  SELECT "stations".* FROM "stations" ORDER BY "stations"."id" ASC LIMIT 1
  Measure Load (0.1ms)  SELECT "measures".* FROM "measures" WHERE "measures"."station_id" = ? ORDER BY created_at DESC LIMIT 1  [["station_id", 1]]

编辑：实际上，如果您只需要最新的，就可以使用has_one。稍作修改后的语法即可在Rails 4和Rails 3上使用：

class Stations
  has_one :recent_measure, -> { order('created_at DESC')  }, class_name: 'Measure' # Rails 4
  has_one :recent_measure, order: 'created_at DESC', class_name: 'Measure' # Rails 3
end

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。