ActiveRecord Query to identify orphan child records

Question

I have Post and User models. Every post belongs to a user. However, during database import, some erroneous user_ids have been entered on some posts. What would be the query to get posts whose user_ids don't refer to any user? Thanks.

Answer 1

As @user2553863 already mentioned, Rails 5 added support for left_outer_joins, meaning you can now do this in an efficient manner and without writing any SQL like this:

Post.left_outer_joins(:user).where(users: {id: nil}).delete_all

This will find any orphaned posts (those without users) and delete them. Here, user is the association name and users is the name of the joined table. You do not have to fire an additional SELECT to query all the user ids, which might break when you have many users.