Как сортировать древовидные комментарии по рейтингу рутового комментария?

Question

[IgorVol]

Обычная реализация nested set комментариев, хранится в БД вот так:

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

На некоторых сайтах (например, на vc.ru) комментарии можно сортировать по рейтингу, причем сортируется по рейтингу именно первого комментария в ветке, а внутри ветки сортируется обычно rgt DESC

Как такое реализовывают?

Answer 1

[IgorVol]

Как вариант, можно создать поле root_rating и хранить там либо рейтинг главного комментария ветки, либо сумму рейтинга комментариев в ветке. Тогда сортировка будет простая

order by comments.root_rating desc, comments.rgt desc

Но при каждом голосовании придется перезаписывать поле root_rating всей ветки.
Очень не хочется так делать, может кто-то предложит более правильный способ.

ПРАВИЛЬНОЕ РЕШЕНИЕ

Правильный способ - использовать with recursive. Пример успешного запроса:

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;