PG::Error: SELECT DISTINCT, ORDER BY 表达式必须出现在选择列表中 [英] PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list

问题描述

ActionView::Template::Error (PG::Error: ERROR: for SELECT DISTINCT, ORDER BY 表达式必须出现在选择列表中

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

我正在创建一个活动网站，我正在尝试按活动的开始时间对呈现的 rsvps 进行排序.有很多 RSVPS，所以我将它们以不同的方式分组，但是在过去的几天里，我在对结果进行排序时遇到了很多困难，而没有在 PG 上弹出这个错误.我已经查看了有关该主题的一些先前问题，但仍然很迷茫.我怎样才能让它工作?非常感谢！

I'm creating an events website and I'm trying to sort the rendered rsvps by the start time of the event. There are a lot of RSVPS so I'm grouping them with distinct, but I've been having a lot of difficulty over the last few days on sorting the results without this error popping up on PG. I've looked at some of the previous questions on the topic and am still pretty lost. How can I get this to work? Thank you so much!

@rsvps = Rsvp.where(:voter_id => current_user.following.collect {|f| f["id"]}, :status => 'going').where("start_time > ? AND start_time < ?", Time.now, Time.now + 1.month).order("count_all desc").count(:group => :event_id).collect { |f| f[0] }

<%= render :partial => 'rsvps/rsvp', :collection => Rsvp.where(:event_id => @rsvps).select("DISTINCT(event_id)").order('start_time asc') %>

推荐答案

ORDER BY 子句只能在应用 DISTINCT 之后 应用.由于 DISTINCT 操作只考虑 SELECT 语句中的字段，因此这些是唯一可以在 ORDER BY 中使用的字段.

The ORDER BY clause can only be applied after the DISTINCT has been applied. Since only the fields in the SELECT statement are taken into consideration for the DISTINCT operations, those are the only fields may be used in the ORDER BY.

从逻辑上讲，如果您只想要一个不同的 event_id 值列表，那么它们出现的顺序应该无关紧要.如果订单确实重要，那么您应该将 start_time 添加到 SELECT 列表中，以便为订单提供上下文.

Logically, if you just want a distinct list of event_id values, what order they occur in should be irrelevant. If order does matter, then you should add the start_time to the SELECT list so that there is context for the order.

另外，这两个 SELECT 子句并不等价，所以要小心:

Also, these two SELECT clauses are NOT equivalent, so be careful:

SELECT DISTINCT(event_id, start_time) FROM ...

SELECT DISTINCT event_id, start_time FROM ...

第二种是你想要的形式.第一个将返回一系列记录，其数据表示为 ROW 构造(内部带有元组的单个列).第二个将返回数据输出的正常列.它仅在 ROW 构造减少的单列情况下按预期工作，因为它只是单列.

The second is the form you want. The first will return a series of records with the data represented as a ROW construct (a single column with a tuple inside). The second will return normal columns of data output. It only works as expected in the single-column case where the ROW construct is reduced down since it is only a single column.

这篇关于PG::Error: SELECT DISTINCT, ORDER BY 表达式必须出现在选择列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！