CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
daveruinseverything
daveruinseverything

Reputation: 5167

Place SQLAlchemy event listener on many-to-many table

I'm trying to detect when a many-to-many relationship is created or deleted, but I'm having trouble figuring out the right event to listen for.

Let's say I have a User model and a Team model, and I'm using a membership table to define which users belong to which teams. Here's a simple schema (using Flask-SQLAlchemy's base model):

membership_table = db.Table('membership', db.metadata,
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('team_id', db.Integer, db.ForeignKey('team.id')),
    db.PrimaryKeyConstraint('user_id', 'team_id'))

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    teams = db.relationship('Team', secondary=membership_table, backref='users')

class Team(db.Model):
    __tablename__ = 'team'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))

What I want to do is detect when someone joins or leaves the team, and trigger an event that can do something with this information (e.g. send a notification somewhere: "Dave joined the Yankees"; "Sarah left the Mets").

I tried first attaching after_insert and after_delete events directly to the membership table, but this simply failed with an AttributeError: after_delete exception, which was more or less what I expected given that it's not a model like the others.

I tried attaching a set listener to User.teams:

@event.listens_for(User.teams, 'set')
def membership_updated(target, value, oldvalue, initiator):
    # compare `oldvalue` to `newvalue` to determine membership change

However this event is simply never triggered when I add or remove users from a team

I tried simply listen for updates to Team:

@event.listens_for(Team, 'after_update')
def test(mapper, connection, target):
    # check current membership via `target.users`

This is correctly triggered when membership changes, but the problem is I can't really reason about what's happening. Maybe someone simply edited the team name, and the membership hasn't changed at all. And if it has changed, how? Was someone added or deleted?

I'm a bit stuck on where to go next, or how else to get the necessary information.

Upvotes: 1

Views: 2552

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

In order to "detect when someone joins or leaves the team" listen for the append and remove events on Team.users relationship:

@event.listens_for(Team.users, 'append')
def receive_team_users_append(target, value, initiator):
    print(value.name, 'added to team', target.name)

@event.listens_for(Team.users, 'remove')
def receive_team_users_append(target, value, initiator):
    print(value.name, 'removed from team', target.name)

The event handlers will also fire if you append a team to a user's teams collection, thanks to how backref / back_populates mirror operations between the 2 relationship properties.

Upvotes: 6

Related Questions