CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3
Hopstream
Hopstream

Reputation: 6451

Rails exclusion query with active record -- Rails 3.1

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.

Upvotes: 0

Views: 2027

Answers (1)

tolitius
tolitius

Reputation: 22499

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

Upvotes: 1

Related Questions