Rails/Postgres: column must appear in the GROUP BY clause

Question

I have a scope that joins users to posts, to get only users that have visible posts. This works in MySQL, but PG is a lot more strict, and throws an error.

User Model:

belongs_to :account

scope :have_posts, joins(:posts).where('posts.visible => true').group('users.id')

Controller:

@account.users.have_posts.each do |user|
  # do stuff
end

Error:

(PGError: ERROR: column "users.account_id" must appear in the GROUP BY clause or be used in an aggregate function: SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id" WHERE ("users".account_id = 1) AND (recommendations.approved = true) GROUP BY users.id)

It's complaining about "users.account_id" which is from calling @account.users (as I obviously don't want all users in the DB).

Any idea how to fix?

M. Cypher · Accepted Answer · 2011-08-03 19:39:51Z

6

The problem is the GROUP BY clause. If you use this, you cannot select any non-aggregated fields, so SELECT "users".* [...] doesn't work. From the Postgres docs:

In general, if a table is grouped, columns that are not used in the grouping cannot be referenced except in aggregate expressions.

Something like this may work, although messy:

scope :have_posts, 
  joins('inner join (select user_id from posts where visible = true group by user_id) users_with_posts on users_with_posts.user_id=users.id')

One alternative would be to specify every selected field using aggregate functions like MAX or MIN, but this will probably make the scope even longer and less versatile.

edited Aug 3, 2011 at 19:39

answered Aug 3, 2011 at 19:22

M. Cypher

7,0762 gold badges38 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gertas · Accepted Answer · 2014-02-15 20:31:41Z

0

This should be expressed as where condition not joins, because there is no need for any data from posts. IMHO much easier to read:

scope :have_posts, -> {
  where(
    'EXISTS (SELECT 1 FROM posts p WHERE p.id = users.id AND visible = true)'
  )
}

Ruby 2 and Rails 3/4 ready. PG optimizer will take care to run it as fast as possible.

answered Feb 15, 2014 at 20:31

gertas

17.2k1 gold badge79 silver badges58 bronze badges

Collectives™ on Stack Overflow

Rails/Postgres: column must appear in the GROUP BY clause

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related