SQLAlchemy - Many-to-many self-relational relationship with optional column

Question

I have a model representing a user, and I want to create relationships between users representing that they are friends. My functional model with an association table and methods for listing all friends looks like this:

friendship = db.Table('friend', db.Column('id', db.Integer, primary_key=True), db.Column('fk_user_from', db.Integer, db.ForeignKey('user.id'), nullable=False), db.Column('fk_user_to', db.Integer, db.ForeignKey('user.id'), nullable=False) ) class User(db.Model): ... ... friends = db.relationship('User', secondary=friendship, primaryjoin=(friendship.c.fk_user_from==id), secondaryjoin=(friendship.c.fk_user_to==id), backref = db.backref('friend', lazy = 'dynamic'), lazy = 'dynamic') def list_friends(self): friendship_union = db.select([ friendship.c.fk_user_from, friendship.c.fk_user_to ]).union( db.select([ friendship.c.fk_user_to, friendship.c.fk_user_from] ) ).alias() User.all_friends = db.relationship('User', secondary=friendship_union, primaryjoin=User.id==friendship_union.c.fk_user_from, secondaryjoin=User.id==friendship_union.c.fk_user_to, viewonly=True) return self.all_friends 

The problem is that I need to implement a status request and a status that is awaiting confirmation (as you know from Facebook), so I need to add an additional column to the frienship table. According to the SQLAlchemy tutorial, I have to create an association object, but how do I make it a link again?

Or can you just add this column to my current frienship table and access and change the status value there?

thanks

Answer 1

All you need to do is add primaryjoin to your table and also make two foreign keys in the Friendship table, 'primary_key'. you also need to make friendship as a class.

 class Friendship(db.Model): __tablename__ = 'friend' fk_user_from = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True) fk_user_to = db.Column(db.Integer, db.ForeignKey('user.id'), primary_key=True) extra_field = db.Column(db.Integer) class User (db.Model): __tablename__ = 'user' id = db.Column(db.Integer, primary_key=True) user_to = db.relationship('Friendship',backref='to', primaryjoin=id==Friendship.fk_user_to) user_from = db.relationship('Friendship',backref='from', primaryjoin=id==Friendship.fk_user_from ) 

And to add a friend, you need to define Friendship as:

 friend = Friendship(extra_field=0 , to=me , from=my_friend)