2

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

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
TJ Sherrill
  • 2,465
  • 7
  • 50
  • 88
  • Can you clarify a few things? Do you actually have a column of the name `approvals.count`? And can you describe in free words what the query is supposed to do? Did you copy & paste the error message verbatim? – Erwin Brandstetter Dec 31 '11 at 02:44
  • Could you try doing `Recommendation.joins(:approvals).where('approvals.count = ? AND recommendations.user_id = ?', 1, 1)` in the Rails console to try to isolate the problem? – mu is too short Dec 31 '11 at 03:02
  • MU: that query error'd the same way. I do not have a column for count, but I thought Ruby had a .count function for arrays. Is my problem that I don't have an array? – TJ Sherrill Jan 02 '12 at 05:15
  • @TJSherrill: It looks like my assessment was corrrect to begin with. Move your `count` to the HAVING clause. – Erwin Brandstetter Jan 03 '12 at 02:06

2 Answers2

5

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Can you direct me on how to modify the query based on this info, I am not solid with Postgres yet. – TJ Sherrill Dec 30 '11 at 23:48
  • But there is no `count(*)` in the SQL (or `count()` on anything for that matter), just the rather suspicious looking `approvals.count` column. – mu is too short Dec 31 '11 at 00:44
  • @muistooshort: Well, you are right. A mixup of the name was also my theory in the first draft. But then, where does the error message come from? The table-qualified name `approvals.count` would not be mistaken for an aggregate function. I start to think we are not presented with the full story ... – Erwin Brandstetter Dec 31 '11 at 02:42
  • We have the query and the error message (apparently) I can't think of any other possibility. The gist on Github does look like a Rails error message. Do you have an 8.3 installation kicking around? I only have a 9.0 accessible right now. I share your suspicion that something might be missing here. – mu is too short Dec 31 '11 at 03:01
  • @muistooshort: Sorry, only pg 8.4+. (How do you figure 8.3?) The whole story doesn't pan out. `approvals.count` wouldn't be mistaken for the built in aggregate function in any version of Postgres. Also, no brackets, so even a user-defined aggregate function `approvals.count(..)` would not work that way. There is an unmatched bracket at the end of line 17 in the linked error msg, which made me think: "copy/paste artefacts?" – Erwin Brandstetter Dec 31 '11 at 03:18
  • Sorry, new years. I am a newb on this so I am sorry if I have not provided enough info. My understanding was that ruby provides a count function on arrays. I am simply trying to check where recommendation.approvals.count = 1. I would be more than happy to give more info... just not sure what – TJ Sherrill Jan 02 '12 at 05:10
1

Looks like you have a column named count and PostgreSQL is interpreting that column name as the count aggregate function. Your SQL ends up like this:

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 at the approvals.count:

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

I can't reproduce that error in my PostgreSQL (9.0) but maybe you're using a different version. Try double quoting that column name in your where:

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

If that sorts things out then I'd recommend renaming your approvals.count column to something else so that you don't have to worry about it anymore.

mu is too short
  • 426,620
  • 70
  • 833
  • 800