Postgres WITH RECURSIVE CTE: sorting/ordering children by popularity while retaining tree structure (parents always above children)

Question

I'm building a forum, very much like Reddit/Slashdot, i.e.

• Unlimited reply nesting levels
• Popular comments (ordered by likes/votes) will rise to the top (within their own nesting/depth level), but the tree structure needs to be retained (parent is always shown directly above children)

Here's a sample table & data:

DROP TABLE IF EXISTS "comments";
CREATE TABLE comments (
  id BIGINT PRIMARY KEY,
  parent_id BIGINT,
  body TEXT NOT NULL,
  like_score BIGINT,
  depth BIGINT
);

INSERT INTO comments VALUES (  0, NULL, 'Main top of thread post', 5 , 0 );

INSERT INTO comments VALUES (  1, 0, 'comment A', 5 , 1 );
INSERT INTO comments VALUES (  2, 1,    'comment A.A', 3, 2 );
INSERT INTO comments VALUES (  3, 1,    'comment A.B', 1, 2 );
INSERT INTO comments VALUES (  9, 3,    'comment A.B.A', 10, 3 );
INSERT INTO comments VALUES ( 10, 3,    'comment A.B.B', 5, 3 );
INSERT INTO comments VALUES ( 11, 3,    'comment A.B.C', 8, 3 );
INSERT INTO comments VALUES (  4, 1,    'comment A.C', 5, 2 );

INSERT INTO comments VALUES ( 5, 0, 'comment B', 10, 1 );
INSERT INTO comments VALUES ( 6, 5, 'comment B.A', 7, 2 );
INSERT INTO comments VALUES ( 7, 5, 'comment B.B', 5, 2 );
INSERT INTO comments VALUES ( 8, 5, 'comment B.C', 2, 2 );

Here's the recursive query I've come up with so far, but I can't figure out how to order children, but retain tree structure (parent should always be above children)...

WITH RECURSIVE tree AS (
  SELECT
    ARRAY[]::BIGINT[] AS sortable,
    id,
    body,
    like_score,
    depth
  FROM "comments"
  WHERE parent_id IS NULL

  UNION ALL

  SELECT
    tree.sortable ||  "comments".like_score || "comments".id,
    "comments".id,
    "comments".body,
    "comments".like_score,
    "comments".depth
  FROM "comments", tree
  WHERE "comments".parent_id = tree.id
)
SELECT * FROM tree
ORDER BY sortable DESC

This outputs...

+----------------------------------------------------------+
|sortable      |id|body                   |like_score|depth|
+----------------------------------------------------------+
|{10,5,7,6}    |6 |comment B.A            |7         |2    |
|{10,5,5,7}    |7 |comment B.B            |5         |2    |
|{10,5,2,8}    |8 |comment B.C            |2         |2    |
|{10,5}        |5 |comment B              |10        |1    |
|{5,1,5,4}     |4 |comment A.C            |5         |2    |
|{5,1,3,2}     |2 |comment A.A            |3         |2    |
|{5,1,1,3,10,9}|9 |comment A.B.A          |10        |3    |
|{5,1,1,3,8,11}|11|comment A.B.C          |8         |3    |
|{5,1,1,3,5,10}|10|comment A.B.B          |5         |3    |
|{5,1,1,3}     |3 |comment A.B            |1         |2    |
|{5,1}         |1 |comment A              |5         |1    |
|              |0 |Main top of thread post|5         |0    |
+----------------------------------------------------------+

...however notice that "comment B", "comment A" and "Main top of thread post" are below their children? How do I keep the contextual order? i.e. The output I want is:

+----------------------------------------------------------+
|sortable      |id|body                   |like_score|depth|
+----------------------------------------------------------+
|              |0 |Main top of thread post|5         |0    |
|{10,5}        |5 |comment B              |10        |1    |
|{10,5,7,6}    |6 |comment B.A            |7         |2    |
|{10,5,5,7}    |7 |comment B.B            |5         |2    |
|{10,5,2,8}    |8 |comment B.C            |2         |2    |
|{5,1}         |1 |comment A              |5         |1    |
|{5,1,5,4}     |4 |comment A.C            |5         |2    |
|{5,1,3,2}     |2 |comment A.A            |3         |2    |
|{5,1,1,3}     |3 |comment A.B            |1         |2    |
|{5,1,1,3,10,9}|9 |comment A.B.A          |10        |3    |
|{5,1,1,3,8,11}|11|comment A.B.C          |8         |3    |
|{5,1,1,3,5,10}|10|comment A.B.B          |5         |3    |
+----------------------------------------------------------+

I actually want the users to be able to sort by a number of methods:

• Most popular first
• Least popular first
• Newest first
• Oldest first
• etc

...but in all cases the parents need to be shown above their children. But I'm just using "like_score" here as the example, and I should be able to figure out the rest from there.

Spent a many hours researching the web and trying things myself, and feels like I'm getting close, but can't figure out this last part.

Answer 1

1.

tree.sortable ||  -"comments".like_score || "comments".id
                  ^
                 /|\
                  |
                  |  

---

2.

ORDER BY sortable  

---

WITH RECURSIVE tree AS (
  SELECT
    ARRAY[]::BIGINT[] AS sortable,
    id,
    body,
    like_score,
    depth
  FROM "comments"
  WHERE parent_id IS NULL

  UNION ALL

  SELECT
    tree.sortable ||  -"comments".like_score || "comments".id,
    "comments".id,
    "comments".body,
    "comments".like_score,
    "comments".depth
  FROM "comments", tree
  WHERE "comments".parent_id = tree.id
)
SELECT * FROM tree
ORDER BY sortable 

---

+-------------------+----+-------------------------+------------+-------+
| sortable          | id | body                    | like_score | depth |
+-------------------+----+-------------------------+------------+-------+
| (null)            | 0  | Main top of thread post | 5          | 0     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5}           | 5  | comment B               | 10         | 1     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5,-7,6}      | 6  | comment B.A             | 7          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5,-5,7}      | 7  | comment B.B             | 5          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-10,5,-2,8}      | 8  | comment B.C             | 2          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1}            | 1  | comment A               | 5          | 1     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-5,4}       | 4  | comment A.C             | 5          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-3,2}       | 2  | comment A.A             | 3          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3}       | 3  | comment A.B             | 1          | 2     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3,-10,9} | 9  | comment A.B.A           | 10         | 3     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3,-8,11} | 11 | comment A.B.C           | 8          | 3     |
+-------------------+----+-------------------------+------------+-------+
| {-5,1,-1,3,-5,10} | 10 | comment A.B.B           | 5          | 3     |
+-------------------+----+-------------------------+------------+-------+