CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
anonn023432
anonn023432

Reputation: 3120

Find records with missing associated records in Rails

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

Upvotes: 0

Views: 1574

Answers (3)

Tashows
Tashows

Reputation: 559

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

Upvotes: 2

Ronan Lopes
Ronan Lopes

Reputation: 3398

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

Upvotes: 2

Anand
Anand

Reputation: 6531

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.

Upvotes: 3

Related Questions