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 查询