Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
377 views
in Technique[技术] by (71.8m points)

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

What's the proper way to design a three-way many-to-many in flask-sqlalchemy?

Assume I have users, teams and roles. Users are assigned to teams. When assigned to a team, the user is also assigned a role within that team.

from myapp import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=True)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<User(%s)>" % self.name

class Team(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=True)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<Team(%s)>" % self.name

class Role(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(128), unique=True)

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<Role(%s)>" % self.name

Several approaches to design a proxy table for this have failed for me. I think I am misunderstanding the docs and thus don't want to confuse you with all the failed approaches I've taken, so far.

I'd like to leave it with the question: What's the proper way to design a three-way many-to-many relationship in flask-sqlalchemy.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...