CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresql
Cbas
Cbas

Reputation: 6213

Rails select distinct and order by most recent

I have a Notifications modal and a User modal where User has many Notifications and the notifications are sent between users. I need a function to get the newest notification from each user, and return them sorted by their timestamps.

I tried using SELECT DISTINCT .. ORDER BY:

@notifications = Notification.select("DISTINCT(notifier_id, created_at)").order('created_at DESC')

which generates this SQL: SELECT DISTINCT(notifier_id, created_at) FROM "notifications" ORDER BY "notifications"."created_at" DESC, created_at DESC)

but I get the error: SELECT DISTINCT, ORDER BY expressions must appear in select list

created_at does appear in the select list, so what's the problem?

Based on this post: PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

I tried using SELECT ... GROUP BY .. ORDER BY:

@notifications = Notification.select("notifier_id").group("notifier_id").order("MAX(created_at)")

which generates this SQL: SELECT "notifications"."notifier_id" FROM "notifications" GROUP BY notifier_id ORDER BY "notifications"."created_at" DESC, MAX(created_at)

but now I get this error: column "notifications.created_at" must appear in the GROUP BY clause or be used in an aggregate function

I am using an aggregate function, so what is the error about?

UPDATE:

Using this code:

@notifications = Notification.select("DISTINCT ON (created_at) created_at, notifier_id").order('created_at DESC')

I get back a list of all messages, instead of just the newest from each sender.

Upvotes: 4

Views: 6435

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Try this:

@messages = Message.select("DISTINCT ON (created_at) created_at, sender_id").order('created_at DESC')

From the documentation:

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

You will have to rework the second query per the error message.

Upvotes: 4

Related Questions