Friends List Implementation Using Python Flask

Whilst working on a recent Flask project that required a friends list relationship I realized there was a lack of explicit examples on how to implement such a thing so below is the solution I came up with.

To begin this relationship is called a self-referential many-to-many relationship since a User can be related to many Users. This is what makes this problem tricky and can tigger errors in Flask. Many of the examples you see online for friends lists with flask I would describe as a follower connections because they just document a connection between users and nothing else. The trick with a friends list is that you need other attributes associated with the connection other than just foreign keys. These attributes are used to show things like the status of the request ie. has it been accepted ect.

Follower Example
To create a follower example you can just implement an association table.

follower_connection = db.table(
'follower_connections', metadata,
db.Column('follower_id', db.Integer, db.ForeignKey('user.id'), primary_key = True)
db.Column('followed_id', db.Integer, db.ForeignKey('user.id'), primary_key = True)
)

class User(db.Model)
    __tablename__ = 'users'

    id = db.Column(db.Integer, primary_key = True)

    followers = db.relationship('User', secondary = 
        friend_connection, 
        primaryjoin=id==follower_connection.c.follower_id,
        secondaryjoin=id=follower_connection.c.followed_id)

As a note on the above, you could potentially create a friends list architecture using multiple association tables where depending on the status of the relationship you move the relationship between a pending table and an accepted table but I found the below example more straight forward.

Friends List Example
To create the friends list I used an association table so that I could have other values associated with the relationship.

class User(db.Model)
    __tablename__ = 'user'

    id = db.Column(db.Integer, primary_key = True)

class Friendship(db.model)
    __tablename__ = 'freindships'

    id = db.Column(db.Integer, primary_key = True)
    friendship_status = db.Column(db.Boolean, default = False)
    requestee_id = db.Column(db.Integer, db.ForeignKey('user.id))
    acceptee_id = db.Column(db.Integer, db.ForeignKey('user.id))

   requestee = db.relationship("User", foreign_keys=[requestee_id])
   acceptee = db.relationship("User", foreign_keys=[acceptee_id])

The important thing to remember about the above example is to specify the foreign key relationship or else you will get an error. Once you have the relationship established you can use the boolean status or any other field you choose to establish the state of the friendship.