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

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 wan开发者_开发问答t all users in the DB).

Any idea how to fix?

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.

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.