SQLAlchemy: limit select depth for self referencing table

Viewed 181

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 ?

0 Answers
Related