PGError：错误：聚集体不WHERE子句上允许的对象的AR查询及其的has_many对象 [英] PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects

问题描述

运行上的has_many协会下面的查询。建议HAS_MANY认证。

Running the following query on a has_many association. Recommendations has_many Approvals.

我运行，轨道3和PostgreSQL：

I am running, rails 3 and PostgreSQL:

Recommendation.joins(:approvals).where('approvals.count = ?
                      AND recommendations.user_id = ?', 1, current_user.id)

这是返回以下错误： https://gist.github.com/1541569

推荐答案

该错误消息告诉您：

聚集体中不允许WHERE子句

aggregates not allowed in WHERE clause

<一个href="http://www.postgresql.org/docs/current/interactive/functions-aggregate.html"><$c$c>count()是聚合函数。使用 HAVING子句了点。
查询可以是这样的：

count() is an aggregate function. Use the HAVING clause for that.
Query could look like this:

SELECT recommendations.*
FROM   recommendations
JOIN   approvals ON approvals.recommendation_id = recommendations.id
WHERE  recommendations.user_id = $current_user_id
GROUP  BY recommendations.id
HAVING count(approvals.recommendation_id) = 1

在PostgreSQL 9.1或更高版本就足够了 GROUP BY A表的主键（我presume： recommendations.id ）。对于旧版本，你将不得不拼出未聚合的 GROUP BY 列表选择列表中的所有列。所以，用的建议。* 在 SELECT 名单，这将是该表的每一个列。

With PostgreSQL 9.1 or later it is enough to GROUP BY the primary key of a table (I presume: recommendations.id). For older versions you will have to spell out all columns of the SELECT list that are not aggregated in the GROUP BY list. So, with recommendations.* in the SELECT list, that will be every single column of the table.

我引用的PostgreSQL 9.1 的版本说明：

I quote the release notes of PostgreSQL 9.1:

当主允许非GROUP BY列的查询目标列表中   关键是BY子句指定在GROUP（彼得Eisentraut）

Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)

是否是相同的：

SELECT *
FROM   recommendations
WHERE  user_id = $current_user_id
AND   (SELECT count(*) FROM approvals
       WHERE  approvals.recommendation_id = recommendations.id) = 1

这篇关于PGError：错误：聚集体不WHERE子句上允许的对象的AR查询及其的has_many对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！