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.