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

问题描述

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

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.

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

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 %>

<%结束%>

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

@comments 在控制器中定义为:

heroku logs give me this as the error message:

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

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

我已经尝试了其他 SO 问题的解决方案，例如 20942477.其中说我应该将字段 comments.id 添加到我的组子句中:

<% @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.

这消除了 heroku 上的错误，但违背了 group 命令的目的 - 它不再只显示独特的患者，而是列出所有患者.

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

我还尝试了 1780893.其中说我应该更改 ORDER BY:

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

Which gives this error locally:

在本地出现此错误:

SQLite3::SQLException: 没有这样的函数: substring: SELECT"comments".* FROM "comments" WHERE "comments"."clinician_id" = ?命令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.

我意识到这是一个常见问题，我对 SQL 缺乏经验，因此无法获取代码，因此它可以在我的开发和生产环境中运行.我在 SO 上读到的答案没有使用 Ruby 来获取 SQL 并超过我的经验水平.

解决方案

推荐答案

你不能在 Postgres 中将 SELECT * 和 GROUP BY some_column 结合起来(除非 some_columncode> 是 PK)，因为这是一个矛盾.所有非聚合列(在聚合函数之外的 SELECT、HAVING 或 ORDER BY 子句中使用)必须在 GROUPBY 列表 - 其中主键列可以替换表的所有列.否则未定义哪个值从聚合集合中选择.

Per documentation:

根据文档:<块引用>

当存在 GROUP BY 或任何聚合函数时，它对引用未分组的 SELECT 列表表达式无效列，聚合函数内或未分组的列除外在功能上依赖于分组列，因为会有否则对于未分组的返回值可能不止一个柱子.如果分组列(或其子集)是包含表的主键未分组的列.

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.

_{已知某些其他 RDBMS 在这里玩弄肮脏的把戏，并允许这样做并选择任意值......}

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

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;

但这不会与 SQLite 一起使用 - 它不应该在循环中开始:

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

• 适用于 SQLite3 的通用 Ruby 解决方案 "喜欢"或 PostgreSQL ILIKE"?

NULLS LAST 仅在 created_at 可以为 NULL 时才相关:

NULLS LAST is only relevant if created_at can be NULL:

• PostgreSQL 按日期时间 asc 排序，空值优先?

DISTINCT ON 的详细信息:

• 选择每个 GROUP BY 组中的第一行?

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