[ActiveRecord] Finding Orphans

Its common for me to look for orphans and I typically do it like this:

Child.eager_load(:parent).where(parents: { id: nil })

``

I would like to propose putting together a PR for something like this:

Child.missing(:parent)
Parent.missing(:children)

``

Please let me know if others are interested in the functionality and I would love to give it a shot!

I definitely find myself looking for orphans to clean up my database. What about adding a method scope as “missing_#{relationship_name}”

Book.missing_author #without a parent

Author.missing_books #without children

``

That functionality sounds really useful. The code you showed you use above to find orphans seems a bit more complex than an easy to use ActiveRecord method. I look forward to seeing what other people say about this idea :slight_smile:

This reminds me of another recent topic: https://groups.google.com/forum/?fromgroups#!topic/rubyonrails-core/wAkJbUb3mio

They’re discussing the idea of adding a presence scope with Person.where.present(:name)

If this method also checks for the presence of associated records like Person.where.present(:parent) then it would make sense to also have a method that does the opposite (like you’re proposing).

Or perhaps it would simply make more sense to instead use Person.where.not.present(:parent).

I even like Person.where.missing(:parent). The key distinguisher is to use modifiers on #where, just like we have with #not, rather than hang these off the root scope.

This sounds interesting.

I’d like to give this a shot! I am thinking this will be added to the activerecord/lib/active_record/relation/query_methods.rb to the WhereChain class. The missing method will accept an individual or array of relationships. The resulting scope will have a left join on each relationship as well as a where clause for each table with the id set nil:

New WhereChain method:

Post.where.missing(:author)

Equivalent:

Post.left_joins(:author).where(authors: { id: nil })

Resulting sQL

SELECT “posts”.* FROM “posts” LEFT OUTER JOIN “authors” ON “authors”.“id” = “posts”.“author_id” WHERE “authors”.“id” IS NULL

``

Once you’re getting down to specific code choices, it’s better in a PR. So feel free to submit one :+1:

You also don’t have to get everything right up front as long as you help guide reviewers. For instance, it’s fine to submit a piecemeal PR, just describe what you intend to defer for your second/third pass (e.g. tests, documentation, CHANGELOG entry).

Tom’s PR can be found at https://github.com/rails/rails/pull/34727.

Any help with this (my first) PR is appreciated!

Rafael França and I got this working at RailsConf and have submitted a PR. Let me know if anyone has suggestions for improvement!