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)
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)