1

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?

3
  • (1) Aren't your examples exchanged? The first one is the one ordered by creation time. (2) Have you just tried changing the last line of your query: ORDER BY created_at ?
    – joanolo
    Apr 17 '17 at 7:59
  • Yes, I have. Nothing was changed.
    – blackst0ne
    Apr 17 '17 at 11:25
  • Basic requirement for this kind of questions: table definition showing relevant columns with data types and all constraints (CREATE TABLE statements). Most importantly: is created_at UNIQUE NOT NULL? Else it gets more complex. And your version of Postgres. Also: Are you trying to solve this for only 2 levels or for any number of levels? Sort by root timestamp of level, then by timestamp of each parent level recursively? Please edit to clarify. Apr 17 '17 at 22:51
2

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.

1
  • Thank you for the idea! I changed your code a bit. Everythin works like a charm!
    – blackst0ne
    Apr 27 '17 at 6:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.