After lots of research and digging it seems I've found the answer, finally. Since I found many bits and pieces of other people having a hard time solving this and couldn't find a complete and clear answer, I figured I could post it here for future travellers.
If you've hit this question, it might be possible that you aren't really looking for a three-way many-to-many. I thought I was, but I wasn't.
Recap:
I have users, teams and roles. If a user joins a team, he is also assigned a role within that team.
I went back to the scratch-board and drew what I really wanted:
+---------+---------+---------+
| user_id | team_id | role_id |
+---------+---------+---------+
| 1 | 1 | 1 |
+---------+---------+---------+
Then it started to become clear to me, that I wasn't really looking for a three-way many-to-many, but rather for a three-way one-to-many departing from a forth model.
class Membership(db.Model):
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True)
team_id = db.Column(db.Integer, db.ForeignKey('team.id'), primary_key=True)
role_id = db.Column(db.Integer, db.ForeignKey('role.id'), primary_key=True)
db.UniqueConstraint('user_id', 'team_id', 'role_id')
db.relationship('User', uselist=False, backref='memberships', lazy='dynamic')
db.relationship('Team', uselist=False, backref='memberships', lazy='dynamic')
db.relationship('Role', uselist=False, backref='memberships', lazy='dynamic')
def __init__(self, user, team, role):
self.user_id = user.id
self.team_id = team.id
self.role_id = role.id
def __repr__(self):
return "<Membership(%s)>"
Case of 42: This is exactly the answer I was looking for - I've just asked the wrong question.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…