How to three-way many-to-many relationship in flask-sqlalchemy

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.

dudes. There is official way to solve the problem with two many-many by using a helper table.

Helper_table = db.Table('Helper_table',                     db.Column('id_a', db.Integer,                               db.ForeignKey('a.id')),                     db.Column('id_b', db.Integer,                               db.ForeignKey('b.id'))                )

class A(db.Model):  # A ORM    id = db.Column(db.Integer, primary_key=True)    username = db.Column(db.String(64), index=True, unique=True)    bs = db.relationship(        'B', secondary=Helper_table, lazy='dynamic')    def __repr__(self):        return '<A {}>'.format(self.username)class B(db.Model):  # B ORM    id = db.Column(db.Integer, primary_key=True)    username = db.Column(db.String(64), index=True, unique=True)    as = db.relationship(        'A', secondary=Helper_table, lazy='dynamic')    def __repr__(self):        return '<B {}>'.format(self.username)

But it can only solve the two many-many problem !!! It will automatically handle the relationship.

a1 = A(username="a1_"+str(uuid.uuid4()))b1 = B(username="b1_"+str(uuid.uuid4()))a1.bs.append(b1)db.session.add(a1)db.session.commit()

As the code above shows that it will auto add b1 into database as well as b1.as.

But when I tried this method in 3 many-many-many situation, everything f**k up.

Here are the questions I asked: multi-helper table for many-many-many-... relationship in flask-sqlalchemy

Generally, I think it should be a feature of this repo. There should be an elegant way to handle it.