Geek Answers Handbook

Rails merges with merge constraint

I try to fulfill a request for all stations and join the Measures

But I need only the latest measure (ordered by create_at DESC), since the station has thousands of measures.

I tried

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

but it just limits the stations.

Additional Information:

The station has many measures.

Measurement refers to Station

I read Active Records docs , and there is only information about using the where clause for associations.

The application is only for Postgres, SQL accepted.

Edit: added with the exception of 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

Addendum This is the most hash code currently in use:

 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
+6
source share
3 answers

I believe in Rails 4, you can apply scope for association:

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

Then:

 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]]

Edit: Actually, if you only need the latter, you can use has_one . It will work for both Rails 4 and Rails 3 with slightly modified syntax:

 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
+4
source

Station.joins(:measures).group(:station_id).order("measures.created_at DESC")

0
source

If the measurement data is taken in a certain interval, you must filter the measurements by the created_at field.

0
source

More articles:


All Articles