Rails named_scope error in postgreSQL, works in SQLite

Question

I have the following models to associate users with roles:

class User < ActiveRecord::Base
  has_many :user_role_assignments, :dependent => :destroy
  has_many :user_roles, :through => :user_role_assignments
end

class UserRole < ActiveRecord::Base
  has_many :user_role_assignments
  has_many :users, :through => :user_role_assignments
end

class UserRoleAssignment < ActiveRecord::Base
  belongs_to :user
  belongs_to :user_role

  named_scope :has_admin_role, 
    :joins => :user_role, 
    :conditions => "user_roles.name = 'admin'",
    :group => "user_role_assignments.user_id" 

  named_scope :has_non_admin_role, 
    :joins => :user_role, 
    :conditions => "user_roles.name <> 'admin'",
    :group => "user_role_assignments.user_id" 
end

The named scopes in UserRoleAssignment work in SQLite, but not in postgreSQL, which issues this error:

ActiveRecord::StatementInvalid: PGError: ERROR:  column "user_role_assignments.id" must appear in the GROUP BY clause or be used in an aggregate function
: SELECT "user_role_assignments".* FROM "user_role_assignments"   INNER JOIN "user_roles" ON "user_roles".id = "user_role_assignments".user_role_id  WHERE (user_roles.name = 'admin')  GROUP BY user_role_assignments.user_id

I have tried many different variations of the named scope (adding a :having clause, adding additional groupings) but each time I add something, a new error arises in postgreSQL so I have reverted back to my original, simple named_scopes. The point of the first scope is to retrieve all users that either have a role with name "admin" and the second is to retrieve users that have some role other than "admin". I use this with searchlogic from other models and it works as expected in SQLite, but not postgreSQL (e.g. User.user_role_assignments_has_admin_role).

How can I modify these named_scopes to work with postgreSQL?

Answer 1

Postgres is much more picky about GROUP BY clauses than sqlite3.

You'll have to add all of the user_role_assignments column names to your GROUP BY clause:

class UserRoleAssignment < ActiveRecord::Base
  belongs_to :user
  belongs_to :user_role

  named_scope :has_admin_role, 
    :joins => :user_role, 
    :conditions => "user_roles.name = 'admin'",
    :group => "user_role_assignments.id, user_role_assignments.user_id, user_role_assignments.user_role_id" 

  named_scope :has_non_admin_role, 
    :joins => :user_role, 
    :conditions => "user_roles.name <> 'admin'",
    :group => "user_role_assignments.id, user_role_assignments.user_id, user_role_assignments.user_role_id" 
end

If that doesn't work, you can try this next (but I'm not sure if it will work).

class UserRoleAssignment < ActiveRecord::Base
  belongs_to :user
  belongs_to :user_role

  named_scope :has_admin_role, 
    :joins => :user_role, 
    :conditions => "user_roles.name = 'admin'",
    :group => "user_role_assignments.*" 

  named_scope :has_non_admin_role, 
    :joins => :user_role, 
    :conditions => "user_roles.name <> 'admin'",
    :group => "user_role_assignments.*" 
end