Rails选择最近的去重并按顺序排列

Question

我有一个通知模态框和一个用户模态框，其中用户有许多通知，这些通知是在用户之间发送的。我需要一个函数来获取每个用户的最新通知，并按时间戳排序返回它们。

我尝试使用SELECT DISTINCT .. ORDER BY：

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

生成以下SQL语句：SELECT DISTINCT(notifier_id, created_at) FROM "notifications" ORDER BY "notifications"."created_at" DESC, created_at DESC

但我遇到了错误：SELECT DISTINCT，ORDER BY表达式必须出现在选择列表中

created_at已经出现在选择列表中，那么问题出在哪里呢？

根据这篇帖子：PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

我尝试使用SELECT ... GROUP BY .. ORDER BY：

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

生成这个SQL语句：

SELECT "notifications"."notifier_id" FROM "notifications" GROUP BY notifier_id ORDER BY "notifications"."created_at" DESC, MAX(created_at)

但是现在我遇到一个错误：column "notifications.created_at" must appear in the GROUP BY clause or be used in an aggregate function

我正在使用聚合函数，那么这个错误是关于什么的呢？

更新:

使用这段代码:

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

我返回了所有消息的列表，而不仅限于每个发件人最新的消息。

- Cbas

---

你所得到的错误输出基本上告诉你需要修复什么。你尝试过这样做了吗？ - Tim Biegeleisen

你能解释一下什么是基本解决方案吗？ - Cbas

你的第二个 GROUP BY 查询有点混乱。你能否提供一些示例数据以及你想要的结果？这更像是一个 Postgres 的问题，而不是 Ruby 的问题。 - Tim Biegeleisen

我在底部添加了更多信息。我认为我感到困惑是因为我通常使用 WHERE 查询并返回所有属性。使用 SELECT，您必须指定要返回哪些属性或使用 *，对吗？如果是这样，那么我该如何将所有其他属性纳入 DISTINCT 查询中？ - Cbas

您的独特查询现在很好。至于“GROUP BY”查询，您只能包括出现在“GROUP BY”子句中或是聚合列（例如SUM，COUNT）的列。 - Tim Biegeleisen

Answer 1

试试这个：

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

根据文档，DISTINCT ON表达式必须与最左边的ORDER BY表达式匹配。根据错误信息，您需要重新调整第二个查询。

- Tim Biegeleisen

---

程序运行正常，但现在当我尝试打印消息时出现了一个与属性不存在相关的错误。我必须在选择和分组中包含我想要检索的每个属性吗？如果是这样，如果我有12个属性要检索，是否有更简洁的编写方式？ - Cbas

@Cbas 在Postgres中，除非它们出现在聚合函数（例如SUM）内部，否则必须在GROUP BY子句中包含您选择的列。您的错误可能是一般SQL问题，而不是我的答案的限制。 - Tim Biegeleisen

好的，谢谢。我已经解决了那个问题，但现在我得到了所有消息的列表，而不仅仅是每个发送者的最新消息。有没有办法防止从同一用户获取多条消息？我将代码复制并粘贴到更新中。 - Cbas

为了回答这个问题，我需要看到样例输入和期望的输出。你可能需要提一个新问题，因为现在很少有人会关注这个问题。 - Tim Biegeleisen