4

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.

Community
  • 1
  • 1
Cbas
  • 6,003
  • 11
  • 56
  • 87
  • The error output you are getting basically tells you what to fix. Have you tried doing that? – Tim Biegeleisen May 18 '16 at 00:21
  • could you explain what the basic solution is? – Cbas May 18 '16 at 00:43
  • Your second `GROUP BY` query is a bit messy. Could you post some sample data along with what your desired result is? This is more of a Postgres problem than a Ruby problem. – Tim Biegeleisen May 18 '16 at 03:25
  • Ok I added more info at the bottom. I think I was confused b/c I usually use WHERE queries and they return all the attributes. With SELECT you have to specify which attributes to return or use a * right? If so, how can I work all those other attributes into the DISTINCT query? – Cbas May 18 '16 at 05:33
  • Your distinct query is fine now. As for the `GROUP BY` query, you can only include columns which are either appear in the `GROUP BY` clause or which are aggregates (e.g. `SUM`, `COUNT`) of columns. – Tim Biegeleisen May 18 '16 at 06:15

1 Answers1

3

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • that runs Ok but now I get an error related to an attribute not existing when I try to print out the message. Do I have to include every attribute I want to retrieve in both select and group? If so, is there a cleaner way to write this if I have say 12 attributes to retrieve? – Cbas May 18 '16 at 00:26
  • @Cbas In Postgres you must include columns you select in the `GROUP BY` clause _unless_ they appear inside aggregate functions (e.g. `SUM`). Your error may be a general SQL problem rather than a limitation in my answer. – Tim Biegeleisen Jul 15 '16 at 10:45
  • Ok thanks, I was able to fix that problem, but now I'm getting back a list of ALL messages, not just the newest from each sender. Is there a way to prevent getting back multiple messages from the same users? I copied and pasted the code into the update – Cbas Jul 15 '16 at 22:45
  • In order to answer this I am going to need to see sample input and desired output. You might want to ask a new question since few people will be paying attention to this question now. – Tim Biegeleisen Jul 16 '16 at 00:43