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?
6 Answers
Answers 1
To get all properties that have no user, try this:
Property.includes(:users).where(users: { property_id: nil }) Answers 2
You can try this query:
Property.where.not(:id=>User.where.not(:property_id=>nil).pluck(:property_id)) or
Property.where.not(:id=>User.where.not(:property_id=>nil).pluck("DISTINCT property_id")) Answers 3
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
Answers 4
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.
Answers 5
use this code:
@users= User.includes(:properties).where(properties: { property_id: nil }) Answers 6
Also You can write scope based on this query just for easy use.
class Property < ActiveRecord::Base has_many :users scope :incomplete, -> { joins(:users).where("property.user_id is null") } end Then, You can call this scope like this: Property.incomplete
0 comments:
Post a Comment