Find and destroy dependent records

Question

Over a period of time my Rails app has had various rewrites, and in some cases incorrect model associations.

Currently my User model has_many :posts and its destroy method correctly removes all dependent Posts, but at times when things were improperly written this did not happen. I'm now left with a handful of Post records that cause errors all over because their User does not exist.

What would be the most efficient way to manually filter through all Post records, check if its User actually exists, and if not destroy that Post?

I'm imagining something like:

Post.all.select{ |post| post.user.nil? }.destroy

But that seems incredibly inefficient for thousands of records. I'd love to know the best way to do this. Thank you!

Answer 1

Fastest way would probably be to do it directory in the db console, but if you've got other dependent relationships and activerecord callbacks that you need to get fired, you could do something like:

Post.where("id in (select p.id from posts p left outer join users u on p.user_id = u.id where u.id is null)").destroy_all

Answer 2

any reason why you cannot do it directly on the database?

delete from posts where user_id not in (select id from users);

Answer 3

Delete the orphan posts using

• If you want to destroy the dependent associations with Post and run the callbacks

  Post.where("user_id NOT IN (?)", User.pluck(:id)).destroy_all
  

• If you just want to delete the posts

  Post.where("user_id NOT IN (?)", User.pluck(:id)).delete_all
  

Here is one good post about finding and deleting orphan records