Any rails plugins/gems for detecting orphaned records?

Question

Looking for something that can go through the relationships defined in models and can check the DB for orphaned records/broken links between tables.

Answer 1

(for the latest version of the script below, see https://gist.github.com/KieranP/3849777)

The problem with Martin's script is that it uses ActiveRecord to first pull records, then find the associations, then fetch the associations. It generates a ton of SQL calls for each of the associations. It's not bad for a small app, but when you have a multiple tables with 100k records and each with 5+ belongs_to, it can take well into the 10+ minute mark to complete.

The following script uses SQL instead, looks for orphaned belongs_to associations for all models in app/models within a Rails app. It handles simple belongs_to, belongs_to using :class_name, and polymorphic belongs_to calls. On the production data I was using, it dropped the runtime of a slightly modified version of Martin's script from 9 minutes to just 8 seconds, and it found all the same issues as before.

Enjoy :-)

task :orphaned_check => :environment do

  Dir[Rails.root.join('app/models/*.rb').to_s].each do |filename|
    klass = File.basename(filename, '.rb').camelize.constantize
    next unless klass.ancestors.include?(ActiveRecord::Base)

    orphanes = Hash.new

    klass.reflect_on_all_associations(:belongs_to).each do |belongs_to|
      assoc_name, field_name = belongs_to.name.to_s, belongs_to.foreign_key.to_s

      if belongs_to.options[:polymorphic]
        foreign_type_field = field_name.gsub('_id', '_type')
        foreign_types = klass.unscoped.select("DISTINCT(#{foreign_type_field})")
        foreign_types = foreign_types.collect { |r| r.send(foreign_type_field) }

        foreign_types.sort.each do |foreign_type|
          related_sql = foreign_type.constantize.unscoped.select(:id).to_sql

          finder = klass.unscoped.select(:id).where("#{foreign_type_field} = '#{foreign_type}'")
          finder.where("#{field_name} NOT IN (#{related_sql})").each do |orphane|
            orphanes[orphane] ||= Array.new
            orphanes[orphane] << [assoc_name, field_name]
          end
        end
      else
        class_name = (belongs_to.options[:class_name] || assoc_name).classify
        related_sql = class_name.constantize.unscoped.select(:id).to_sql

        finder = klass.unscoped.select(:id)
        finder.where("#{field_name} NOT IN (#{related_sql})").each do |orphane|
          orphanes[orphane] ||= Array.new
          orphanes[orphane] << [assoc_name, field_name]
        end
      end
    end

    orphanes.sort_by { |record, data| record.id }.each do |record, data|
      data.sort_by(&:first).each do |assoc_name, field_name|
        puts "#{record.class.name}##{record.id} #{field_name} is present, but #{assoc_name} doesn't exist"
      end
    end
  end

end