每个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