CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-3.2ruby-on-rails-4rails-activerecord
at.
at.

Reputation: 52500

Retrieve objects that don't have a relationship in ActiveRecord/Rails

I need to get all users who are not staff from my database:

class User < ActiveRecord::Base
  has_one :staff
  def is_staff?
    staff != nil
  end
end
class Staff < ActiveRecord::Base
  attr_accessible :user_id
  belongs_to :user
end

So I could do this in code with:

User.all.select {|user| not user.is_staff? }

That seems to work, but I'd like to have this logic in the database and also I get an error in production with Heroku for some reason when trying to sort this way (dev with sqlite does not give me this error):

NAME_SORT = ->(a, b) {
  if a.first_name == b.first_name
    a.last_name <=> b.last_name
  else
    a.first_name <=> b.first_name
  end
}
User.all.select {|user| not user.is_staff? }.sort &NAME_SORT

The error I get for some reason is:

ArgumentError: comparison of User with User failed

Anyway, if I wanted to get all the users who are staff, that seems simple:

User.joins(:staff)

Is there an easy way to get users who are not staff? Maybe something like this?

User.not_joins(:staff)

And really I need the User to not be a Staff or Admin:

User.not_joins(:staff, :admin)

Upvotes: 3

Views: 2333

Answers (2)

MrYoshiji
MrYoshiji

Reputation: 54882

To get all Users having no relation with Staff, you can use the following:

User.includes(:staff).where(staff: { id: nil })

It would work the same way for the the following query "Get all Staff having no user associated":

Staff.includes(:user).where(users: { id: nil })
                   ^            ^

Something to know: In the includes (and preload and joins) methods you have to use the relation's name as defined in the model (belongs_to & has_one: singularize, has_many: pluralize), but in the where clause you have to use the pluralized version of the relation (actually, the table's name).

Upvotes: 7

Jonathan Vukovich-Tribouharet
Jonathan Vukovich-Tribouharet

Reputation: 1988

You have 3 solutions:

  • do something not optimized and dirty like:

    User.where('id NOT IN(?)', Staff.select(:user_id).all.map(&:user_id).uniq).all

or like MrYoshiji said

    User.includes(:staff).where(:staff => {:id => nil})

  • add a field to your table user, for know if your user have or not a staff, so you have to set this field to true after your create a staff for a user but you could get all users easily with

    User.where(:have_staff => false).all

  • or you invert your relation and say Staff has_one User and User belongs_to Staff and for get all users you do

    User.where(:staff_id => nil).all

Upvotes: 0

Related Questions