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
endBefore 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.
