Grouping and ordering using ActiveRecord Query interface

Question

I have a model, ConnectedUser, which belongs to 2 other models User and Station.

It is a simple model with just those relationships and a boolean active.

The results of the query I'm aiming for would be only each most recent ConnectedUser record for each User (where the Station is a specific id).

For example, if my ConnectedUser table looked like this...

+----+---------+------------+--------+------------+
| id | user_id | station_id | active | created_at |
+----+---------+------------+--------+------------+
|  1 |       1 |          1 | true   | 20 June    |
|  2 |       1 |          1 | false  | 19 June    |
|  3 |       1 |          2 | false  | 20 June    |
|  4 |       2 |          1 | false  | 18 June    |
|  5 |       2 |          1 | false  | 21 June    |
+----+---------+------------+--------+------------+

And the station was the one with id 1 then I'd like the query to return...

[
<ConnectedUser id: 1, user_id: 1, station_id: 1, active: true, created_at: "2019-06-20">,
<ConnectedUser id: 5, user_id: 2, station_id: 1, active: false, created_at: "2019-06-21">
]

To achieve this I've been trying to use group to and order

ConnectedUser.where(station: station).select(:user_id).group(:user_id).order(:created_at)

but have kept getting errors like this:

ActiveRecord::StatementInvalid (PG::GroupingError: ERROR: column "connected_users.created_at" must appear in the GROUP BY clause or be used in an aggregate function)

I am unable to get the specific ConnectedUser id, so feel like I'm missing some important understanding how to work with group and aggregate the results.

Is this possible in one ActiveRecord query?

Many thanks.

Answer 1

In Postgres, if you want the most recent version for each user/station, I would recommend distinct on:

select distinct on (station_id, user_id) cu.*
from ConnectedUser cu
order by station_id, user_id, created_at desc;

Answer 2

You should use DISTINCT ON instead of GROUP. Here is how you can do it in Rails:

ConnectedUser.select("DISTINCT ON (station_id, user_id) *")
             .where(station_id: params[:station_id])
             .order(created_at: :desc)

Of course with params[:station_id] is what you want to filter

Answer 3

Probaly, this would solve it

ConnectedUser.where(active: true).order(user_id: :desc).uniq(&:user_id)

order is used to sort the user_id field in DESC

on this solution, i am using uniq, which is a array solution, and will select just the most recent user_id

edit:

I was wrong, uniq didnt work as expected, but testing here, i discover that:

ConnectedUser.where(active: true).order(created_at: :desc).group(:user_id)

seems much like yours, but, when you use select, it does select just the field user_id, so, you can't order by created_at

Should work