How to select from a model all elements that have no relation to another model-CodePudding

Sorry for the vague title. I have 3 tables: User, Place and PlaceOwner. I want to write a scope in the "PlaceOwner" model to get all the "Places" that don't have an owner.

class User < ApplicationRecord
 has_one :place_owner
end

class PlaceOwner < ApplicationRecord
  belongs_to :user
  belongs_to :place

  #scope :places_without_owner, -> {}

class Place < ApplicationRecord
  has_many :place_owners
end

I tried checking for association in the rails console for each element and it worked. But I don't know how to implement this at scope. I've seen people solve similar problems by writing SQL but I don't have the knowledge to write it that way. I understand that I need to check all IDs from Place to see if they are in the PlaceOwner table or not. But I can't implement it.

For example:

There are 3 records in the "Place" table: House13, House14, House15.

There are 2 records in the "PlaceOwner" table: House13 - User1, House 14 - User2

I want to get House15

I hope I explained clearly what I'm trying to do. Please help or at least tell me where to go. Thanks in advance!

CodePudding user response：

I would use the ActiceRecord::QueryMethods::WhereChain#missing method which was introduced in Ruby on Rails 6.1 like this:

Place.where.missing(:place_owners)

CodePudding user response：

In older versions:

Place.includes(:place_owners).where(place_owners: { id: nil })
Place.left_joins(:place_owners).where(place_owners: { id: nil })

Another interesting option is using EXISTS. Very often such queries have better performance, but unfortunately rails haven't such feature yet (see discussions here and here)

Place.where_not_exists(:place_owners)
Place.where_assoc_not_exists(:place_owners)

To use these methods you need to use where_exists or activerecord_where_assoc gem