CODEWITHSUNDEEP
ruby-on-railsrubyscopeassociationsscopes
Ezath
Ezath

Reputation: 3

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

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!

Upvotes: 0

Views: 660

Answers (2)

spickermann
spickermann

Reputation: 106932

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

Place.where.missing(:place_owners)

Quote from the docs:

missing(*associations)

Returns a new relation with left outer joins and where clause to identify missing relations.

For example, posts that are missing a related author:

Post.where.missing(:author)
# SELECT "posts".* FROM "posts"
# LEFT OUTER JOIN "authors" ON "authors"."id" = "posts"."author_id"
# WHERE "authors"."id" IS NULL

Upvotes: 2

mechnicov
mechnicov

Reputation: 15288

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

Upvotes: 0

Related Questions