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 functional 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

---