Order by in a self referencing table

Question

I have a table of comments (to some article like this):

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

The comment_id is referencing to the id in the same table. When comment_id is NULL, this means that this comment is parent (it is not a response to another comment, but rather to the article itself), but if it is not null, it means that this comment is a child (it is a response to another comment in the article).

The query I am using orders the results in a manner where each child is displayed under its parent.

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

But I can't figure out how to make parent comments (with their children following it) with the most likes_count appear first in the results, the likes_count DESC seems to have no effect, as results are always ordered by the id.

Result returned by the query:

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

Expected result:

---------------------------------------------------------------
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 show the following info:

Server type: MariaDB

Server version: 10.1.40-MariaDB-cll-lve - MariaDB

Server Protocol version: 10

cpsrvd 11.78.0.34

Database client version:libmysql - 5.1.73

PHP version: 7.2.7

phpMyAdmin: 4.8.3

This is a recursive design. A comment can have comments, which can have comments, … In your example there is just parent and child, no grandchild. Can child comments have comments in your database or is this forbidden? — Thorsten Kettner, Jul 21 '19 at 10:08
@ThorstenKettner only children are allowed, can have more nesting. — hiddeneyes02, Jul 21 '19 at 10:10
I don't understand this answer. "only children are allowed" seems to imply that a child cannot have children. "can have more nesting" seems to imply the opposite. — Thorsten Kettner, Jul 21 '19 at 10:15
Can child comments have likes? Must we add them to their parent comments like? If so, should we show the cumulated count in the results? Or only use them silently in the order by clause? — Thorsten Kettner, Jul 21 '19 at 10:24
@ThorstenKettner for now, child comments can't have likes, but I may consider this later. Anyway, the `likes_count` should not be considered when ordering children, because in order to make the comments look more concise, they should be ordered by `add_time`, nothing else. — hiddeneyes02, Jul 21 '19 at 10:30

Tim Biegeleisen · Accepted Answer · 2019-07-21T10:15:43.697

2

You could try adding a sort level using the max likes count over each parent child group:

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;

If your database (whatever that actually is) does not support analytic functions, then we can do the same logic with a join:

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;

edited Jul 21 '19 at 10:15

answered Jul 21 '19 at 09:36

Tim Biegeleisen

502,043
27
286
360

It is giving me syntax error `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OVER (PARTITION BY comment_id) DESC, COALESCE (comment_id, id) DESC, ' at line 4` – hiddeneyes02 Jul 21 '19 at 10:01
@hiddeneyes02 You are not running the code in my answer, you're running something else :-) – Tim Biegeleisen Jul 21 '19 at 10:04
What is your MariaDB version? I thought you tagged the question Oracle? – Tim Biegeleisen Jul 21 '19 at 10:07
`10.1.40-MariaDB-cll-lve - MariaDB Server` with `libmysql - 5.1.73` – hiddeneyes02 Jul 21 '19 at 10:09
Answer updated assuming you don't have analytic functions. – Tim Biegeleisen Jul 21 '19 at 10:16
That working now :) Now I have to adapt this to my real more complicated query :( – hiddeneyes02 Jul 21 '19 at 10:21
@hiddeneyes02: Can you upgrade? Window functions where introduced in MariaDB 10.2. They make some queries much easier to write. – Thorsten Kettner Jul 21 '19 at 10:26
@TimBiegeleisen I am on a shared host, I couldn't update it. – hiddeneyes02 Jul 21 '19 at 12:56

Order by in a self referencing table

1 Answers1