使用postgres在导轨中使用DISTINCT ID进行订购 [英] Order with DISTINCT ids in rails with postgres

问题描述

我有以下代码通过micropost_id列连接两个表微博和活动，然后根据具有不同微博ID的活动表的created_at进行排序。

I have the following code to join two tables microposts and activities with micropost_id column and then order based on created_at of activities table with distinct micropost id.

 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), *")

应返回完整值micropost列。这在我的开发环境中不起作用。

which should return whole micropost columns.This is not working in my developement enviornment.

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

如果我在SELECT DISTINCT中添加activity.created_at，我将得到重复的微型帖子ids是因为具有不同的activity.created_at列。我已经做了很多搜索才能到达此处。但是由于这种postgres条件，避免了随机选择，因此问题始终存在。

If I add activities.created_at in SELECT DISTINCT, I will get repeated micropost ids because the have distinct activities.created_at column. I have done a lot of search to reach here. But the problem always persist because of this postgres condition to avoid random selection.

我要根据活动的顺序进行选择。created_at具有不同的微型帖子_id。

I want to select based on order of activities.created_at with distinct micropost _id.

请帮助。。

推荐答案

首先，我们需要快速介绍 SELECT DISTINCT 的实际操作。只是一个很好的关键字，以确保您只返回不同的值，而这不应该改变什么，对不对？除了发现之外， SELECT DISTINCT 在幕后实际上更像是 GROUP BY 。如果要选择不同的值，则只能按所选的相同值对结果集进行排序-否则，Postgres不知道该怎么做。

To start with, we need to quickly cover what SELECT DISTINCT is actually doing. It looks like just a nice keyword to make sure you only get back distinct values, which shouldn't change anything, right? Except as you're finding out, behind the scenes, SELECT DISTINCT is actually acting more like a GROUP BY. If you want to select distinct values of something, you can only order that result set by the same values you're selecting -- otherwise, Postgres doesn't know what to do.

要说明歧义的来源，请考虑以下活动的简单数据集：

To explain where the ambiguity comes from, consider this simple set of data for your activities:

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)

您在问题中说，您希望根据活动的顺序创建了不同的micropost_id。created_at。通过降序created_at（ 1、3、2 ）来排序这些活动很容易，但是同时 1 和 2 的micropost_id与1相同。因此，如果您希望查询仅返回微博ID，则查询应返回 1、2 还是 2，1 ？

You stated in your question that you want "distinct micropost_id" "based on order of activities.created_at". It's easy to order these activities by descending created_at (1, 3, 2), but both 1 and 2 have the same micropost_id of 1. So if you want the query to return just micropost IDs, should it return 1, 2 or 2, 1?

如果您可以回答上述问题，则需要采取逻辑操作，然后将其移至您的查询中。这么说吧，我认为这很有可能，您希望这是最近执行过的微博列表。在这种情况下，您要按照它们的最近活动的降序对它们进行排序。 Postgres可以通过多种方式为您做到这一点，但在我看来，最简单的方法是：

If you can answer the above question, you need to take your logic for doing so and move it into your query. Let's say that, and I think this is pretty likely, you want this to be a list of microposts which were most recently acted on. In that case, you want to sort the microposts in descending order of their most recent activity. Postgres can do that for you, in a number of ways, but the easiest way in my mind is this:

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的每组活动，仅按最新的排序。

Note that I've dropped the SELECT DISTINCT bit in favor of using GROUP BY, since Postgres handles them much better. The MAX(activities.created_at) bit tells Postgres to, for each group of activities with the same micropost_id, sort by only the most recent.

您可以像上面那样将以上内容翻译为Rails：

You can translate the above to Rails like so:

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')

希望这会有所帮助！如果您想了解有关查询方式的更多信息，可以使用此sqlFiddle 有效。

Hope this helps! You can play around with this sqlFiddle if you want to understand more about how the query works.

这篇关于使用postgres在导轨中使用DISTINCT ID进行订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！