title: Postgres WITH RECURSIVE CTE: sorting/ordering children by popularity while retaining tree structure (parents always above children)
body: <p>I'm building a forum, very much like Reddit/Slashdot, i.e.</p>
  
  <ul>
  <li>Unlimited reply nesting levels</li>
  <li>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)</li>
  </ul>
  
  <p>Here's a sample table &amp; data:</p>
  
  <pre><code>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 );
  </code></pre>
  
  <p>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)...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This outputs...</p>
  
  <pre><code>+----------------------------------------------------------+
  |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    |
  +----------------------------------------------------------+
  </code></pre>
  
  <p>...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:</p>
  
  <pre><code>+----------------------------------------------------------+
  |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    |
  +----------------------------------------------------------+
  </code></pre>
  
  <p>I actually want the users to be able to sort by a number of methods:</p>
  
  <ul>
  <li>Most popular first </li>
  <li>Least popular first </li>
  <li>Newest first </li>
  <li>Oldest first </li>
  <li>etc</li>
  </ul>
  
  <p>...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.</p>
  
  <p>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.  </p>