hllktlhndd
Reputation: 303
Ordering infinite level hierarchical result set with MySQL
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.
Upvotes: 1
Views: 194
Answers (1)
Corion
Reputation: 3925
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 |
Upvotes: 3
Related Questions
- Mysql sorting Hierarchical data
- MySQL Order By Hierarchy
- MySQL select parent followed by its children and ordered by
- Implementing Nested Order Set in MySQL/PHP
- Order by on recursive table
- Infinite Sub Category Ordering in MySQL
- order sql tree hierarchy
- MySQL Tree ordered by parent and child
- mySQL query that Sorts by hierarchy while using foreign keys
- MySQL Closure Table hierarchical database - How to pull information out in the correct order