Update property in all child objects if parent is updated in one to many relationship

Question

I have a one to many relationship between a Project and a Note (i.e. a project will have many notes but a note belongs to a single project) in my Flask app:

class BaseDocument(db.Model):
    __abstract__ = True

    created_at = db.Column(db.DateTime, default=datetime.now)
    updated_at = db.Column(db.DateTime, onupdate=datetime.now)
    archived = db.Column(db.Boolean, default=False)

    def __repr__(self):
        return str(self.__dict__)

class Project(BaseDocument):
    __tablename__ = "project"

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    parent_id = db.Column(db.Integer, db.ForeignKey("project.id"))
    notes = db.relationship(
        "Note",
        backref="project",
        lazy=True,
        order_by="Note.updated_at",
        cascade="all, delete, delete-orphan",
    )

class Note(BaseDocument):
    __tablename__ = "note"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String)
    content = db.relationship(
        "Bullet", backref="note", lazy=True, order_by="Bullet.order"
    )
    project_id = db.Column(db.Integer, db.ForeignKey("project.id"))

I would like to do something that seems to be very simple but I can't figure out how: I want to update the archived property of all the child notes in a project to True if the parent project archived property is also set to True.

I can only find answers here in StackOverfow about how to update the parent object if a child is updated (the oposite of what I am trying to do), so I am assuming that what I want to do is trivial and I am just bad at sqlalchemy. How can I set this up? Do I need to use a after_update event on Project? If yes, how can I access all the child Notes and set archived=True for all of them?

I have tried to setup the following event listener with no success, I get the following error AttributeError: 'InstrumentedList' object has no attribute 'update':

@db.event.listens_for(Project, "after_update")
def archive_notes(mapper, connection, target):
    obj = target.object
    connection.execute(target.notes.update(archived=True))

Any help will be very appreciated!

user_2144390 · Answer

You're on the right track by using after_update. Here's a working example:

import sqlalchemy as sa
from sqlalchemy.orm import declarative_base, relationship

connection_uri = (
    "mssql+pyodbc://@localhost:49242/myDb?driver=ODBC+Driver+17+for+SQL+Server"
)
engine = sa.create_engine(
    connection_uri,
    future=True,
    echo=False,
)

Base = declarative_base()


class Project(Base):
    __tablename__ = "project"
    id = sa.Column(sa.Integer, primary_key=True)
    title = sa.Column(sa.Unicode(100), nullable=False)
    archived = sa.Column(sa.Boolean, nullable=False, default=False)


class ProjectNote(Base):
    __tablename__ = "project_note"
    id = sa.Column(sa.Integer, primary_key=True)
    project_id = sa.Column(sa.Integer, sa.ForeignKey("project.id"))
    project = relationship(Project)
    note_text = sa.Column(sa.Unicode(255), nullable=False)
    archived = sa.Column(sa.Boolean, nullable=False, default=False)


@sa.event.listens_for(Project, "after_update")
def archive_remaining_project_notes(mapper, connection, target):
    if target.archived:
        sql = """\
        UPDATE project_note SET archived = :yes 
        WHERE project_id = :proj_id
            AND archived = :no
        """
        connection.execute(
            sa.text(sql),
            {"yes": True, "no": False, "proj_id": target.id},
        )


# 
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
# 

p1 = Project(title="project 1")
p1n1 = ProjectNote(
    project=p1, note_text="project 1, note 1, archived", archived=True
)
p1n2 = ProjectNote(project=p1, note_text="project 1, note 2, not archived")

with sa.orm.Session(engine, future=True) as session:
    session.add_all([p1, p1n1, p1n2])
    session.commit()
    print(f"p1n2.archived is: {p1n2.archived}")  # p1n2.archived is: False

    p1.archived = True
    session.commit()
    print(f"p1.archived is: {p1.archived}")  # p1.archived is: True
    print(f"p1n2.archived is: {p1n2.archived}")  # p1n2.archived is: True

Update property in all child objects if parent is updated in one to many relationship

1 Answers