PGError：ERROR：在对象及其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关联运行以下查询。建议has_many批准。

我正在运行rails 3和PostgreSQL：

  Recommendation.joins（：approvals）.where（'approvals.count =？
 AND recommendations.user_id =？'，1，current_user.id）
  

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

解决方案

错误讯息告诉您：

$

/www.postgresql.org/docs/current/interactive/functions-aggregate.htmlrel =nofollow noreferrer> count（） 是一个聚合函数。请使用 HAVING子句。

查询可能如下所示：

  SELECT r。* 
 FROM recommendations r 
 JOIN审批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）。在Postgres 9.1之前的版本中，您必须包括 SELECT 列表中未聚合在 GROUP BY 列表。在 SELECT 列表中使用建议。，这将是表的每一列。

我引用 PostgreSQL 9.1版本说明：

允许非 GROUP BY 查询目标中的在 GROUP BY 子句（Peter Eisentraut）中指定主
键时列出

使用子选择更简单

无论哪种方式，这都更简单和更快，执行相同操作：

  SELECT * 
从建议r 
 WHERE user_id = $ current_user_id 
 AND（SELECT count（*）
 FROM approvals 
 WHERE recommendation_id = r.id）= 1; 
  

避免将行乘以 JOIN ，那么您不必将它们聚合回来。

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

I am running, rails 3 and PostgreSQL:

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

This is returning the following error: https://gist.github.com/1541569

解决方案

The error message tells you:

aggregates not allowed in WHERE clause

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

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.

I quote the release notes of PostgreSQL 9.1:

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;

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

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