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.

Answer 2

I'm not sure how you plan to implement this in the MVC framework, but it seems clean to do the organization purge via model action. Whenever a user is deleted, check to see of the organization has any remaining members.

in the User.rb

class User < ActiveRecord::Base
  before_destroy :close_user
  ...

 def user_organization
   Organization.where(user_id: id)
 end

  private
    def close_user
        unless user_organization.users.any? 
          user_organization.destroy
        end
    end

end

---

Added To apply callback delete solution to users being member of many organizations

If the user has multiple organizations

class User < ActiveRecord::Base
      before_destroy :close_user
      ...

     def user_organizations
       Organization.where(user_id: id)
     end

      private
        def close_user
            user_organization.find_each do |organization| 
            unless organization.users.any? 
              organization.destroy
            end
        end

    end

Caveat: this is not tested, didn't fail syntax. I don't have the data to test it fully but I think it will work. But it means running this action after every user delete, which is a system architecture decision. If it's an option, it might be worth a try.