ruby on rails - PG::Error in GROUP BY clause

Question

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:

=@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)

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?

Answer 1

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.