Like a many-to-many triangular relationship in a sqlalchemy flask

Question

What is the proper way to develop tripartite many-to-many-count sqlalchemy?

Suppose I have users, teams, and roles. Users are assigned to teams. When you assign a command, the user is also assigned a role within this command.

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 creating a proxy table for this did not work for me. I think that I misunderstand the documents and therefore do not want to confuse you with all the unsuccessful approaches that I have taken so far.

I would like to leave the question with the question: what is the right way to create a tripartite many-to-many relationship in colloid sqlalchemy.

Answer 1

After much research and digging, it seems I found the answer, finally. Since I found many pieces of other people who could hardly solve this and could not find a complete and clear answer, I thought that I could publish it here for future travelers.

If you come across this question, you may not really be looking for a tripartite lot for many. I thought it was, but I was not.

Summary: I have users, teams, and roles. If a user joins a team, he is also assigned a role in 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 became clear to me that I really did not look for tripartite "many to many", but rather for tripartite "one to many", coming out of the fourth 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 42: This is exactly the answer I was looking for - I just asked the wrong question.

Answer 2

dudes. There is an official way to solve the problem with two many using the helpers 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 this can only solve the problem of two, many !!! This will automatically process 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, it will automatically add b1 to the database and b1.as

But when I tried this method in situation 3 many-many-many, everything went up.

Here are the questions I asked: a table with several helpers for many-many-many -... relationships in a jar-sqlalchemy

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