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.

Apologies, it should not change, I have edited to fix that.

Chris
– Chris

2019-05-27 14:17:14 +00:00
Commented May 27, 2019 at 14:17 — Chris
– Chris, Commented May 27, 2019 at 14:17

Gordon Linoff · Accepted Answer · 2019-05-27 14:07:15Z

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;

answered May 27, 2019 at 14:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Chris Over a year ago

Thanks, I'd like to user ActiveRecord query if possible so I can pass in the station via parameters.

The Lazy Log · Accepted Answer · 2019-05-28 01:18:59Z

1

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

edited May 28, 2019 at 1:18

answered May 27, 2019 at 14:32

The Lazy Log

3,5742 gold badges23 silver badges30 bronze badges

2 Comments

Chris Over a year ago

Thanks, trying that ConnectedUser.select("DISTINCT ON (station_id, user_id)").where(station: station).order(created_at: :desc) gives this error for me though...

ActiveRecord::StatementInvalid (PG::SyntaxError: ERROR:  syntax error at or near "FROM") LINE 1: SELECT DISTINCT ON (station_id, user_id) FROM "connected_use...

The Lazy Log Over a year ago

Yeah, it seems I forgot character * in the select statement. Please see my updated answer and see if it works now

ogabriel · Accepted Answer · 2019-05-27 15:53:47Z

0

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

edited May 27, 2019 at 15:53

answered May 27, 2019 at 15:30

ogabriel

3013 silver badges7 bronze badges

4 Comments

Chris Over a year ago

Thanks, using uniq seems interesting but I think uniq has been deprecated now in favour of distinct. When trying it it doesn't seem to return the most recent records either—but ordering by created_at instead didn't seem to help.

Chris Over a year ago

Hi, with the edited version I'm now getting the familiar error again which I've been trying to resolve:

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

thanks for trying.

ogabriel Over a year ago

@Chris, and if you try to put id inside the group, like group(:id, :user_id) ?

Chris Over a year ago

Yeh, I've tried this approach and variations of it, without any luck. Thanks again.

Collectives™ on Stack Overflow

Grouping and ordering using ActiveRecord Query interface

3 Answers 3

1 Comment

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related