用postgres在rails中用DISTINCT ids排序[英] Order with DISTINCT ids in rails with postgres

本文是小编为大家收集整理的关于用postgres在rails中用DISTINCT ids排序的处理方法,想解了用postgres在rails中用DISTINCT ids排序的问题怎么解决?用postgres在rails中用DISTINCT ids排序问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有以下代码来连接两个表 microposts 和带有 micropost_id 列的活动,然后根据具有不同 micropost 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 中添加activity.created_at,我会得到重复的微博ID,因为有不同的activity.created_at 列.我做了很多搜索才能到达这里.但是问题总是存在,因为这个 postgres 条件避免了随机选择.

我想根据活动的顺序进行选择.created_at 具有不同的 micropost _id.

请帮忙..

推荐答案

首先,我们需要快速了解 SELECT DISTINCT 实际在做什么.它看起来只是一个不错的关键字,可确保您只获取不同的值,这不应该改变任何东西,对吧?除了你发现,在幕后,SELECT DISTINCT 实际上更像是一个 GROUP BY.如果您想选择某物的不同值,则只能按您选择的相同值对该结果集进行排序——否则,Postgres 不知道该怎么做.

要解释歧义的来源,请考虑一下您的 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""基于 activity.created_at 的顺序".很容易通过对 created_at (1, 3, 2) 进行降序来对这些活动进行排序,但是 1 和 2 的 micropost_id 都是 1.因此,如果您希望查询只返回 micropost 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有效.

本文地址:https://www.itbaoku.cn/post/1764205.html