ActiveRecord Orphans

orphans.rake

desc "Check for ActiveRecord orphans"
task orphan_check: :environment do
  # Silly housekeeping.. I'm sure there's a better way to spin up the app before
  #   we run the rake task but I A) can't remember and B) can't be bothered I'm
  #   just spiking some code here. Only PITA here is it makes us Rails specific.
  #
  Rails.application.eager_load!
  ActiveRecord::Base.descendants.each{ |k| k.connection }

  # Load up all the AR models in our app
  #
  ar_classes = ActiveRecord::Base.descendants

  ar_classes.each do |klass|
    # Find the klass's belongs_to relationships
    #
    belongs_to_assoc = klass.reflect_on_all_associations.select{|assoc| assoc.macro == :belongs_to}

    if belongs_to_assoc.any?
        puts "#{klass.name}"
        puts "=" * klass.name.length
        puts "\n"

      belongs_to_assoc.each do |assoc|
        # For the records that have a declared owner record, filter for ones
        #   where their owner's record's ID is nil, meaning that owner record
        #   does not exist in the DB.
        #
        results = klass.eager_load(assoc.name).
          where("#{assoc.foreign_key} IS NOT NULL").
          where("#{assoc.plural_name}": { id: nil })

          if results.any?
            puts "Missing #{assoc.class_name}"
            puts "-" * (assoc.class_name.length + 8)

            results.each do |result|
              puts "#{klass.name} ID: #{result.id} \t #{assoc.class_name} ID: #{result.send(assoc.foreign_key)}"
            end
          end
      end

      puts "-"*80
    end
  end
end

I had to run it to feel like I understood it, but it worked for my test case and the sql makes sense.

# set up a db to try the code against
require 'active_record'
ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:'
ActiveRecord::Schema.define do
  self.verbose = false
  create_table :users
  create_table(:posts) { |t| t.integer :user_id }
end
User = Class.new(ActiveRecord::Base) { has_many :posts }
Post = Class.new(ActiveRecord::Base) { belongs_to :user }

# set up the data
user1, user2 = User.create! [{}, {}]
user1.posts.create!
user2.posts.create!
user2.destroy

# expected results: find post 2 since it references a user that was deleted
post1, post2 = Post.order(:id).all

post1.user_id # => 1
post1.user    # => #<User id: 1>

post2.user_id # => 2
post2.user    # => nil



# Kerri's code (modified slightly)
ActiveRecord::Base.descendants.each do |klass|
  belongs_to_assoc = klass.reflect_on_all_associations.select{|assoc| assoc.macro == :belongs_to}
  
  next if belongs_to_assoc.none?

  puts klass.name
  puts "=" * klass.name.length
  puts

  belongs_to_assoc.each do |assoc|
    results = klass
      .eager_load(assoc.name)                    # does a left outer join, apparently
      .where("? IS NOT NULL", assoc.foreign_key) # I paramaterized the FK
      .where("#{assoc.plural_name}": { id: nil })

    # Just wanted to see what SQL it generates. Seems reasonable enough, though no need for it to select users.id
    # Also, it's creating AR objects here, #pluck would probably be more efficient, but probably nbd
    results.to_sql.split(/\b(?=select|left|where|from)/i).each { |s| def s.inspect; self; end }
    # => [SELECT "posts"."id" AS t0_r0, "posts"."user_id" AS t0_r1, "users"."id" AS t1_r0 ,
    #     FROM "posts" ,
    #     LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id" ,
    #     WHERE ('user_id' IS NOT NULL) AND "users"."id" IS NULL]

    if results.any?
      puts "Missing #{assoc.class_name}"
      puts "-" * (assoc.class_name.length + 8)

      results.each do |result|
        result # => #<Post id: 2, user_id: 2>
        puts "#{klass.name} ID: #{result.id} \t #{assoc.class_name} ID: #{result.send(assoc.foreign_key)}"
      end
    end
  end

  puts "-"*80
end

# >> Post
# >> ====
# >> 
# >> Missing User
# >> ------------
# >> Post ID: 2 \t User ID: 2
# >> --------------------------------------------------------------------------------