ruby-on-rails - 使用 postgres 在 rails 中使用 DISTINCT ids 进行排序

Question

我有以下代码将两个表微博和事件与 micropost_id 列连接起来，然后根据具有不同微博 ID 的事件表的 created_at 进行排序。

 Micropost.joins("INNER JOIN activities ON 
 (activities.micropost_id = microposts.id)").
 where('activities.user_id= ?',id).order('activities.created_at DESC').
 select("DISTINCT (microposts.id), *")

应该返回整个微博专栏。这在我的开发环境中不起作用。

(PG::InvalidColumnReference: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

如果我在 SELECT DISTINCT 中添加 activities.created_at，我会得到重复的微博 ID，因为它们有不同的 activities.created_at 列。我做了很多搜索才能到达这里。但问题始终存在，因为这个 postgres 条件避免了随机选择。

我想根据具有不同微博 _id 的 activities.created_at 的顺序进行选择。

请帮忙..

Answer 1

首先，我们需要快速介绍一下 SELECT DISTINCT 实际在做什么。它看起来只是一个很好的关键字，可以确保您只返回不同的值，这不应该改变任何东西，对吧？除非您发现，在幕后，SELECT DISTINCT 实际上更像是 GROUP BY。如果您想选择某些东西的不同值，您只能按照您选择的相同值对该结果集进行排序——否则，Postgres 不知道该怎么做。

要解释歧义的来源，请考虑您的事件的这组简单数据:

CREATE TABLE activities (
  id INTEGER PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE,
  micropost_id INTEGER REFERENCES microposts(id)
);
INSERT INTO activities (id, created_at, micropost_id)
VALUES (1, current_timestamp,                      1),
       (2, current_timestamp - interval '3 hours', 1),
       (3, current_timestamp - interval '2 hours', 2)

您在问题中声明您希望“不同的微博 ID”“基于 activities.created_at 的顺序”。通过降序排列这些事件很容易 created_at (1, 3, 2)，但是 1 和 2 具有相同的 micropost_id 1。那么，如果您希望查询只返回微博 ID，应该返回 1, 2 还是 2, 1？

如果您可以回答上述问题，则需要将这样做的逻辑移至您的查询中。比方说，我认为这很可能是您希望这是最近采取行动的微博列表。在这种情况下，您希望按最近事件 的降序对微博进行排序。 Postgres 可以通过多种方式为您做到这一点，但我认为最简单的方法是:

SELECT micropost_id
FROM activities
JOIN microposts ON activities.micropost_id = microposts.id
GROUP BY micropost_id
ORDER BY MAX(activities.created_at) DESC

请注意，我放弃了 SELECT DISTINCT 位以支持使用 GROUP BY，因为 Postgres 可以更好地处理它们。 MAX(activities.created_at) 位告诉 Postgres，对于具有相同 micropost_id 的每组事件，仅按最近的排序。

您可以像这样将上面的内容翻译成 Rails:

Micropost.select('microposts.*')
  .joins("JOIN activities ON activities.micropost_id = microposts.id")
  .where('activities.user_id' => id)
  .group('microposts.id')
  .order('MAX(activities.created_at) DESC')

希望对您有所帮助!你可以玩弄 this sqlFiddle如果您想了解有关查询工作原理的更多信息。