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

时间:2021-01-11 22:43:44

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):

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

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

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

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

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

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.

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

3 个解决方案

#1


23  

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:

您不能选择reviews.id(通过通配符*隐式选择),而不将其添加到GROUP BY子句或应用像avg()这样的聚合函数。解决方案是执行以下操作之一:

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

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

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

#2


0  

Just would like to share this code on ruby using active record (sinatra)

只是想使用活动记录(sinatra)在ruby上分享这段代码

I had to add "group by" to an "order by" function, so line of code ...

我不得不将“group by”添加到“order by”函数中,因此代码行...

from:

从:

@models = Port.all.order('number asc')

to:

至:

@models = Port.select(:id, :device_id, :number, :value, :sensor, :UOM).all.order('number asc').group(:id,:sensor,:UOM)

and it worked perfect, just remember the ID field in this case "Port.id" must be added to the group clause otherwise will raise this error, and as @slash mentioned you can not achieve this with special functions (select implicitly through the wildcard * or in my case using "all")

并且它工作得很完美,只需记住ID字段在这种情况下“Port.id”必须添加到group子句中否则会引发此错误,并且正如@slash所提到的那样你无法通过特殊函数实现这一点(通过通配符隐式选择) *或在我的情况下使用“全部”)

#3


0  

The only really acceptable solution I found to this kind of problem was with this code:

我发现这种问题的唯一真正可接受的解决方案是使用以下代码:

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

I hope it helps someone.

我希望它对某人有帮助。

#1


23  

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:

您不能选择reviews.id(通过通配符*隐式选择),而不将其添加到GROUP BY子句或应用像avg()这样的聚合函数。解决方案是执行以下操作之一:

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

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

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

#2


0  

Just would like to share this code on ruby using active record (sinatra)

只是想使用活动记录(sinatra)在ruby上分享这段代码

I had to add "group by" to an "order by" function, so line of code ...

我不得不将“group by”添加到“order by”函数中,因此代码行...

from:

从:

@models = Port.all.order('number asc')

to:

至:

@models = Port.select(:id, :device_id, :number, :value, :sensor, :UOM).all.order('number asc').group(:id,:sensor,:UOM)

and it worked perfect, just remember the ID field in this case "Port.id" must be added to the group clause otherwise will raise this error, and as @slash mentioned you can not achieve this with special functions (select implicitly through the wildcard * or in my case using "all")

并且它工作得很完美,只需记住ID字段在这种情况下“Port.id”必须添加到group子句中否则会引发此错误,并且正如@slash所提到的那样你无法通过特殊函数实现这一点(通过通配符隐式选择) *或在我的情况下使用“全部”)

#3


0  

The only really acceptable solution I found to this kind of problem was with this code:

我发现这种问题的唯一真正可接受的解决方案是使用以下代码:

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

I hope it helps someone.

我希望它对某人有帮助。