Tip: Find records missing an association with `where.missing`
You can’t prove a negative, but what about querying a database for a negative? While the majority of the time you are writing queries to find data, there are some cases when you want the opposite: writing a query that looks for the absence of data.
When it comes to raw SQL, you can use a LEFT OUTER JOIN combined with a NULL check to find records without certain associations.
Usage
In Rails, you can apply the same concepts from SQL directly with ActiveRecord.
Let’s say you have the following model:
class Account < ApplicationRecord
has_many :recovery_email_addresses
end
If you wanted to find Accounts that have not yet set up a backup recovery email, you could write this query and everything will work just fine:
Account.left_joins(:recovery_email_addresses).where(recovery_email_addresses: { id: nil })
# SELECT "accounts".* FROM "accounts" LEFT OUTER JOIN "recovery_email_addresses" ON "recovery_email_addresses"."account_id" = "accounts"."id" WHERE "recovery_email_addresses"."id" IS NULL
But it’s kind of verbose. Since Rails 6.1, you can use a much cleaner shorthand to write this same query.
Account.where.missing(:recovery_email_addresses)
# SELECT "accounts".* FROM "accounts" LEFT OUTER JOIN "recovery_email_addresses" ON "recovery_email_addresses"."account_id" = "accounts"."id" WHERE "recovery_email_addresses"."id" IS NULL
This will generate identical SQL and is a lot easier to read. You can use this functionality on belongs_to relationships as well.
class Contract < ApplicationRecord
belongs_to :promiser, class_name: "User"
belongs_to :promisee, class_name: "User"
belongs_to :beneficiary, optional: true, class_name: "User"
end
Contract.where.missing(:promiser) # Contracts without a promiser
Contract.where.missing(:promiser, :beneficiary) # Contracts without a promiser AND beneficiary
You can also combine missing with your normal ActiveRecord chaining methods
Contact.where("amount > ?", 1200).where.missing(:promiser)
Contact.where(signed: true).where.missing(:beneficiary)
Additional Resources
Rails repository: Finding Orphan Records
Rails API docs: WhereChain#missing