CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4
Random Guy
Random Guy

Reputation: 25

Retrive records which are not referenced in other table, ActiveRecord query

There are 2 tables : User and Teacher. Teacher.user_id is from User. So, how do I find in a single query, all the users who are not in teachers.

I meant something along the lines :

         User.not_in(Teacher.all)

Upvotes: 1

Views: 1562

Answers (3)

engineersmnky
engineersmnky

Reputation: 29373

The other users seem to have neglected the rails 3 tag (since removed based on the approved answer. My answer left for posterity) : Please try this 

User.where("id NOT IN (?)",Teacher.pluck(:user_id).join(","))

This will become SELECT * FROM users WHERE id NOT IN (....) (two queries one to get the user_id from teachers and another to get the user(s) not in that list) and may fail based on the size of teacher table.

Other option is an arel table: 

users = User.arel_table
User.where(users[:id].not_in(Teacher.select(:user_id).where("user_id IS NOT NULL")))

This should produce a single query similar to 

SELECT * FROM users 
WHERE id NOT IN ( SELECT user_id FROM teachers WHERE user_id IS NOT NULL)

(one query better performance) * syntax was not fully tested

Another single query option might be 

User.joins("LEFT OUTER JOIN teachers ON teachers.user_id = users.id").
     where("teachers.user_id IS NULL")

Upvotes: 1

Abhinay
Abhinay

Reputation: 1816

You can use where.not query from ActiveRecord try something like below:

User.where.not(id: Teacher.pluck(:user_id).reject {|x| x.nil?})

Note: used reject method, in case you have nil values in some records.

Upvotes: 2

user1875195
user1875195

Reputation: 988

I think you should be able to do something like this

User.where.not(id: Teacher.ids)

Upvotes: -1

Related Questions