SQLAlchemy many-to-many orphan deletion

Question

I'm trying to use SQLAlchemy to implement a basic users-groups model where users can have multiple groups and groups can have multiple users.

When a group becomes empty, I want the group to be deleted, (along with other things associated with the group. Fortunately, SQLAlchemy's cascade works fine with these more simple situations).

The problem is that cascade='all, delete-orphan' doesn't do exactly what I want; instead of deleting the group when the group becomes empty, it deletes the group when any member leaves the group.

Adding triggers to the database works fine for deleting a group when it becomes empty, except that triggers seem to bypass SQLAlchemy's cascade processing so things associated with the group don't get deleted.

What is the best way to delete a group when all of its members leave and have this deletion cascade to related entities.

I understand that I could do this manually by finding every place in my code where a user can leave a group and then doing the same thing as the trigger however, I'm afraid that I would miss places in the code (and I'm lazy).

Answer 1

The way I've generally handled this is to have a function on your user or group called leave_group. When you want a user to leave a group, you call that function, and you can add any side effects you want into there. In the long term, this makes it easier to add more and more side effects. (For example when you want to check that someone is allowed to leave a group).

Answer 2

I think you want cascade='save, update, merge, expunge, refresh, delete-orphan'. This will prevent the "delete" cascade (which you get from "all") but maintain the "delete-orphan", which is what you're looking for, I think (delete when there are no more parents).

Answer 3

Could you post a sample of your table and mapper set up? It might be easier to spot what is going on.

Without seeing the code it is hard to tell, but perhaps there is something wrong with the direction of the relationship?

Answer 4

I had the same problem about 3 months ago, i have a Post/Tags relation and wanted to delete unused Tags. I asked on irc and SA's author told me that cascades on many-to-many relations are not supported, which kind of makes sense since there is no "parent" in many-to-many.

But extending SA is easy, you can probably use a AttributeExtension to check if the group became empty when is removed from a User and delete it from there.