0

I have a Rails app with Users, and each user HABTM Roles.

I want to select Users without a specific role. I have searchlogic at my disposal, and I'm lost. I've tried using a combination of conditions and joins and includes and what not, but I can't seem to nail it. This works:

User.find(:all, :conditions => ['role_id != ?', Role[:admin].id], :joins => :roles)

To find users that are not admins, but doesn't not find users with no roles (which I want to find as well).

What simple thing am I missing in my tired state?

Daniel Huckstep
  • 5,368
  • 10
  • 40
  • 56

3 Answers3

2

Use a sub-query and the NOT IN operator

User.find(:all,:conditions => ["id NOT IN (select user_id from roles_users where role_id = ?)", Role[:admin].id)
Tilendor
  • 48,165
  • 17
  • 52
  • 58
0

I can do 

User.all - User.find(:all, :conditions => ['role_id = ?', Role[:admin].id], :joins => :roles)

Which accomplishes what I want in two queries, which is probably fine for this project, but if I can get it to a single query it would be nice.

Daniel Huckstep
  • 5,368
  • 10
  • 40
  • 56
0

How about this:

User.find :all, :conditions => [ 'roles.id is ? or roles.id != ?', nil, Role[:admin].id ], :include => :roles

This works for has_many :through, seems like it should be the same for HABTM.

austinfromboston
  • 3,791
  • 25
  • 25