Deleting VS Finding Orphans using ActiveRecord helpers

Question

I'm trying to delete all the organizations that no longer have any users.

Using the below code, I can find all the records I wish to delete:

Organization.includes(:users)
  .where(users: { id: nil })
  .references(:users)

When I add delete_all, I get the same error I would get if I didn't include references:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "users"

I could probably write the solution in pure SQL, but I don't understand why Rails isn't keeping the reference to users when I add the delete_all statement.

Here are some more details:

Organization:
  has_many :users

User:
  belongs_to :organization

Answer 1

I've found the includes useful only for eager loading (and it can rarely handle my cases), and when coupled with references it generates something completely insane (aliasing every single field with something like tN_rM) even though it actually does a LEFT OUTER JOIN... Which could help if it didn't vanish once delete_all appears!

I've found that it's much clearer and simpler just to use exists. It's Arel (and there's no point in avoiding it, its under the hood of ActiveRecord anyway), but it's such a tiny portion that it's barely noticeable:

Organization.where(
  User.where('users.organization_id = organizations.id').exists.not
)

Or, if this string of SQL doesn't look nice to you, use a bit more Arel, so it gets noticeable:

Organization.where(
  User.where(organization_id: Organization.arel_table[:id]).exists.not
) # I tend to extract these   ^^^^^^^^^^^^^^^^^^^^^^^ into local variables

That handles chaining .delete_all on top just fine, since it's not (syntactically) a join, even though it's effectively equivalent to one.

The magic behind this

SQL has an EXISTS operator that is similar in functionality to a join, except for inability of selecting fields from a joined table. It forms a valid boolean expression which can be negated and thrown into WHERE-conditions.

In the "SQL-free" form I'm using an expression "column of a table", which turns out to be usable in Rails' hash-conditions. It's an accidental discovery, one of the few uses of Arel that does not make code too bulky.