如何在flask-sqlalchemy中实现三对多对多关系 [英] How to three-way many-to-many relationship in flask-sqlalchemy
问题描述
在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.
我想提一个问题:在flask-sqlalchemy中设计三向多对多关系的正确方法是什么.
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.
推荐答案
经过大量研究和挖掘,看来终于找到了答案.由于我发现很多其他人很难解决这个问题,并且找不到完整而清晰的答案,因此我想可以将其发布在这里,供将来的旅行者使用.
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)>"
案例42:这正是我在寻找的答案-我刚问错了问题.
Case of 42: This is exactly the answer I was looking for - I've just asked the wrong question.
这篇关于如何在flask-sqlalchemy中实现三对多对多关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!