CODEWITHSUNDEEP
ruby-on-railspostgresql
Nick
Nick

Reputation: 859

PG::Error in GROUP BY clause

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

The query itself is:

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

Which comes from the following view code:

[email protected](:publication).map do |p|
  =p.publication
  [email protected]("publication = ?", p.publication).sum(:twitter_count)
  [email protected]("publication = ?", p.publication).sum(:facebook_count)
  [email protected]("publication = ?", p.publication).sum(:linkedin_count)

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?

Upvotes: 3

Views: 5519

Answers (2)

Simon Perepelitsa
Simon Perepelitsa

Reputation: 20649

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

Upvotes: 5

Craig Ringer
Craig Ringer

Reputation: 325241

When using GROUP BY you cannot SELECT fields that are not either part of the GROUP BY or used in an aggregate function. This is specified by the SQL standard, though some databases choose to execute such queries anyway. Since there's no single correct way to execute such a query they tend to just pick the first row they find and return that, so results will vary unpredictably.

It looks like you're trying to say:

"For each publication get me the sum of the twitter, facebook and linkedin counts for that publication".

If so, you could write:

SELECT publication,
       sum(twitter_count) AS twitter_sum,
       sum(linkedin_count) AS linkedin_sum,
       sum(facebook_count) AS facebook_sum
FROM "articles" 
WHERE "articles"."user_id" = 1 
GROUP BY publication;

Translating that into ActiveRecord/Rails ... up to you, I don't use it. It looks like it's pretty much what you tried to write but ActiveRecord seems to be mangling it, perhaps trying to execute the sums locally.

Upvotes: 7

Related Questions