How to get the all unique value using foreign key in Rails

Question

I have a model sensor that stores environmental data from an Arduino(temperature, humidity).

create_table "sensors", force: :cascade do |t|
  t.integer "system_id"
  t.float "value"
  t.datetime "created_at"
end

I want to get the average value by all my devices. To do it I need to get the last values from all devices by system_id.

The result I would like to get should look like this:

 [#<Sensor:0x0000556dd6a5c758
  id: 10,
  sensor_id: 1,
  value: 14.0,
  created_at: Sat, 31 Jul 2021 18:41:13 UTC +00:00,
 #<Sensor:0x0000556dd6a6f448
  id: 149,
  sensor_id: 2,
  value: 22.0,
  created_at: Sat, 31 Jul 2021 18:41:13 UTC +00:00,
 #<Sensor:0x0000556dd6a6f238
  id: 329,
  sensor_id: 3,
  value: 11.0,
  created_at: Sat, 31 Jul 2021 18:41:13 UTC +00:00,
  ...]

or just array of values corresponding to the task conditions:

[14.0, 22.0, 11.0]

I try it like this:

Sensor.order('created_at DESC').distinct(:system_id)

But it doesn't work. Any idea how to do this?

Are you looking for `Sensor.select("system_id, max(created_at) as created_at").group(:system_id)` — Eyeslandic, Aug 04 '21 at 06:46
@Eyeslandic thanks, yes, this is almost what I want, but as a result, there are no values that I need `[ #, # — Roman, Aug 04 '21 at 08:34

score 0 · Answer 1 · edited Aug 04 '21 at 10:53

0

You try this.

Sensor.order('created_at DESC').pluck(:system_id).uniq

edited Aug 04 '21 at 10:53

Deepesh

6,138
1
24
41

answered Aug 04 '21 at 09:50

Sajid Ali

76
1

Deepesh · Answer 2 · 2021-08-04T12:27:28.247

You could try this to get the latest records:

Sensor.joins('INNER JOIN (SELECT system_id, MAX(created_at) AS max_date FROM sensors GROUP BY system_id) latestSensor ON latestSensor.system_id = sensors.system_id AND latestSensor.created_at = sensors.created_at')

This might return multiple rows for a system_id if the created_at is exactly the same for those records.

Source: https://stackoverflow.com/a/2411703/4207394

score 0 · Answer 3 · answered Aug 04 '21 at 11:26

You can do this. You will have to tweak this a bit probably to fit your situation exactly.

sensors = Sensor.select("system_id, max(created_at) as created_at").group(:system_id)

# To get values from this you can do something like
# This will give you an array like [[1, 2021-01-01], [2, 2021-01-01]]
sensors.to_a.pluck(:system_id, :created_at)

How to get the all unique value using foreign key in Rails

3 Answers3