我正在尝试查询所有的站点,并关联测量数据
但是我只想要最近的一次测量(按created_at降序排序),因为一个站点可能有成千上万条测量数据。
我已经尝试过
Station.joins(:measures).limit(1)
但这只是限制了站点。
附加信息:
站点有许多措施。
措施属于站点。
我已经阅读了Active Records docs,但只有有关在关联上使用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