Rails/Postgres：列必须出现在GROUP BY子句中

Question

我有一个作用域，将用户与帖子连接起来，只获取拥有可见帖子的用户。这在MySQL中可以工作，但PG要求更加严格，并会产生错误。

用户模型：

belongs_to :account

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

控制器：

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

错误:

(PGError: 错误: "users.account_id" 列必须出现在 GROUP BY 从句中，或在聚合函数中使用：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)

它指出了来自调用 @account.users 的 "users.account_id"（因为我显然不希望在数据库中所有用户都被查询）。

有什么想法如何解决？

- Derek J

Answer 1

问题出在GROUP BY子句上。如果使用它，就不能选择任何非聚合字段，因此SELECT "users".* [...]无法正常工作。根据Postgres文档:

通常情况下，如果对表进行了分组，未用于分组的列除了在聚合表达式中外，不能被引用。

可能会有一些麻烦，但类似下面这样做可能有效：

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')

一种替代方案是使用聚合函数（如MAX或MIN）来指定每个选定的字段，但这可能会使范围更长、更不灵活。

- M. Cypher

Answer 2

这应该用where条件而不是joins来表达，因为不需要任何来自posts的数据。在我看来，这种写法更容易阅读。

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

Ruby 2和Rails 3/4准备就绪。PG优化器将尽可能快地运行它。

- gertas