Допустим есть таблица, содержащая древовидную структуру
(имеется поле parent_id, ссылающееся на id родительской строки).
Также имеется поле "время".
Как отсортировать таблицу по времени таким образом, чтобы все поддеревья отображались упорядоченными по времени, а дерево в целом — по алгоритму обхода в глубину?
База Sqlite. Применение этого: есть что-то вроде древовидного форума (чем-то похожего на рсдн, но по факту там не более 2 уровней вложенности), и хочется его отобразить на веб-странице. Все ответы на каждое сообщение должны быть упорядочены по времени, но глобальное упорядочение не годится — кто-то может через год ответить на старое сообщение, и ответ должен быть последним в данной ветке, но не последним вообще. Кстати, упорядочивать сами корневые сообщения, чтобы они "всплывали" при добавлении ответа, не нужно (хотя если подскажете и это — тоже будет интересно, на всякий случай пригодится).

ИИ выдал мне некое решение:

To sort a SQLite table that represents a tree structure by time, so that all subtrees appear ordered by time and the tree as a whole appears ordered by depth-first traversal, you can use a recursive common table expression (CTE). A CTE allows you to define a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement.

Here is a step-by-step guide:

Define a recursive CTE that traverses the tree in a depth-first manner. The base case of the recursion is the root of the tree (where parent_id is NULL). The recursive case is all the children of a node, identified by the id of the parent. You can join this CTE with the original table to sort the children by time.

WITH RECURSIVE tree AS (
 -- Base case: select the root node(s)
 SELECT id, parent_id, time
 FROM my_table
 WHERE parent_id IS NULL
 UNION ALL
 -- Recursive case: select the children of the current node
 SELECT t.id, t.parent_id, t.time
 FROM my_table t
 JOIN tree r ON t.parent_id = r.id
)

After defining the CTE, you can use it in a SELECT statement to get the desired result. You can ORDER BY the time column to sort the children of each node by time. Since the CTE is defined in a depth-first manner, the entire tree will be sorted in depth-first order.

SELECT * FROM tree ORDER BY time;

Remember that SQLite treats NULLs as the smallest possible values. If your time column contains NULLs, they will appear at the beginning of the result set when you use ASC or at the end when you use DESC. If you want to handle NULLs differently, you can use the NULLS FIRST or NULLS LAST options in the ORDER BY clause

Проблема в том, что я вообще первый раз вижу такую конструкцию. Я не понимаю как она работает, не понимаю ее быстродействие и т.п.

Альтернативный вариант — хранить корневые сообщения в одной таблице, а ответы на них — в другой (пока всего 2 уровня вложенности). И тупо склеивать выборки из этих таблиц. Это тупо, пропадает красота, лаконичность и унификация, разрастается и усложняется разнообразный серверный код обработки этих запросов. Например при поиске нужно искать уже по двум таблицам а не по одной, и множество вот таких неприятных мелочей. Жестко прибивается ограничение на количество уровней вложенности.

Что посоветуете? Насколько вот эти "CTE" распространены, надежны, стоит ли с ними связываться?