I'm trying to build a simple app where users can follow other users so, I have a table "Users" that has all the user's info, and since a user can follow many users and that user can have many followers we get a Many-to-Many relationship, so I created a linking table. Here's my code:
class User(Base):
__tablename__ = "Users"
user_id = Column(Integer, primary_key=True, index=True)
name = Column(String)
email = Column(String, unique=True)
class UserFollowing(Base):
__tablename__ = "UsersFollowing"
user_id = Column(Integer, ForeignKey(
"Users.user_id", ondelete="CASCADE"), primary_key=True)
user_following_id = Column(Integer, ForeignKey(
"Users.user_id", ondelete="CASCADE"), primary_key=True)
As you can see the "UsersFollowing" table has composite Primary Key that is build from the Foreign Keys taken from the "User" table (user, and who is following).
Now I want to get the following: "Given a user id return all the users that follows".
I'm new in SQLAlchemy and I don't know how to get the result.
If you're using the SQLAlchemy ORM, you should be able to get the users by issuing a join and filtering:
db_session.query(User)\
.join(UsersFollowing, User.id == UsersFollowing.user_following_id)\
.filter(UsersFollowing.user_id == <userid>)