Delete parent if the child is deleted

Question

I want to delete the parent row if the associated rows in child tables have been removed.

class Child(Base):
    __tablename__ = "children"

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("parents.id", ondelete='CASCADE'))

class Parent(Base):
    __tablename__ = "parents"
    id = Column(Integer, primary_key=True)
    child = relationship(Child, backref="parent", passive_deletes=True)

If I remove the child

child_obj = session.query(Child).first()
session.delete(child_obj)
session.commit()

It does delete the child obj but parent remains as it is. I want to remove the parent as well using cascading.

Orvil

You may want to have a look to this post https://mcmap.net/q/134994/-sqlalchemy-cascade-delete. Also, I have never used passive_deletes attribute, but by reading the documentation, it seems that it does the contrary of what you want. – Tarriance

@CloC Yes, the post I had seen it before it does the opposite of what I want. I believe there is no straightforward way of doing it using cascading. passive_deletes might not be the best param here but wanted to know what everybody thinks of it. Thanks for the comment though – Orvil

I do not believe you can implement purely using sqlalchemy constructs, as this use case is not a very common one. I would implement it using the SQL level DELETE and UPDATE triggers by checking (and deleting) the rows in the Parent which do not have any more children. If the database allows, one could create a computed column (count of children) and delete all such parents where the children count is 0. – Dilatation

Also see this: https://mcmap.net/q/806734/-how-to-cascade-a-delete-from-a-child-table-to-the-parent-table – Dilatation

Answer 1

You can read this thread: Linking Relationships with Backref

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String)

    addresses = relationship("Address", backref="user")

class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))

And you can define it in your child class:

parent = relationship(Parent, backref=backref("children", cascade="all,delete"))

Blindly

Answer 2

You could do this for simple^* cases by creating a listener that intercepts deletions of Child instances and deletes the parent if there are no other children.

import sqlalchemy as sa

@sa.event.listens_for(sessionmaker, 'persistent_to_deleted')
def intercept_persistent_to_deleted(session, object_):
    # We are only interested in instances of Child.
    if not isinstance(object_, Child):
        return
    p = object_.parent
    # Handle null parents.
    if p is None:
        return
    cs = session.query(Child).filter(Child.parent == p).count()
    if cs == 0:
        session.delete(p)

^* I would recommend thorough testing if your code is doing things like deleting children and then creating new children with the deleted children's parents in the same session. The listener works in this case:

c = session.query(Child).first()
p = c.parent
session.delete(c)
c1 = Child(parent=p)
session.add(c1)
session.commit()

but hasn't been tested on anything more complicated.

Crookback

Note that the sessinmaker argument to the decorator is a session factory. It could be the result of calling orm.sessionmaker(bind=engine) or whatever your application uses as a session factory. – Crookback

Answer 3

The behavior you're referring to is kinda simple. But once the application gets larger, you'd need more sophisticated methods. I, personally, implement a static method remove for every class. It takes the id and any other parameter necessary to know the delete scheme.

The answer is already given... But that's - not efficient - approach just to give you an example.

class Child(Base):
    __tablename__ = "children"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("parents.id", ondelete='CASCADE'))
    
    @staticmethod
    def remove(_id_):
        child = Child.query().get(_id_)
        Parent.remove(child.parent_id)
        session.delete(child)

class Parent(Base):
    __tablename__ = "parents"
    id = Column(Integer, primary_key=True)
    child = relationship(Child, backref="parent", passive_deletes=True)

    @staticmethod
    def remove(_id_):
        parent = parent.query().get(_id_)
        session.delete(parent)

If you, for example, added a Column named is_old - in the Child class - that's a binary integer 0 or 1 and wanted to delete the parent of the Child that has is_old == 1, It'll be very easy task.

Entrails