Find all objects with broken association

Question

I have two models in my rails app with a has many and belongs to association. Category has many items and Item belongs to category.

These models are associated in the normal way through a category_id column in the Item model.

I'm looking for a quick way of finding all elements in the database with broken associations. i.e. find all categories that exist with no associated items and items that exist with no associated category.

For example, if I have an item with a category_id of 7, but the category with id 7 has been deleted then this would be considered broken.

Answer 1

For your example, to find items with category_id's for categories which don't exist any more:

Item.where('NOT EXISTS (SELECT * FROM categories where category.id = item.category_id)')

You might want to look at this as well: A rake task to track down missing database indexes (not foreign keys though, but indexes): https://github.com/eladmeidar/rails_indexes

Answer 2

A very effective way is using find_by_sql to let the database do the heavy lifting:

uncategorized_items = Item.find_by_sql("select * from items where category_id IS NULL")

Another way is a named scope:

class Item < ActiveRecord::Base
  scope :uncategorized, where(:category_id => nil) # rails 3

  # or...

  named_scope :uncategorized, :conditions => 'category_id IS NULL'
end

These are just a couple of ideas. I assume that once you've found these broken associations you plan to fix them, right? You might want to use validates_associated in both models if it's important to you that this not happen again.

You can use find_by_sql and a left outer join to find all the items in one table but not another. Here, I use a downloads table and an image_files table (I've only included the SQL):

SELECT d.*, d.image_file_id
from downloads as d
LEFT OUTER JOIN image_files as i 
ON i.id = d.image_file_id 
WHERE d.image_file_id IS NULL