Paginating hierarchical data using closure tables (MySQL)

Question

I am trying to query paged hierarchical comments sorted by score. score is an integer, and the comments table has a self-referential parent_id column.

Each page should have at least one root comment followed by its children. If only one root comment is in the dataset, than only one page will be returned.

So, given the following data in the comments table:

+----+-------+-----------+
| id | score | parent_id |
+----+-------+-----------+
|  1 |    10 |      NULL |
|  2 |     5 |      NULL |
|  3 |     0 |         1 |
|  4 |     6 |         2 |
|  5 |     0 |      NULL |
|  6 |    30 |         1 |
|  7 |     1 |         3 |
|  8 |     0 |         4 |
|  9 |    50 |      NULL |
| 10 |     2 |         2 |
+----+-------+-----------+

I'd like to be able to SELECT * FROM comments...LIMIT 4 OFFSET 0 and have Page 1 be:

+----+-------+-----------+
| id | score | parent_id |
+----+-------+-----------+
|  9 |    50 |      NULL |
|  1 |    10 |      NULL |
|  6 |    30 |         1 |
|  3 |     0 |         1 |
+----+-------+-----------+

And Page 2 be:

+----+-------+-----------+
| id | score | parent_id |
+----+-------+-----------+
|  2 |     5 |      NULL |
|  4 |     6 |         2 |
| 10 |     2 |         2 |
|  5 |     0 |      NULL |
+----+-------+-----------+

And Page 3 be blank because no root comments remain.

I'm using a supporting closure table, as described by Bill Karwin, because comment subtrees can be viewed independently using any comment as the root comment, and this seems like the best solution for that.

The structure and sample data for the table in question is the following:

CREATE TABLE `comments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `parent_id` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `comments` (`id`, `score`, `parent_id`)
VALUES
  (1,10,NULL),
  (2,5,NULL),
  (3,0,1),
  (4,6,2),
  (5,0,NULL),
  (6,30,1),
  (7,1,3),
  (8,0,4),
  (9,50,NULL),
  (10,2,2);

CREATE TABLE `comments_closure` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ancestor` int(11) unsigned NOT NULL,
  `descendant` int(11) unsigned NOT NULL,
  `depth` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `comments_closure` (`id`, `ancestor`, `descendant`, `depth`)
VALUES
  (1,1,0), (1,3,1), (1,6,1), (1,7,2),
  (2,2,0), (2,4,1), (2,10,1), (2,8,2),
  (3,3,0), (3,7,1),
  (4,4,0), (4,8,1),
  (5,5,0),
  (6,6,0),
  (7,7,0),
  (8,8,0),
  (9,9,0),
  (10,10,0);

Charles Esau · Answer 1 · 2017-03-17T21:23:48.903

This should work for a 1-level deep query:

SELECT @id_multiplier := MAX(POW(10, -(length(id) + 1))) FROM comments;
SELECT @score_multiplier := @id_multiplier * MAX(POW(10, -(length(score) + 1))) FROM comments;
SELECT c1.id
     , c1.score
     , c1.parent_id 
FROM comments c1
LEFT JOIN comments c2 
   ON c1.parent_id = c2.id
WHERE c1.parent_id IS NULL 
   OR c1.parent_id IN 
     (SELECT id FROM comments WHERE parent_id IS NULL)
ORDER BY 
   IF(ISNULL(c1.parent_id)
    , c1.score
    , c2.score + (c1.parent_id * @id_multiplier) - (1-(c1.score * @score_multiplier))
    ) DESC

Paginating hierarchical data using closure tables (MySQL)

1 Answers1