Sort LTree by Upvotes

Question

I am writing a comment system. I have been able to get the hierarchy following the tutorial here (http://www.postgresonline.com/journal/archives/173-Using-LTree-to-Represent-and-Query-Hierarchy-and-Tree-Structures.html)

    SELECT n.node_path          AS PATH,
           n.note_id            AS _1,
           n.note_no            AS _2,
           n.public_key         AS _3,
           n.upvotes            AS _4
    FROM public.comment_table AS n
             INNER JOIN public.comment_table AS a ON (a.node_path @> n.node_path)
    GROUP BY _1, PATH
    ORDER BY PATH

However, I have having trouble order the row by upvote. I cannot simply ORDER BY PATH, n.upvotes here because replies on the same thread will have the different node paths. node_paths are calculated using public_key.

From example, given

a (0 upvotes)
-> b (0 upvotes)
-> c (1 upvote)
d (1 upvote)
-> e (0 upvotes)

The node path for b an c will be a.b and a.c respectively. I cannot simply substract b and c from the node path and ORDER BY them. If I do so, it will result in this order:

a
d
-> b
-> c
-> e

This make sense because if you remove each row's public_key from the node_path, it will simply sort by shortest node_path to longest.

How do I write a query that will result in the correct hierarchy and sorted by upvotes like so:

d (1)
-> e (0)
a (0)
-> c (1)
-> b (0)

Answer 1

Assuming that upvote counts are constantly changing and that you went the ltree route to avoid recursion, I cannot think of a solution to this that does not ironically require recursion since each row needs to have access to the upvote counts of its ancestors to find where to place itself in the results.

with recursive base as (
  select node_path, upvotes, 
         array[row_number() over (order by upvotes desc, node_path)] as sort_path
    from comment_table 
   where nlevel(node_path) = 1
  union all
  select c.node_path, c.upvotes, 
         p.sort_path||row_number() over (order by c.upvotes desc, c.node_path)
    from base p
    join comment_table c 
      on subpath(c.node_path, 0, -1) = p.node_path
)
select * from base order by sort_path;

 node_path | upvotes | sort_path 
-----------+---------+-----------
 d         |       1 | {1}
 d.e       |       0 | {1,3}
 a         |       0 | {2}
 a.c       |       1 | {2,1}
 a.b       |       0 | {2,2}
(5 rows)