Postgresql – How to ORDER BY timestamp in this recursive query

order-bypostgresqlrecursive

I have a working reqursive query for my comments table. 

WITH RECURSIVE comments_tree(
  id,
  content,
  created_at,
  path,
  level
) AS (
  SELECT
    id,
    content,
    created_at,
    ARRAY[id],
    1 as level
  FROM comments
  WHERE
    parent_id IS NULL
UNION ALL
  SELECT
    comments.id,
    comments.content,
    comments.created_at,
    path || comments.id,
    comments_tree.level + 1 as level
  FROM comments_tree
  JOIN comments ON comments_tree.id = comments.parent_id
  WHERE NOT comments.id = ANY(path)
)
SELECT * FROM comments_tree ORDER BY path;

I have added 5 comments (2 of them have content 'parent x', others – 'child x').

Example of output (formatted to be more readable): 

"-> parent 1: 16:49"
"--> child 1: 16:50"
"--> child 2: 16:51"
"--> child 3: 16:52"
"-> parent 2: 16:53"

I want to order comments by their creation time (created_at field) like this: 

"-> parent 2: 16:53"
"-> parent 1: 16:49"
"--> child 3: 16:52"
"--> child 2: 16:51"
"--> child 1: 16:50"

How do I do that?

Best Answer

For simplicity I omit additional columns to focus on core of the problem.

Simple solution

Collect timestamps (created_at) in another array (path_created) like:

WITH RECURSIVE tree AS (
   SELECT id, created_at, ARRAY[id] AS path
        , ARRAY[created_at] AS path_created
   FROM   comments
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.created_at, path || c.id
        , t.path_created || c.created_at
   FROM   tree     t
   JOIN   comments c ON t.id = c.parent_id
   WHERE  NOT c.id = ANY(t.path)
   )
SELECT *
FROM   tree
ORDER  BY path_created[1] DESC, path[1] ASC
        , path_created[2] DESC, path[2] ASC
        , path_created[3] DESC, path[3] ASC
        , ...

If two threads start at the same created_at, sort the smaller id first.

... which is good enough for a low maximum number of elements. Works if there are fewer elements in the array. It's neither very dynamic nor very elegant for an unknown number of levels, though.

Dynamic number of levels

You could concatenate or calculate a single value from created_at and id or use a document type like jsonb. All of these solutions have corner cases. If you put some smarts into it, it'll be unambiguous.

The general, clean solution is to compute the row number for each row per level with row_number(), ordered by any number of columns or expressions directly:

WITH RECURSIVE tree AS (
   SELECT id, created_at, ARRAY[id] AS path
        , ARRAY[row_number() OVER (ORDER BY created_at DESC, id ASC)] AS path_sort
   FROM   comments
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.created_at, t.path || c.id
        , t.path_sort || row_number() OVER (ORDER BY c.created_at DESC, c.id ASC)
   FROM   tree t
   JOIN   comments c ON t.id = c.parent_id
   WHERE  NOT c.id = ANY(path)
   )
SELECT *
FROM   tree
ORDER  BY path_sort;

dbfiddle here

row_number() breaks ties among peers in any case. In the example the added id to the ORDER BY clause of the window definition. Assuming it is the PK and therefore breaks ties in deterministic fashion.

Related Question