How to delete many-to-many association where uselist=True

Question

In my application, instances of Parent share a one-to-many relationship with instances of Child. Each Parent may have many children, but each Child may have only one Parent. I understand how this relationship works.

One important feature is that only one of a Parent's children may be active at any one time. At first, I tried to represent this by using a boolean on the Child model, but it was too difficult to enforce the constraint. Instead, I opted to use an association table which would just keep record of which children were active at a given time.

Here's a diagram of the relationship generated by dbdiagram.io.

In order to represent this relationship, I'm using the uselist=True flag, which tells SQLAlchemy to return a single instance instead of a list when querying via the relationship.

I need to be able to delete these relationships, thus deactivating the children, without deleting either the Parent or the Child. I've tried using session.delete(parent.active_child), but this deletes the Child record, not just the association. I see that there are special instructions for deleting from M2M relationships, but this assumes that the relationship query returns a list, thus allowing the developer to use association.remove(instance). This is not true in my case.

Please see the example below. Note both relationships on the Parent model.

children_active = Table('children_active', Base.metadata,
    Column('parent_id', Integer, ForeignKey('parents.id'), primary_key=True, unique=True),
    Column('child_id', Integer, ForeignKey('children.id'), primary_key=True)
)

class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref='parent')
    active_child = relationship("Child", secondary=children_active, uselist=False)

class Child(Base):
    __tablename__ = 'children'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parents.id'))

p1 = Parent()
p1.children = [Child() for i in range(3)]
p1.active_child = p1.children[0]

print(p1.children)      # [Child<1>, Child<2>, Child<3>]
print(p1.active_child)  # Child<1>

session.delete(p1.active_child)

print(p1.children)      # [Child<2>, Child<3>]
print(p1.active_child)  # None

active_child is emptied, which is correct, but the Child object it represented is also deleted, which is unacceptable.

Matt Davis Matt Davis · Accepted Answer · 2019-07-30T22:55:34

I figured this out. Reassigning the active_child value overwrites the relationship and leaves the Child object untouched. Setting active_child to None deletes the relationship.

p1 = Parent()
p1.children = [Child() for i in range(3)]
p1.active_child = p1.children[0]

print(p1.children)      # [Child<1>, Child<2>, Child<3>]
print(p1.active_child)  # Child<1>

p1.active_child = p1.children[1]
session.commit()

print(p1.children)      # [Child<1>, Child<2>, Child<3>]
print(p1.active_child)  # Child<2>

p1.active_child = None
session.commit()

print(p1.children)      # [Child<1>, Child<2>, Child<3>]
print(p1.active_child)  # None

How to delete many-to-many association where uselist=True

1 Answers