PG :: GROUP BY子句中的错误 [英] PG::Error in GROUP BY clause

问题描述

我想不出一种更好的方法来重构以下代码（请参见这个问题），尽管我知道这很丑陋。但是，它会引发Postgres错误（不适用于SQLite）：

I couldn't think of a better way to refactor the below code (see this question), though I know it's very ugly. However, it's throwing a Postgres error (not with SQLite):

ActiveRecord::StatementInvalid: 
PG::Error: ERROR:  
column "articles.id" must appear in the GROUP BY clause or be used in an aggregate function

$中使用b $ b

查询本身是：

The query itself is:

SELECT "articles".* 
FROM "articles" 
WHERE "articles"."user_id" = 1 
GROUP BY publication

来自以下视图代码：

=@user.articles.group(:publication).map do |p|
  =p.publication
  =@user.articles.where("publication = ?", p.publication).sum(:twitter_count)
  =@user.articles.where("publication = ?", p.publication).sum(:facebook_count)
  =@user.articles.where("publication = ?", p.publication).sum(:linkedin_count)

在SQLite中，输出为（例如）NYT 12 18 14 BBC 45 46 47 CNN 75 54 78

In SQLite, this gives the output (e.g.) NYT 12 18 14 BBC 45 46 47 CNN 75 54 78, which is pretty much what I need.

如何改进代码以消除此错误？

How can I improve the code to remove this error?

推荐答案

克雷格的答案很好地说明了这个问题。 Active Record默认会选择* ，但是您可以轻松覆盖它：

Craig's answer explains the issue well. Active Record will select * by default, but you can override it easily:

@user.articles.select("publication, sum(twitter_count) as twitter_count").group(:publication).each do |row|
  p row.publication # "BBC"
  p row.twitter_count # 45
end

这篇关于PG :: GROUP BY子句中的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！