I have two SQLAlchemy models: Comment and User, defined as following:
Comment
class Comment(Base):
__tablename__ = 'comment'
id = Column(Integer, primary_key=True,autoincrement=True , index=True)
....
owner_id = Column(String, ForeignKey("user.id"), nullable=False)
item_id = Column(Integer, ForeignKey("item.id"), nullable=False)
original_comment_id = Column(Integer, ForeignKey("comment.id"), nullable=True)
#relationship
owner = relationship("User", back_populates="comments")
item = relationship("Item", back_populates="comments")
replies = relationship("Comment", backref=backref('parent', remote_side=[id]))
User
class User(Base):
__tablename__ = 'user'
id = Column(String, primary_key=True, index=True )
....
comments = relationship("Comment", back_populates="owner")
As shown above, each comment can be a reply to another comment.
I want to get a list of all comments for a given item, and each comment should contain its immediate parent only, if it has one.
To achieve that, I'm using the following code
comments = db.query(Comment).filter(Comment.item_id == item_id)\
.options(
joinedload(Comment.owner).options(load_only("display_name")),
joinedload(Comment.parent).options(joinedload(Comment.owner).options(load_only("display_name")))\
)
But it's not giving me just the immediate parent, but grandparents too.
How can I limit the depth to immediate parents only ?