CTEs for Hierarchical Data / Threaded Comments

DDL.sql

-- sqlite3 flavored SQL

CREATE TABLE comment (
  id INTEGER PRIMARY KEY ASC,
  parent_id INTEGER,
  content TEXT NOT NULL,
  create_time TEXT NOT NULL,
  is_deleted INTEGER DEFAULT 0,
  
  FOREIGN KEY(parent_id) references comment(id)
);

query.sql

-- sqlite3
--
-- retrieves all comments recursively in depth-first order, up to the provided max_depth.
-- parameters:
--   parent_id: ID of parent comment
--   max_depth: maximum depth of comments to retrieve
--
-- see here for more info on how it works: https://www.postgresql.org/docs/9.1/queries-with.html

-- think of 'tree' as a temporary table with two columns, parent and level.
WITH RECURSIVE tree(parent, level) AS 
(
  -- base case: the initial contents of the tree 'table'
  VALUES(:parent_id, 0) 
    UNION 
  -- recursive case: 'RECURSIVE' keyword means the below SELECT is re-evaluated to add rows to tree until this 
  --                  SELECT returns an empty result set.
  SELECT id, tree.level + 1 FROM comment, tree 
    WHERE comment.parent_id = tree.parent AND tree.level < :max_depth
    ORDER BY 2 desc 
) 
SELECT {} FROM tree, comment 
WHERE tree.parent = comment.id ORDER BY tree.level;