I currently working on a backend of an application and try to create a table Follower over a table User. It’s a many-to-many relationship so it need to create a third association table to make the relation work.
A user can follow a person, so the table will have user_id, followed_user_id.

While I asked to chat GPT and look at stack overflow post (here and here) which seems working at a first point of view, according to the doc of SqlAlchemy, it end up with error on my code.

Here is my orignal tables:
/project/models/users.py

from project.models.base import Base, BaseMixin
from dataclasses import dataclass
from sqlalchemy.orm import relationship

follow = Table(
    'follow',
    Base.metadata,
    Column('following_id', Integer, ForeignKey('user.id')),
    Column('follower_id', Integer, ForeignKey('user.id'))
)

@dataclass()
class User(Base, BaseMixin):
    __tablename__ = "user"

    username: str = Column(String(30), nullable=True, unique=True)
    email: str = Column(String(254), unique=True, nullable=False)
    password: str = Column(String(60), nullable=False)
    active: bool = Column(Boolean(), default=True, nullable=False)
    admin: bool = Column(Boolean(), default=False, nullable=False)
    followers = relationship('user',
                                secondary = follow,
                                primaryjoin = (follow.c.following_id == id),
                                secondaryjoin = (follow.c.follower_id == id),
                                backref = 'children'
                                )

/project/models/base.py

Base = declarative_base()


@dataclass
class BaseMixin:
    id: int = Column(Integer, primary_key=True, autoincrement=True)
    created_at: datetime = Column(DateTime(timezone=True), server_default=func.now())
    updated_at: datetime = Column(
        DateTime(timezone=True), onupdate=func.now(), default=func.now()
    )

Note here I’m using these version containerized into Docker container
/project/requirements.txt

Flask==2.3.2
Flask-SQLAlchemy==3.0.3
gunicorn==20.1.0
psycopg2-binary==2.9.6
redis==5.0.1
black==23.12.0
Flask-Bcrypt==1.0.1
boto3==1.34.2
pydantic==2.5.3
pydantic[email]
flask-cors

The following code lead to this error:

WARNING:AuthEndPoint:Unexpected error during login: relationship 'followers' expects a class or a mapper argument (received: <class 'sqlalchemy.sql.schema.Table'>)

Which seems due to this line

relationship('user', ...)

Where the relationship need to have User -> The class (ie stack overflow post cited before). When I change it to User, like this

relationship('User', ...)

I get this error:

sqlalchemy.exc.ArgumentError: Could not locate any simple equality expressions involving locally mapped foreign key columns for primary join condition 'follow.following_id = :following_id_1' on relationship User.followers.  Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or are annotated in the join condition with the foreign() annotation. To allow comparison operators other than '==', the relationship can be marked as viewonly=True.

Here there is an issued with view only that I don’t understand that much. In the second post it add some code that I don’t understand too, here is the snippet

friendship = Table(
    'friendships', Base.metadata,
    Column('friend_a_id', Integer, ForeignKey('users.id'), 
                                        primary_key=True),
    Column('friend_b_id', Integer, ForeignKey('users.id'), 
                                        primary_key=True)
)

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    # this relationship is used for persistence
    friends = relationship("User", secondary=friendship, 
                           primaryjoin=id==friendship.c.friend_a_id,
                           secondaryjoin=id==friendship.c.friend_b_id,
    )

    def __repr__(self):
        return "User(%r)" % self.name

# this relationship is viewonly and selects across the union of all
# friends
friendship_union = select([
                        friendship.c.friend_a_id, 
                        friendship.c.friend_b_id
                        ]).union(
                            select([
                                friendship.c.friend_b_id, 
                                friendship.c.friend_a_id]
                            )
                    ).alias()
User.all_friends = relationship('User',
                       secondary=friendship_union,
                       primaryjoin=User.id==friendship_union.c.friend_a_id,
                       secondaryjoin=User.id==friendship_union.c.friend_b_id,
                       viewonly=True) 

This is so all I’ve read and try, don’t seems to work on my place. My code looks like much of the first post answer but don’t find well on my PC.

If there is any need, file ref or whatever I can share that could help you to help me feel free to ask.

Much thanks for potential responses