Rails: How to find records with empty associations

Updated . Posted . Visible to the public.

Imagine these models and associations:

class Deck < ApplicationRecord
  has_many :cards
end

class Card < ApplicationRecord
  belongs_to :deck, optional: true
end

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
Michael Leimstädtner
Last edit
Henning Koch
Keywords
has_many
License
Source code in this card is licensed under the MIT License.
Posted by Michael Leimstädtner to makandra dev (2022-05-13 07:47)