Rails joins with limit on association

Question

I´m trying to do a query for all Stations, and join Measures

But I only want the most recent measure (ordered by created_at DESC) since a station has thousands of measures.

I have tried

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

but that just limits the Stations.

Additional info:

Station has many Measures

Measure belongs to Station

I have read through the Active Records docs and there is only information on using a where condition on associations.

The app only targets Postgres, SQL is accepted.

---

Edit: added except from 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

---

Addition This is the very hackhish code that is 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

Answer 1

I believe in Rails 4 you can apply a scope on the 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 need only the most recent one you can use has_one. It will work both for 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