Find Missing Associations in Rails

My Rails app didn’t have the dependent: :destroy option configured on one of my models so when the parent model got deleted it left a bunch of orphaned records in the database, and these were causing one of my views to blow up as it was trying to read from a record that didn’t exist.

I was ready to write a bit of SQL or a simple Ruby script to find all the orphaned records but then I found a comment on this post and learned that Rails 6 introduced a feature which makes this a piece of cake. It’s part of ActiveRecord and it’s called where.missing.

Use where.missing to find missing associations

The method builds a SQL query for you which uses LEFT OUTER JOIN to find the missing records and return them as an array:

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

Just need the IDs? Pluck them out

For an array of post IDs which have missing authors simply use the where.missing and add a pluck on the end:

Post.where.missing(:author).pluck(:id)
=> [101, 154, 63]

Multiple associations work too

You can also combine associations in case you want to find posts that are missing both an author and any comments:

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