PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects

by

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.

More Related Contents:

  1. Ignoring time zones altogether in Rails and PostgreSQL
  2. Can’t find the ‘libpq-fe.h header when trying to install pg gem
  3. Can’t find the PostgreSQL client library (libpq)
  4. Eager load polymorphic
  5. Repairing Postgresql after upgrading to OSX 10.7 Lion
  6. PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list
  7. Rails order by results count of has_many association
  8. How to chain scope queries with OR instead of AND?
  9. When to use a “has_many :through” relation in Rails?
  10. Rails 3: Get Random Record
  11. Setting session timeout in Rails 3
  12. Alias for column names in Rails
  13. How to handle Ruby on Rails error: “Please install the postgresql adapter: `gem install activerecord-postgresql-adapter'”
  14. ActiveRecord Find By Year, Day or Month on a Date field
  15. Rails 3 devise, current_user is not accessible in a Model ?
  16. Rails 3, has_one / has_many with lambda condition
  17. Rails 3 – select with Include?
  18. GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
  19. belongs_to through associations
  20. Check if record exists from controller in Rails
  21. Installing Postgres on windows for use with Ruby-on-Rails
  22. Validate uniqueness of multiple columns
  23. Rails auto-assigning id that already exists
  24. Rails – Validate Presence Of Association?
  25. How to implement bulk insert in Rails 3
  26. Store the day of the week and time?
  27. Rails: FATAL – Peer authentication failed for user (PG::Error)
  28. Adding foreign key to a rails model
  29. Specifying column name in a “references” migration
  30. Is the server running on host “localhost” (::1) and accepting TCP/IP connections on port 5432?

Leave a Comment