Find records with missing associated records in Rails

Question

I have a legacy database where I have two models as:

class Purchase
  belongs_to :product
end

class Product
  has_many :purchases
end

Now over time some of the products were deleted even though the associated purchases still exist in the database. Is there a way I can get the list of all Purchases for which the associated products were deleted from the rails console?

My purchase table references product and has a column product_id

Answer 1

Purchase.left_outer_joins(:product).where(products: {id: nil})

Note rails 5 have left_outer_joins

so here basically it will result all purchases who does not have associated product.

Answer 2

Why don't you nullify the purchases when you remove a product?

class Product
  has_many :purchases, :dependent => :nullify
end

Like that, all you have to do is query the purchases where product_id is null. Hope this helps!

EDIT: If you're using this approach now, for updating the old records and making them consistent, you should do something like this:

Purchase.where.not(product_id: Product.select(:id).distinct).update_all(product_id: nil)

So like this it should work by now

Answer 3

Rails 6.1 added the missing wherechain. Now you can achieve the same as Anand's answer with Purchase.where.missing(:products)

https://edgeapi.rubyonrails.org/classes/ActiveRecord/QueryMethods/WhereChain.html#method-i-missing