find all that are nil in the association in ruby on rails

Question

Here I've got a 1-to-many relationship between Products and Users:

class Property < ActiveRecord::Base
  has_many :users
end

class User < ActiveRecord::Base
  belongs_to :property
end

How could I get all the properties which do not belong to any user?

Answer 1

To get all properties that have no user, try this:

Property.includes(:users).where(users: { property_id: nil })

Answer 2

One more approach would be to write some SQL:

Property.joins("LEFT OUTER JOIN users ON users.property_id = properties.id").
where('users.id IS NULL').
uniq

The code above is being translated to the following pure SQL query to the database:

SELECT DISTINCT properties.* FROM properties 
LEFT OUTER JOIN users on users.property_id = properties.id 
WHERE users.id IS NULL;

LEFT JOIN keyword returns all rows from the left table (properties), with the matching rows in the right table (users). The result is NULL in the right side when there is no match. Afterwards WHERE keyword filters results by a condition that we're intrested in those rows which have NULL on the right side only.

Reference: SQL LEFT JOIN Keyword

Answer 3

You can do it like this too:

Property.where('id NOT IN (SELECT DISTINCT(property_id) FROM users)')

Another option would be:

Property.where("(select count(*) from users where property_id = properties.id) = 0")

You can always check which is more efficient according to you application by checking the time take to execute the queries and choose an option accordingly.