CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlpostgresql-8.4
Joe Morano
Joe Morano

Reputation: 1895

Postgresql Grouping Error refers to a column that was never referenced in the query

I have a model "Playlist" which has_many and belongs_to another model "User", through an intermediary model "PlaylistUser". "Playlist" has several attributes including id, name, and subject_id.

I'm trying to get this query to work:

@playlist_ids = @playlist.user_ids
@more = Playlist.select('playlists.id, playlists.name')
    .joins(:playlist_users)
    .where('playlists.id NOT IN (30, 41)')
    .where(playlist_users: {user_id: @playlist_ids})
    .group('playlists.id, playlists.name')
    .order('count(*) desc')

which, for a given Playlist @playlist, is supposed to list all the other Playlists that share a User with @playlist, ordered by how many they share.

It works on Postgres 9.4, but with Postgres 8.4 it returns this error:

PG::GroupingError: ERROR:  column "playlists.subject_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT "playlists".* FROM "playlists" INNER JOIN "playlist...
           ^
: SELECT "playlists".* FROM "playlists" INNER JOIN "playlist_users" ON "playlist_users"."playlist_id" = "playlists"."id" INNER JOIN "users" ON "users"."id" = "playlist_users"."user_id" WHERE (NOT (playlists.id = 30 OR playlists.id = 41)) AND "users"."id" IN (45, 89, 71, 117, 115, 173, 177, 180, 161, 220, 223, 199, 221, 239, 204, 205, 206, 207, 211, 261, 282, 284, 286, 251, 252, 255, 310, 311, 315, 318, 307, 362, 319, 306, 289, 316, 305, 321, 322, 330, 333, 292, 294, 304, 300, 340, 341, 342, 343, 405, 406, 410, 408, 409, 407, 413, 416, 417, 418, 425, 427, 392, 401, 403, 445, 446, 449, 450, 379, 456, 451, 454, 459, 437, 442, 444, 496, 501, 518, 548, 549, 533, 553, 1112, 1113, 1459, 455, 348, 1458, 242, 1275, 151, 1890, 336, 203, 404, 166, 453, 114, 157, 285, 448, 447, 443, 550, 2167, 2168, 287, 320, 293, 65, 2098, 2097, 2099, 387, 3, 2175, 2170, 2174, 2182, 2171, 438, 2180, 2181, 2169, 2176, 347, 2429, 2177, 2445, 2178, 2447, 58, 2480, 390, 452, 554, 555, 313, 92, 275, 335, 428, 167, 302, 2173, 1538) GROUP BY playlists.id, playlists.name  ORDER BY count(*) desc

But I'm not referencing the column subject_id anywhere in my query, or anywhere in the view, controller, or model for the page I'm on.

Why would that column have anything to do with whether my query works or not?

Assocations:

class Playlist < ActiveRecord::Base
  has_many :playlist_users
  has_many :users, :through => :playlist_users
end

class PlaylistUser < ActiveRecord::Base
  belongs_to :playlist
  belongs_to :user
end

class User < ActiveRecord::Base
  has_many :playlist_users
  has_many :playlists, :through => :playlist_users
end

Upvotes: 0

Views: 122

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Maybe you are not referencing subject_id explicitly but your result query uses: 

  SELECT "playlists".* FROM "playlists"

And because of the wildcard * you are getting all of the columns in playlist, which includes playlists.subject_id.

You will have to add that column to the group_by clause as well as any others that are grabbed by the wildcard SELECT.

Upvotes: 1

Related Questions