PGError: ERROR: aggregates are not allowed in the WHERE clause on the AR request of the object and its has_many objects

Question

Run the following query in the has_many association. Has_many Approvals recommendations.

I am running rails 3 and PostgreSQL:

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

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

Answer 1

The error message tells you:

aggregates not allowed in the WHERE clause

count() is an aggregate function. Use the HAVING clause for this.
The request may 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 

In PostgreSQL 9.1 or later, GROUP BY primary key is sufficient tables (assuming recommendations.id is PK). In versions of Postgres prior to 9.1, you had to include all columns of the SELECT list that were not aggregated in the GROUP BY list. With recommendations.* In the SELECT list, this will be each column of the table.

I quote the PostgreSQL 9.1 release notes :

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

Sub-selection simplification

In any case, it’s easier and faster by 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 strings with JOIN a priori, then you don't need to aggregate them.

Answer 2

It looks like you have a column called count , and PostgreSQL interprets this column name as a count aggregation function. Your SQL ends as follows:

 SELECT "recommendations".* FROM "recommendations" INNER JOIN "approvals" ON "approvals"."recommendation_id" = "recommendations"."id" WHERE (approvals.count = 1 AND recommendations.user_id = 1) 

The error message specifically points to approvals.count :

 LINE 1: ...ecommendation_id" = "recommendations"."id" WHERE (approvals.... ^ 

I cannot reproduce this error in my PostgreSQL (9.0), but maybe you are using a different version. Try double quoting this column name in where :

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

If this changes something, I would recommend renaming your approvals.count column to another so that you no longer have to worry about it.