I am following the fastapi course from freecodecamp(https://www.youtube.com/watch?v=0sOvCWFmrtA). I am trying to join 2 tables, Vote and Post. The model for vote:-
class Vote(Base):
__tablename__="votes"
user_id=Column(Integer,
ForeignKey("users.id",ondelete="Cascade"),
primary_key=True)
post_id=Column(Integer,
ForeignKey("posts.id",ondelete="Cascade"),
primary> _key=True)
The model for Post:-
class Post(Base):
__tablename__="posts"
id=Column(Integer,primary_key=True,nullable=False)
title=Column(String,nullable=False)
content=Column(String,nullable=False)
published=Column(Boolean,server_default='True',nullable=False)
created_at=Column(TIMESTAMP(timezone=True),nullable=False,
server_default=text("now()"))
user_id=Column(Integer,ForeignKey("users.id",ondelete="CASCADE"),
nullable=False)
user = relationship("User",foreign_keys=user_id,lazy=False)
The query I am using to join Post and Vote is:-
results=db.query(models.Post,func.count(models.Vote.post_id).label("votes")).join(
models.Vote,
models.Vote.post_id==models.Post.id,isouter=True).group_by(models.Post.id)
However, when I print the sql part of this query I get:-
SELECT posts.id AS posts_id, posts.title AS posts_title, posts.content AS posts_content, posts.published AS posts_published, posts.created_at AS posts_created_at, posts.user_id AS posts_user_id, count(votes.post_id) AS votes, users_1.id AS users_1_id, users_1.email AS users_1_email, users_1.password AS users_1_password, users_1.created_at AS users_1_created_at
FROM posts LEFT OUTER JOIN votes ON votes.post_id = posts.id LEFT OUTER JOIN users AS users_1 ON users_1.id = posts.user_id GROUP BY posts.id
Can anyone help me out on this ? Users table should not be even called on this.
I’ve looked up this issue everywhere and I am unable to find a solution, someone suggested that it could be because users_id is a foreign key, that that table is being joined automatically.
Achint Dixit is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.