Read more

Rails: How to find records with empty associations

Michael Leimstädtner
May 13, 2022Software engineer at makandra GmbH

Imagine these models and associations:

class Deck < ApplicationRecord
  has_many :cards
end

class Card < ApplicationRecord
  belongs_to :deck, optional: true
end
Illustration web development

Do you need DevOps-experts?

Your development team has a full backlog? No time for infrastructure architecture? Our DevOps team is ready to support you!

  • We build reliable cloud solutions with Infrastructure as code
  • We are experts in security, Linux and databases
  • We support your dev team to perform
Read more Show archive.org snapshot

Now you want to find all Decks without any Card or all Cards without a Deck.

Rails 6.1+

Rails 6.1 introduced a handy method ActiveRecord#missing Show archive.org snapshot to find records without given associations.

Deck.where.missing(:cards)
SELECT "decks".*
FROM "decks"
LEFT OUTER JOIN "cards"
ON "cards"."deck_id" = "decks"."id"
WHERE "cards"."id" IS NULL
Card.where.missing(:deck)
SELECT "cards".*
FROM "cards"
LEFT OUTER JOIN "decks"
ON "decks"."id" = "cards"."deck_id"
WHERE "decks"."id" IS NULL

Older Rails versions

If you don't have Rails 6.1 yet, you can use this not-so-intuitive syntax for the same results:

Deck.left_joins(:cards).where(cards: {id: nil})
SELECT "decks".*
FROM "decks"
LEFT OUTER JOIN "cards"
ON "cards"."deck_id" = "decks"."id"
WHERE "cards"."id" IS NULL
Card.left_joins(:deck).where(decks: {id: nil})
SELECT "cards".*
FROM "cards" 
LEFT OUTER JOIN "decks"
ON "decks"."id" = "cards"."deck_id" 
WHERE "decks"."id" IS NULL
Posted by Michael Leimstädtner to makandra dev (2022-05-13 09:47)