Posted 14 days ago. Visible to the public. Linked content.

Rails: How to find records with empty associations

Imagine these models and associations:

Copy
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 Archive to find records without given associations.

Copy
Deck.where.missing(:cards)
Copy
SELECT "decks".* FROM "decks" LEFT OUTER JOIN "cards" ON "cards"."deck_id" = "decks"."id" WHERE "cards"."id" IS NULL
Copy
Card.where.missing(:deck)
Copy
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:

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

Your development team has a full backlog of feature requests, chores and refactoring coupled with deadlines? We are familiar with that. With our "DevOps as a Service" offering, we support developer teams with infrastructure and operations expertise.

Owner of this card:

Avatar
Michael Leimstädtner
Last edit:
4 days ago
by Henning Koch
Keywords:
has_many
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Michael Leimstädtner to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more