Упорядочить в таблице самоссылки

Question

У меня есть таблица комментариев (к такой статье, как эта):

---------------------------------------------------------------
id  |comment_id     |likes_count      |add_time
---------------------------------------------------------------
0      NULL            0                time0
1      NULL            2                time1
2      0               0                time2
3      0               0                time3
4      NULL            1                time4
5      1               0                time5

comment_id ссылается на id в той же таблице. когда comment_id NULL, это означает, что этот комментарий является родительским (это не ответ на другой комментарий, а скорее на саму статью), но если он не нулевой, это означает, что этот комментарий является дочерним (это ответ на другой комментарий в статье).

Запрос, который я использую, упорядочивает результаты таким образом, чтобы каждый дочерний элемент отображался под его родителем.

SELECT *
FROM comments
ORDER BY
    COALESCE (comment_id, id) DESC, 
    (comment_id IS NULL) DESC, 
    likes_count DESC

Но я не могу понять, как сделать родительские комментарии (когда их дети следуют) с наибольшим likes_count первым в результатах, likes_count DESC кажется, не имеет никакого эффекта, так как результаты всегда упорядочены id,

Результат, возвращаемый запросом:

---------------------------------------------------------------
id  |comment_id     |likes_count      |add_time
---------------------------------------------------------------
0      NULL            0                time0
2      0               0                time2
3      0               0                time3
1      NULL            2                time1
5      1               0                time5
4      NULL            1                time4

Ожидаемый результат:

---------------------------------------------------------------
id  |comment_id     |likes_count      |add_time
---------------------------------------------------------------
1      NULL            2                time1
5      1               0                time5
4      NULL            1                time4
0      NULL            0                time0   
2      0               0                time2
3      0               0                time3

phpMyAdmin показывает следующую информацию:

Тип сервера: MariaDB

Версия сервера: 10.1.40-MariaDB-cll-lve - MariaDB

Версия протокола сервера: 10

cpsrvd 11.78.0.34

Версия клиента базы данных:libmysql - 5.1.73

Версия PHP: 7.2.7

phpMyAdmin: 4.8.3

Answer 1

Вы можете попробовать добавить уровень сортировки, используя максимальное количество лайков для каждой родительской дочерней группы:

SELECT *
FROM comments
ORDER BY
    MAX(likes_count) OVER (PARTITION BY COALESCE(comment_id, id)) DESC,
    COALESCE (comment_id, id) DESC, 
    comment_id IS NULL DESC;

Если ваша база данных (что бы это ни было на самом деле) не поддерживает аналитические функции, то мы можем сделать ту же логику с объединением:

SELECT c1.*
FROM comments c1
INNER JOIN comments c2
    ON COALESCE(c1.comment_id, c1.id) = c2.id
ORDER BY
    c2.likes_count DESC,
    COALESCE (c1.comment_id, c1.id) DESC, 
    c1.comment_id IS NULL DESC;