How to sort tree comments by root comment rating?

Question

PostgreSQL

The usual implementation of a nested set of comments is stored in the database like this:

+---------+-----------+---------+---------+-------+--------+
| id      | parent_id | lft     | rgt     | depth | rating |
+---------+-----------+---------+---------+-------+--------+
| 4073406 |           | 1058655 | 1058656 | 0     | 0      |
| 3721850 |           | 1058651 | 1058654 | 0     | 2      |
| 4279470 | 3721850   | 1058652 | 1058653 | 1     | 0      |
| 3682985 |           | 1058649 | 1058650 | 0     | 1      |
| 3643602 |           | 1058647 | 1058648 | 0     | 0      |
| 3182010 |           | 1058643 | 1058646 | 0     | 3      |
+---------+-----------+---------+---------+-------+--------+

On some sites (for example, on vc.ru ) comments can be sorted by rating, and it is sorted by the rating of the first comment in the thread, and inside the thread it is usually sorted by rgt DESC
How is this implemented?

Answer 1

Alternatively, you can create a root_rating field and store either the rating of the thread's main comment or the sum of the ratings of the comments in the thread. Then the sorting will be simple
But with each vote, you will have to overwrite the root_rating field of the entire branch.
I really do not want to do this, maybe someone will offer a more correct way.
The correct way is to use with recursive. Example of a successful request:

WITH RECURSIVE r AS (
    SELECT id, text, depth, lft, rgt AS _rgt, cached_votes_score AS rating
        FROM comments AS c1
        WHERE depth = 0

    UNION ALL 
    
    SELECT c2.id, c2.text, c2.depth, c2.lft, c2.rgt, rating AS rating
        FROM comments c2
        JOIN r ON c2.parent_id = r.id
)
SELECT * FROM r ORDER BY rating desc, _rgt DESC;