SQLAlchemy - Self referential Many-to-many relationship with extra column

Question

I have a model representing user and I want to create a relationship between users representing that they are friends. My functional model with association table and methods to list all the friends look 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 asking for frienship and status pending before confirmation (just like you know it from Facebook) so it is necesarry to add an extra column to the frienship table. According to the SQLAlchemy tutorial I should create an Association Object but how to make it self referential again?

Or is it possible to just add this column to my current frienship table and access and change the status value there somehow?

Thanks

Answer 1

All you need is to add primaryjoin in your table and also making two foreignkey in table of Friendship, 'primary_key' too. 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 like:

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

Answer 2

I have answered to a similar question that solves Self-referential many-to-many relationship with Association Object.

See https://stackoverflow.com/a/62281276/9387542

Also, if you solely looking for establing a frienship model, I suggest you to establish Many-to-many relationship with the model from https://stackoverflow.com/a/7317251/9387542