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
Posted by Michael Leimstädtner to makandra dev (2022-05-13 07:47)