运行上的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
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