A common table expression (CTE) is a temporary, named result from an SQL query that can be used inside a larger enclosing query. A recursive CTE extends this idea to traverse tree-shaped data, such as posts with nested comments. section 11.7.2
Building a comment hierarchy
The MyBlog application stores posts and comments in one self-referential Post table. The _build_posts_hierarchy() function filters for the requested root post and root-level records, optionally restricts results to active posts, then constructs a recursive SQLAlchemy CTE named hierarchy. The display handler delegates this database work to the helper, keeping page rendering focused on forms, errors, and templates. section 11.7.2
def _build_posts_hierarchy(db_session, post_uid):
# build the list of filters here to use in the CTE
filters = [ #1
Post.post_uid == post_uid, #1
Post.parent_uid == None #1
] #1
if current_user.is_anonymous or #2
current_user.can_view_posts(): #2
filters.append(Post.active == True) #2
# build the recursive CTE query
hierarchy = ( #3
db_session #3
.query(Post, Post.sort_key.label( #3
"sorting_key")) #3
.filter(*filters) #3
.cte(name=‘hierarchy’, recursive=True) #3
) #3
children = aliased(Post, name="c") #4
hierarchy = hierarchy.union_all( #5
db_session #5
.query( #5
children, #5
( #5
func.cast(hierarchy.c.sorting_key, #5
String) + #5
" " + #5
func.cast(children.sort_key, #5
String) #5
).label("sorting_key") #5
) # #5
.filter(children.parent_uid == #5
hierarchy.c.post_uid) #5
)
# query the hierarchy for the post
and it’s comments
return ( #6
db_session #6
.query(Post, func.cast( #6
hierarchy.c.sorting_key, String)) #6
.select_entity_from(hierarchy) #6
.order_by(hierarchy.c.sorting_key) #6
.all() #6
) #6
Inspect the SQLAlchemy query that assembles filters and begins the recursive hierarchy CTE. section 11.7.2
