sqlalchemy-flask: How to count self relation

Question

I have the following model:

Base = declarative_base()

class Comment(Base):
    __tablename__ = "commenting_comment"

    id = Column(UUID, primary_key=True, index=True)
    text = Column(String)
    
    parent_id = Column(UUID, ForeignKey('commenting_comment.id'))
    parent = relationship('Comment', remote_side=[id], backref="replies")

I want to count the number of replies for each Comment. Currently, I have the following query:

ids = ["uuid1", "uuid2", "uuid3"]
qs = db.query(
    Comment,
    func.count('id').label("replies_count"),
).select_from(Comment).group_by(Comment.id).filter(
    Comment.id.in_(ids)
).all()

There are two problems:
1- It returns replies_count=1 for all of the rows.
2- I want replies_count to be in the comment's body but it returns replies_count out of the comment body. (as follows)

comments = [
    {"Comment": {...}, "replies_count": 1},
    {"Comment": {...}, "replies_count": 1},
]

How can I solve these two problems?

UPDATE
In Django corresponding query will be:

Comment.objects.filter(id__in=ids).annotate(replies_count=Count("replies"))

But I don't know how to implement it in the flask and sqlalchemy.

sqlalchemy-flask: How to count self relation

0 Answers