Postgresql 递归查询

每个comment记录它的父级的comment

class Comment(db.Model):    id = db.Column(db.Integer, primary_key=True)    content = db.Column(db.String)    parent_id = db.Column(db.Integer)

included_parts = db.session.query(Comment.id, Comment.parent_id, Comment.content).filter(Comment.id == 1).cte(    name="included_parts", recursive=True)incl_alias = aliased(included_parts, name="pr")parts_alias = aliased(Comment, name="p")included_parts = included_parts.union_all(    db.session.query(        parts_alias.id,        parts_alias.parent_id,        parts_alias.content).filter(parts_alias.parent_id == incl_alias.c.id))statement = select([    included_parts.c.id,    included_parts.c.content])db.session.execute(statement).fetchall()

WITH RECURSIVE included_parts(id, parent_id, content) AS (SELECT comment.id AS id, comment.parent_id AS parent_id, comment.content AS content FROM comment WHERE comment.id = %(id_1)s UNION ALL SELECT p.id AS p_id, p.parent_id AS p_parent_id, p.content AS p_content FROM comment AS p, included_parts AS pr WHERE p.parent_id = pr.id) SELECT included_parts.id, included_parts.content AS content FROM included_parts

-------------------万恶的分割线----------------

输出递归depth

included_parts = db.session.query(Comment.id, Comment.parent_id, Comment.content, literal_column("1", db.Integer).label('depth')).filter(Comment.parent_id == None).cte('included_parts', recursive=True)incl_alias = aliased(included_parts, name="pr")parts_alias = aliased(Comment, name="p")included_parts = included_parts.union_all(    db.session.query(        parts_alias.id,        parts_alias.parent_id,        parts_alias.content, incl_alias.c.depth+1).filter(parts_alias.parent_id == incl_alias.c.id))statement = select([    included_parts])print(statement)for m in db.session.execute(statement).fetchall():    print m

WITH RECURSIVE included_parts(id, parent_id, content, depth) AS (SELECT comment.id AS id, comment.parent_id AS parent_id, comment.content AS content, 1 AS depth   FROM comment   WHERE comment.parent_id IS NULL UNION ALL  SELECT p.id AS p_id, p.parent_id AS p_parent_id, p.content AS p_content, pr.depth + %(depth_1)s AS anon_1  FROM comment AS p, included_parts AS pr  WHERE p.parent_id = pr.id)SELECT included_parts.id, included_parts.parent_id, included_parts.content, included_parts.depth FROM included_parts

postgresql with 查询