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

Question

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

score 1 · Accepted Answer · edited Nov 12 '15 at 21:25

1

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.

edited Nov 12 '15 at 21:25

James B. Byrne

1,048
12
27

answered Nov 12 '15 at 20:53

Juan Carlos Oropeza

47,252
12
78
118

He is constructing the query with active record - not raw SQL. Not very helpful. – max Nov 12 '15 at 20:55
So what, I have to add every single Playlist attribute to the group_by?? – Joe Morano Nov 12 '15 at 21:00
See: https://stackoverflow.com/questions/20942477/groupingerror-error-column-must-appear-in-the-group-by-clause-or-be-used-in-an?rq=1 – James B. Byrne Nov 12 '15 at 21:21
I know doesnt sound good, but the query is sending the active record looks like have some bug, because shouldnt ask for `"playlists".*` so my suggestion is how workaround the bug. Not regarding solving the bug – Juan Carlos Oropeza Nov 12 '15 at 21:28
Also, you do not say what version of ActiveRecord you are using. The syntax shown should not be generating the wildcard select according to the current AR Guide: http://guides.rubyonrails.org/active_record_querying.html 4 Selecting Specific Fields _By default, Model.find selects all the fields from the result set using select *. To select only a subset of fields from the result set, you can specify the subset via the select method._ – James B. Byrne Nov 12 '15 at 21:31
You should check your db log on 8.4 and 9.3 to see if they are sending `"playlists".* ` or `playlists.id, playlists.name`. If both are getting `playlists.*` mean the problem is with the active record not the db. – Juan Carlos Oropeza Nov 12 '15 at 21:31

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

1 Answers1