I am trying to request ordered hierarchical comments sorted by score . score is an integer, and the comment table has a self-referencing parent_id column.
Each page must have at least one root comment, followed by its children . If the dataset has only one comment with a root, only one page is 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 would 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 will be empty because root comments are missing.
I use the closing support table, as described by Bill Karwin , because comment subtrees can be viewed independently using any comment as root comment, and this seems like the best solution for this.
The structure and sample data for the table in question:
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);