0

How would I use active record in this case:

I have two tables: Users, Admins

Admins can be users also and thus have a user_id in their table.

I'd like to Select all users that DO NOT have a user_id set in the admins table

NOTE: This is just an example case. If this was a real case then of course I wouldn't have two different tables for users and admins.

Edit: Again, I know the semantics of doing it like this is terrible db design -- but it's just an dummy case.

Hopstream
  • 6,391
  • 11
  • 50
  • 81

1 Answers1

1

You can use :conditions to include a NOT IN:

User.find(:all, :conditions => ['user_id not in (?)', @admins.map( &:id )])

OR you can code it into a User's scope ( a.k.a. named_scope prior to Rails 3.x ):

class User < ActiveRecord::Base
  scope :not_admin, lambda { |admins| { :conditions => ['user_id not in (?)', admins.select( &:id ).join( ',' )] }
end

And use it as:

User.not_admin( @admins )

OR in order not to depend on @admins variable being passed you can use joins:

class User < ActiveRecord::Base
  scope :not_admin, joins( :admin ).where( :admins => { :user_id => nil } )
end
tolitius
  • 22,149
  • 6
  • 70
  • 81
  • Is there a way I can make that a `scope` so I could put it in the model instead of controller? – Hopstream Nov 05 '11 at 22:17
  • Doesn't seem to work -- I keep getting a `wrong number of arguments (0 for 1)` error – Hopstream Nov 05 '11 at 22:32
  • My models are `user` and `admin`. Do I need to reference some of the fields in the lambda as `self.id` maybe? – Hopstream Nov 05 '11 at 22:33
  • The following seems to work, is it correct also? ... `scope :not_admin, includes(:admin).where(:admins => {:user_id => nil})` – Hopstream Nov 05 '11 at 22:48
  • the example I gave assumes that you have "admins" to pass => hence it expects a variable (0 for 1). If you need a user to always be "connected" to admins, you can use `joins` ( `includes` loads admins eagerly ). – tolitius Nov 06 '11 at 00:57