GroupingError: ERROR: 列必须出现在 GROUP BY 子句中或用于聚合函数中

2021-12-02 00:00:00 postgresql ruby-on-rails sqlite activerecord ruby-on-rails-4

我的控制器中有代码按最高平均评论评级对专辑进行排名(使用此解决方案中的代码 如何通过 has_many 评论关系显示评分最高的专辑):

I have code in my controller that is ranking albums by the highest average review rating (used code from this solution How to display highest rated albums through a has_many reviews relationship): 

@albums = Album.joins(:reviews).select("*, avg(reviews.rating) as average_rating").group("albums.id").order("average_rating DESC")

这段代码在我的开发环境 (sqlite3) 中运行良好，但是当我将代码推送到 heroku 和 postgresql 时，我收到了这个错误:

This code works perfectly in my development environment (sqlite3), however when I pushed the code to heroku and to postgresql I got this error: 

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

我意识到这是一个相当普遍的问题，我对 SQL 有点缺乏经验，所以我在重构代码时遇到了麻烦，因此它可以在我的开发和生产环境中工作.

I realize this is a fairly common problem, I am a bit inexperienced with SQL so I am having trouble refactoring the code so it will work in both my development and production environments.

推荐答案

不允许选择reviews.id(通过通配符*隐式选择)不添加将其添加到 GROUP BY 子句或应用诸如 avg() 之类的聚合函数.解决方案是执行以下操作之一:

You are not allowed to select reviews.id (selected implicitly through the wildcard *) without adding it to the GROUP BY clause or applying an aggregate function like avg(). The solution is to do one of the following:

  1. 从您的选择中删除通配符 *
  2. 将字段 reviews.id 添加到您的组子句
  3. 显式选择reviews.id并对其应用聚合函数(例如sum(reviews.id))
  4. 将通配符 * 替换为特定于表的通配符 albums.*
  1. Remove the wildcard * from your select
  2. Add the field reviews.id to your group clause
  3. Select reviews.id explicitly and apply an aggregate function to it (e.g. sum(reviews.id))
  4. Replace the wildcard * with the table-specific wildcard albums.*

不过，第二个和第三个选项在您的场景中没有多大意义.根据您的评论，我添加了选项四.

The second and third option do not make much sense in your scenario though. Based on your comment, I added option four.

相关文章