GroupingError：错误：列“ "必须出现在GROUP BY子句中或用于聚合函数中 [英] GroupingError: ERROR: column " " must appear in the GROUP BY clause or be used in an aggregate function

问题描述

我正在尝试创建一个留下评论的独特患者名单。这些代码字很好，直到我上传到heroku，它在postgresql中不起作用。

这是我创建列表的Ruby .erb代码：

 <％@ comments.group（：patient_id）.each_with_index do |评论，索引| ％GT; 
< div class =profile-post color-one> 
< span class =profile-post-numb><％= index + 1％>< / span> 
< div class =profile-post-in> 
< h3 class =heading-xs>< a><％= link_to＃{comment.patient.first_name}＃{comment.patient.last_name}，comment_path（comment）％> ;< / A>< / H3> 
< p>来自<％= time_ago_in_words（comment.created_at）的最新评论>前< i class =pull-right><％= link_to编辑，edit_comment_path（评论）％> <％= link_to删除，comment_path（评论），方法:: delete，data：{confirm：'您确定要删除'}％>< / i>< / p> 
< / div> 
< / div> 
<％end％> 
  

@comments在控制器中定义为：

  def index 
 @comments = current_clinician.comments.order（created_at desc）
 end 
  

heroku日志给我这个错误信息：

  PG :: GroupingError：错误：列comments.id必须出现在GROUP BY子句中或用于聚合函数
 
 LINE 1：SELECTcomments。 * FROMcommentsWHERE comments。clini ... 
 ^ 
 SELECTcomments。* * FROMcommentsWHEREcomments。clinician_id= $ 1 GROUP BY patient_id ORDER BY created_at desc 
  

我尝试了其他SO问题的解决方案，例如作为 20942477 。其中说我应该将字段comments.id添加到我的group子句中：

 <％@ comments.group（：patient_id ，：comments.id）。each_with_index do | comment，index | ％GT; 
  

这样可以摆脱heroku上的错误，但会破坏组命令的作用 - 它不再只是显示独特的患者，而是列出所有这些患者。

我也尝试了 1780893 。这说我应该改变ORDER BY：

  @comments = current_clinician.comments.order（substring（created_at，1.8） desc）
  

在本地给出这个错误：

SQLite3 :: SQLException：no such function：substring：SELECT
comments。* FROMcommentsWHEREcomments。clinician_id=？ ORDER
BY substring（created_at，1.8）desc

我知道这是一个常见问题，我对SQL不熟悉我无法获取代码，因此它可以在我的开发和生产环境中使用。我读过的所有答案都没有使用Ruby来获取SQL，并且超过了我的经验级别。

解决方案

您无法合并Postgres中除 some_column 以外的 SELECT * 是PK），因为这是一个矛盾。所有非汇总列（用于 SELECT ， HAVING 或 ORDER BY 子句）必须位于 GROUP BY 列表中 - 其中主键列可以替换表中的所有列。否则，它是未定义哪个值要从汇总集中选择。

per documentation：
$ b

<当存在 GROUP BY 或存在任何集合函数时，
对于 SELECT 列出表达式来引用未分组的
列，除非集合函数中或未分组的列
在功能上依赖于分组列，因为会有
，否则会有多个可能的值返回对于未分组的
列。如果分组列（或其
子集）是包含
未分组列的表的主键，则存在函数依赖项。

某个其他的RDBMS被称为在这里扮演肮脏的诡计，并允许这个和任意值...



您似乎想要一个评论过的独特患者列表，每个评论都有最新评论。 Postgres中最简单的方法是使用 DISTINCT ON ：

  SELECT DISTINCT ON （patient_id）* 
 FROM comments 
 WHERE clinician_id = $ 1 
 ORDER BY patient_id，created_at DESC NULLS LAST; 
  

但是这不会在SQLite中运行 - 它不应该以循环开头：

• SQLite3的通用Ruby解决方案" LIKE"或PostgreSQLILIKE？

NULLS LAST 是只有在 created_at 可以为NULL时才有意义：

• PostgreSQL按日期时间顺序排序，首先为空？

$ b

有关 DISTINCT ON 的详细信息：






• 选择每个GROUP BY中的第一行group？





I am trying to create a list of unique patients who have left comments. The code words fine until I upload to heroku where it doesnt work in postgresql.

This is my Ruby .erb code to create the list:

<% @comments.group(:patient_id).each_with_index do |comment, index| %>
    <div class="profile-post color-one">
       <span class="profile-post-numb"><%= index+1 %></span>
        <div class="profile-post-in">
            <h3 class="heading-xs"><a><%= link_to "#{comment.patient.first_name} #{comment.patient.last_name}", comment_path(comment) %></a></h3>
            <p>Lastest comment from <%= time_ago_in_words(comment.created_at) %> ago<i class="pull-right"><%= link_to "Edit", edit_comment_path(comment) %> <%= link_to "Delete", comment_path(comment), method: :delete, data: {confirm: 'Are you sure you want to delete'} %></i></p>
        </div>
    </div>
<% end %>

@comments is defined in the controller as:

def index
  @comments = current_clinician.comments.order("created_at desc")
end

heroku logs give me this as the error message:

PG::GroupingError: ERROR:  column "comments.id" must appear in the GROUP BY clause or be used in an aggregate function

LINE 1: SELECT "comments".* FROM "comments"  WHERE  comments"."clini...
               ^
SELECT "comments".* *FROM "comments"  WHERE "comments"."clinician_id" = $1 GROUP BY patient_id  ORDER BY created_at desc

I have tried the solutions from a other SO questions, such as 20942477. Which said that I should add the field comments.id to my group clause:

<% @comments.group(:patient_id, :"comments.id").each_with_index do |comment, index| %>

This gets rid of the error on heroku but defeats the purpose of the group command - it no longer only shows unique patients but instead lists all of them.

I also tried the solutions from 1780893. Which said that I should change the ORDER BY:

@comments = current_clinician.comments.order("substring(created_at,1.8) desc")

Which gives this error locally:

SQLite3::SQLException: no such function: substring: SELECT "comments".* FROM "comments" WHERE "comments"."clinician_id" = ? ORDER BY substring(created_at,1.8) desc

I realize this is a common problem, I am inexperienced with SQL so I am having trouble getting the code so it will work in both my development and production environments. The answers I have read on SO aren't using Ruby to get SQL and go over my experience level.

解决方案

You cannot combine SELECT * with GROUP BY some_column in Postgres (unless some_column is the PK), because that's a contradiction. All non-aggregated columns (used in the SELECT, HAVING or ORDER BY clause outside an aggregate function) must be in the GROUP BY list - where the primary key column can replace all columns of a table. Else it is undefined which value to pick from the aggregated set.

Per documentation:

When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.

_{A certain other RDBMS is known to play dirty tricks here and allow this and pick arbitrary values...}

You seem to want a list of unique patients that have commented, with the latest comment each. The simplest way in Postgres is with DISTINCT ON:

SELECT DISTINCT ON (patient_id) *
FROM   comments
WHERE  clinician_id = $1
ORDER  BY patient_id, created_at DESC NULLS LAST;

But this won't fly with SQLite - which should not be in the loop to begin with:

• Generic Ruby solution for SQLite3 "LIKE" or PostgreSQL "ILIKE"?

NULLS LAST is only relevant if created_at can be NULL:

• PostgreSQL sort by datetime asc, null first?

Details for DISTINCT ON:

• Select first row in each GROUP BY group?

这篇关于GroupingError：错误：列“ &QUOT;必须出现在GROUP BY子句中或用于聚合函数中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！