Rails 6.1 adds query method missing to find orphan records

Rails 6.1 adds a query method missing to search for orphan records within ActiveRecord.

We often find ourselves querying an ActiveRecord model for orphan objects for reasons like cleanup or bulk updates.

Example

Let us consider the following models.

# app/models/manager.rb
class Manager < ApplicationRecord
  has_many :job_listings
end

# app/models/job_listing.rb
class JobListing < ApplicationRecord
  has_many :job_applications
  belongs_to :manager
end

# app/models/job_application.rb
class JobApplication < ApplicationRecord
  belongs_to :job_listing
end

Before Rails 6.1

Now let us try to find all the job listings which do not have a manager assigned.

[1] pry(main)> JobListing.left_joins(:manager).where(managers: {id: nil})
JobListing Load (0.2ms)  SELECT "job_listings".* FROM "job_listings" 
LEFT OUTER JOIN "managers" ON "managers"."id" = "job_listings"."manager_id" 
WHERE "managers"."id" IS NULL LIMIT ?  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Manager id: 3, name: "Jane Doe", created_at: "2020-01-20 14:31:16", updated_at: "2020-01-20 14:31:16">]>

In the example above, we have used left_joins, but we could also have used includes or eager_load to achieve the same result.

After Rails 6.1

Rails 6.1 adds a query method missing to the ActiveRecord::QueryMethods::WhereChain class.

It returns a new relation with a left outer join and where clause between the parent and child models to identify missing relations.

Similarly from the example above, let us find all the job listings which do not have a manager assigned.

[1] pry(main)> JobListing.where.missing(:manager)
JobListing Load (0.1ms)  SELECT "job_listings".* FROM "job_listings" 
LEFT OUTER JOIN "managers" ON "managers"."id" = "job_listings"."manager_id" 
WHERE "managers"."id" IS NULL LIMIT ?  [["LIMIT", 11]]
=> #<ActiveRecord::Relation [#<Manager id: 3, name: "Jane Doe", created_at: "2020-01-20 14:31:16", updated_at: "2020-01-20 14:31:16">]>
[2] pry(main)>

We can also combine multiple relations by passing in multiple relation names to the method.

For example, to find job listings which are missing both a manager and any job applications:

[1] pry(main)> JobListing.where.missing(:manager, :job_applications)
JobListing Load (0.1ms)  SELECT "job_listings".* FROM "job_listings"  
LEFT OUTER JOIN "managers" ON "managers"."id" = "job_listings"."manager_id"  
LEFT OUTER JOIN "job_applications" ON "job_applications"."job_listing_id" = "job_listings"."id"  
WHERE "managers"."id" IS NULL AND "job_applications"."id" IS NULL LIMIT ?  [["LIMIT", 11]]
  => #<ActiveRecord::Relation []>
[2] pry(main)>

In the example above, even though we have a job listing where a manager is not assigned, an empty relation is returned because that job listing had job applications.

Here, this query identified job listings which had neither a manager, nor any job applications.