Skip to content

Instantly share code, notes, and snippets.

@kerrizor
Last active March 20, 2019 12:45
Show Gist options
  • Save kerrizor/f9f9e86edfae5291902535b197f384a8 to your computer and use it in GitHub Desktop.
Save kerrizor/f9f9e86edfae5291902535b197f384a8 to your computer and use it in GitHub Desktop.
ActiveRecord Orphans
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
@JoshCheek
Copy link

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
# >> --------------------------------------------------------------------------------

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment