Rails/AR find where habtm does not include

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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)