Ordering infinite level hierarchical result set with MySQL

Question

I am working on infinite level comment-reply system. And getting parent-child relative data rows successfully but I am having trouble ordering this rows.

This being used for create results: https://stackoverflow.com/a/5291159/4894502

And I have results like this;

comment_id     comment      parent_id depth rel_path    rating
    38      Com 1               0       0      0           0
    39      Com 2               0       0      0           10
    40      Com 3               0       0      0           0
    41      Com 1-1             38      1      0/38        0
    42      Com 2-1             39      1      0/39        0
    44      Com 2-2             39      1      0/39        2
    46      Com 3-1             40      1      0/40        0
    43      Com 2-1-1           42      2      0/39/42     0
    47      Com 2-2-1           44      2      0/39/44     0
    45      Com 2-1-1-1         43      3      0/39/42/43  0

But ordering is problem, desired ordering is every child must be under of its parent ans children must be ordering in between according to some parameter for example rating. So desired result like this;

comment_id     comment      parent_id depth rel_path    rating
    38      Com 1               0       0      0           0
    41      Com 1-1             38      1      0/38        0
    39      Com 2               0       0      0           10
    42      Com 2-1             39      1      0/39        0
    43      Com 2-1-1           42      2      0/39/42     0
    45      Com 2-1-1-1         43      3      0/39/42/43  0
    44      Com 2-2             39      1      0/39        2
    47      Com 2-2-1           44      2      0/39/44     0
    40      Com 3               0       0      0           0
    46      Com 3-1             40      1      0/40        0

Or like this (+ order by rating)

comment_id     comment      parent_id depth rel_path    rating
    39      Com 2               0       0      0           10
    44      Com 2-2             39      1      0/39        2
    47      Com 2-2-1           44      2      0/39/44     0
    42      Com 2-1             39      1      0/39        0
    43      Com 2-1-1           42      2      0/39/42     0
    45      Com 2-1-1-1         43      3      0/39/42/43  0
    38      Com 1               0       0      0           0
    41      Com 1-1             38      1      0/38        0
    40      Com 3               0       0      0           0
    46      Com 3-1             40      1      0/40        0

Example on Db Fiddle https://www.db-fiddle.com/f/uk3ZDLdD8N5tvhzb9S6rXC/1

Example Table:

CREATE TABLE `comment` (
  `comment_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `depth` int(4) NOT NULL DEFAULT '0',
  `comment` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `rating` int(4) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

INSERT INTO `comment` (`comment_id`, `parent_id`, `depth`, `comment`, `rating`) VALUES
(42,39, 1,'Com 2-1', 0),
(41,38, 1,'Com 1-1', 0),
(40,0, 0,'Com 3', 0),
(39,0, 0,'Com 2', 20),
(38,0, 0,'Com 1', 0),
(43,42, 2,'Com 2-1-1', 0),
(44,39, 1,'Com 2-2', 2),
(45,43, 3,'Com 2-1-1-1', 0),
(46,40, 1,'Com 3-1', 0),
(47,44, 2,'Com 2-2-1', 0);

ALTER TABLE `comment`
  ADD PRIMARY KEY (`comment_id`),
  ADD UNIQUE KEY `comment_id` (`comment_id`);

ALTER TABLE `comment`
  MODIFY `comment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48;

Procedure (nearly same as answer in link above):

delimiter #

create procedure comment_hier
(
in param_parent_id smallint unsigned
)


BEGIN

DECLARE v_done TINYINT unsigned default 0;
DECLARE v_depth SMALLINT unsigned default 0;

CREATE TEMPORARY TABLE hier(
   comment_id int(11) unsigned, 
   parent_id int(11) unsigned,  
   depth int(99) unsigned default 0,
   relation_path varchar(360) default 0
)engine = memory;

INSERT INTO hier SELECT comment_id, parent_id, v_depth, 0 FROM comment a WHERE a.parent_id = param_parent_id;

CREATE TEMPORARY TABLE tmp engine=memory SELECT * FROM hier;

while not v_done do

    if exists( select 1 from hier h inner join comment a on h.comment_id = a.parent_id and h.depth = v_depth) then

    insert into hier 
    select a.comment_id, a.parent_id, v_depth + 1, CONCAT_WS('/', relation_path, a.parent_id) from comment a 
    inner join tmp t on a.parent_id = t.comment_id and t.depth = v_depth;

    set v_depth = v_depth + 1;          

    truncate table tmp;
    insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select  
    a.comment_id,
    a.comment as comment,
    a.parent_id as parent_id,
    h.depth,
    h.relation_path, 
    a.rating as rating 
from
    hier h
left join comment a on h.comment_id = a.comment_id
ORDER BY h.depth, a.comment_id;

DROP TEMPORARY TABLE if exists hier;
DROP TEMPORARY TABLE if exists tmp;

END #

Run:

delimiter ;

call comment_hier(0);

I've been working for hours but I couldn't solve this. Thanks.

You could re-order the results in your programming language. Which one do you use? — Adder, Jan 10 '19 at 11:46
I work with php but I would like to solve this problem with mysql if possible @Adder — hllktlhndd, Jan 10 '19 at 13:53
@Corion https://www.db-fiddle.com/f/uk3ZDLdD8N5tvhzb9S6rXC/1 I added code to here. Thanks for advice. I am working on your solution. — hllktlhndd, Jan 10 '19 at 13:56
@Corion root id I thought, but deeper levels cause errors, example in link Com 2-2-1 is not child of 2-1-1-1, its child of 2-2. But its position under 2-1-1-1. — hllktlhndd, Jan 10 '19 at 14:27
@Strawberry nested set model so complicated for this, must change so much data every new reply, isn't it? — hllktlhndd, Jan 10 '19 at 14:40
I've fixed my query - the creation of the hierarchical path needs the root id at the front — Corion, Jan 10 '19 at 14:52
@Corion thanks for your effort. I am tried code for deeper levels and alternative situation; parent child ordering seems like working thank you so much for this. But last think rating ordering not working. Here https://www.db-fiddle.com/f/aZJ34a8QPdZGa39G4d7BZz/9 child com 2-2 must be above com 2-1 because it has higher rating, do you have any advice for this? — hllktlhndd, Jan 10 '19 at 15:24
It seems daunting at first, but once you get to grips with it, it's very straightforward. Read Mike Hillyer's excellent article and decide for yourself. — Strawberry, Jan 10 '19 at 17:10
@Corion https://www.db-fiddle.com/f/aZJ34a8QPdZGa39G4d7BZz/24 If you look here, results as I want, thank you so much for your help. Code little bit messy but I am working on it. When I was generating relation_rating_path I used multipler, I'm not sure if it's right logic but Its working, if you have better idea change as you want. If you edit the answer to give these results, I mark it as answered. Thanks again. — hllktlhndd, Jan 10 '19 at 21:52
@hllktlhndd I'm not exactly sure what the logic is behind your ordering. Using a multiplier is a good idea to make all the relation path items the same width. — Corion, Jan 11 '19 at 06:58

Corion · Accepted Answer · 2019-01-10T15:48:17.323

To achieve the different orderings (by rating of the top-level comment, by hierarchy only), you will need to add all the sorting criteria to your hier table as new hierarchical paths. I restructured your hierarchical sort path to include both the path and the final comment id, so that each initial comment does not have 0 but 39 (or 38 or 40) as its first component. This makes the hierarchical ordering trivial.

For ordering by rating, you have to construct each path element of rating/comment_id. That makes it trivial to order things by (relative rating in the thread) and then by comment_id.

The resulting code is

Schema (MySQL v5.7)

CREATE TABLE `comment` (
  `comment_id` int(11) NOT NULL,
  `parent_id` int(11) NOT NULL DEFAULT '0',
  `depth` int(4) NOT NULL DEFAULT '0',
  `comment` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `rating` int(4) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

INSERT INTO `comment` (`comment_id`, `parent_id`, `depth`, `comment`, `rating`) VALUES
(42,39, 1,'Com 2-1', 0),
(41,38, 1,'Com 1-1', 0),
(40,0, 0,'Com 3', 0),
(39,0, 0,'Com 2', 20),
(38,0, 0,'Com 1', 0),
(43,42, 2,'Com 2-1-1', 0),
(44,39, 1,'Com 2-2', 2),
(45,43, 3,'Com 2-1-1-1', 0),
(46,40, 1,'Com 3-1', 0),
(47,44, 2,'Com 2-2-1', 0);

ALTER TABLE `comment`
  ADD PRIMARY KEY (`comment_id`),
  ADD UNIQUE KEY `comment_id` (`comment_id`);

ALTER TABLE `comment`
  MODIFY `comment_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=48;

#CREATING PROCEDURE
#---------------------------------------------------------------------#
drop procedure if exists comment_hier;

delimiter $$

create procedure comment_hier
(
in param_parent_id smallint unsigned
)

BEGIN

DECLARE v_done TINYINT unsigned default 0;
DECLARE v_depth SMALLINT unsigned default 0;

CREATE TEMPORARY TABLE hier(
   comment_id int(11) unsigned, 
   root_id int(11) unsigned,  
   parent_id int(11) unsigned,  
   depth int(99) unsigned default 0,
   relation_path varchar(360) default 0,
   relation_rating_path varchar(360) default 0
)engine = memory;

INSERT INTO hier SELECT comment_id, comment_id as root_id, parent_id, v_depth, comment_id, concat_ws('/',  a.rating, comment_id) FROM comment a WHERE a.parent_id = param_parent_id;

CREATE TEMPORARY TABLE tmp engine=memory SELECT * FROM hier;

while not v_done do

    if exists( select 1 from hier h inner join comment a on h.comment_id = a.parent_id and h.depth = v_depth) then

    insert into hier 
    select a.comment_id, t.root_id, a.parent_id, v_depth + 1, CONCAT_WS('/', relation_path, a.comment_id), CONCAT_WS('/', relation_rating_path,  a.rating, a.comment_id) from comment a 
    inner join tmp t on a.parent_id = t.comment_id and t.depth = v_depth;

    set v_depth = v_depth + 1;          

    truncate table tmp;
    insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

#SELECT STATEMENT
#----------------------------------------------------------------------------------#
select  
    a.comment_id,
    a.comment as comment,
    a.parent_id as parent_id,
    h.depth,
    h.relation_path
    , h.relation_rating_path
    , a.rating as rating
    , h.root_id
from
    hier h
left join comment a on h.comment_id = a.comment_id
-- for tree-like ordering
-- order by relation_path
-- for rating ordering of the comment
order by relation_rating_path desc, comment_id
    ;

DROP TEMPORARY TABLE if exists hier;
DROP TEMPORARY TABLE if exists tmp;

END $$

delimiter ;

Query #1

call comment_hier(0);

| comment_id | comment     | parent_id | rating | depth | relation_path | relation_rating_path | root_id |
| ---------- | ----------- | --------- | ------ | ----- | ------------- | -------------------- | ------- |
| 47         | Com 2-2-1   | 44        | 0      | 2     | 39/44/47      | 20/39/2/44/0/47      | 39      |
| 44         | Com 2-2     | 39        | 2      | 1     | 39/44         | 20/39/2/44           | 39      |
| 45         | Com 2-1-1-1 | 43        | 0      | 3     | 39/42/43/45   | 20/39/0/42/0/43/0/45 | 39      |
| 43         | Com 2-1-1   | 42        | 0      | 2     | 39/42/43      | 20/39/0/42/0/43      | 39      |
| 42         | Com 2-1     | 39        | 0      | 1     | 39/42         | 20/39/0/42           | 39      |
| 39         | Com 2       | 0         | 20     | 0     | 39            | 20/39                | 39      |
| 46         | Com 3-1     | 40        | 0      | 1     | 40/46         | 0/40/0/46            | 40      |
| 40         | Com 3       | 0         | 0      | 0     | 40            | 0/40                 | 40      |
| 41         | Com 1-1     | 38        | 0      | 1     | 38/41         | 0/38/0/41            | 38      |
| 38         | Com 1       | 0         | 0      | 0     | 38            | 0/38                 | 38      |

View on DB Fiddle

Ordering infinite level hierarchical result set with MySQL

1 Answers1