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

问题描述

对 has_many 关联运行以下查询.推荐有_many 批准.

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

我正在运行，rails 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

count() 是一个聚合函数.为此，请使用 HAVING 子句.
查询可能如下所示:

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

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

使用 PostgreSQL 9.1 或更高版本，GROUP BY 表的主键就足够了(假设 recommendations.id 是 PK).在 9.1 之前的 Postgres 版本中，您必须包含 SELECT 列表的 所有 列，这些列未聚合在 GROUP BY 列表中.在 SELECT 列表中使用 recommendations.*，这将是表格的每一列.

With PostgreSQL 9.1 or later it is enough to GROUP BY the primary key of a table (presuming recommendations.id is the PK). In Postgres versions before 9.1 you had to include all columns of the SELECT list that are not aggregated in the GROUP BY list. With recommendations.* in the SELECT list, that would be every single column of the table.

我引用了 PostgreSQL 9.1 的发行说明:

允许非GROUPBY 列在查询目标列表中键在 GROUP BY 子句中指定 (Peter Eisentraut)

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

更简单的子选择

无论哪种方式，这样做都更简单、更快捷:

Simpler with a sub-select

Either way, this is simpler and faster, doing the same:

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

避免先验地将行与 JOIN 相乘，这样您就不必将它们聚合回来.

Avoid multiplying rows with a JOIN a priori, then you don't have to aggregate them back.

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