CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4activerecord
wintermeyer
wintermeyer

Reputation: 8318

Solving a PG::GroupingError: ERROR

The following code gets all the residences which have all the amenities which are listed in id_list. It works with out a problem with SQLite but raises an error with PostgreSQL:

id_list = [48, 49]
Residence.joins(:listed_amenities).
          where(listed_amenities: {amenity_id: id_list}).
          references(:listed_amenities).
          group(:residence_id).
          having("count(*) = ?", id_list.size)

The error on the PostgreSQL version:

Rails error message

What do I have to change to make it work with PostgreSQL?

Upvotes: 1

Views: 103

Answers (2)

Beaker
Beaker

Reputation: 403

The query the Ruby (?) code is expanded to is selecting all fields from the residences table:

SELECT "residences".*
  FROM "residences"
 INNER JOIN "listed_amenities"
    ON "listed_amentities"."residence_id" = "residences"."id"
 WHERE "listed_amenities"."amenity_id" IN (48,49)
 GROUP BY "residence_id"
HAVING count(*) = 2
 ORDER BY "residences"."id" ASC
 LIMIT 1;

From the Postgres manual, When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column.

You'll need to either group by all fields that aggregate functions aren't applied to, or do this differently. From the query, it looks like you only need to scan the amentities table to get the residence ID you're looking for:

SELECT "residence_id"
  FROM "listed_amenities"
 WHERE "listed_amenities"."amenity_id" IN (48,49)
 GROUP BY "residence_id"
HAVING count(*) = 2
 ORDER BY "residences"."id" ASC
 LIMIT 1

And then fetch your residence data with that ID. Or, in one query:

SELECT "residences".*
  FROM "residences"
 WHERE "id" IN (SELECT "residence_id"
                  FROM "listed_amenities"
                 WHERE "listed_amenities"."amenity_id" IN (48,49)
                 GROUP BY "residence_id"
                HAVING count(*) = 2
                 ORDER BY "residences"."id" ASC
                 LIMIT 1
               );

Upvotes: 1

ahmacleod
ahmacleod

Reputation: 4310

A few things:

  1. references should only be used with includes; it tells ActiveRecord to perform a join, so it's redundant when using an explicit joins.

  2. You need to fully qualify the argument to group, i.e. group('residences.id').

For example,

id_list = [48, 49]
Residence.joins(:listed_amenities).
          where(listed_amenities: { amenity_id: id_list }).
          group('residences.id').
          having('COUNT(*) = ?", id_list.size)

Upvotes: 1

Related Questions