Researching hierarchical data persistence and led me to closure tables and pieced together this comment structure based off of the culmination of said research.
Queries for creating new nodes in the closure table were easy enough for me to grasp and fetching data for descendants via a JOIN on the closure table is simple enough.
However, I would like to expand upon that and get results back sorted and limited by both number of parents/children down through a depth of x.
I'm trying to keep things timely/efficient (I expect comments table to get very large) by making use of foreign keys and indexes. I am shooting for an all in one query that can do what I ask in the title, but am not opposed to breaking it up to increase speed/efficiency.
Current table structures:
CREATE TABLE `comments` (
`comment_id` int(11) UNSIGNED PRIMARY KEY,
`reply_to` int(11) UNSIGNED NOT NULL DEFAULT '0',
`user_id` int(11) UNSIGNED NOT NULL,
`comment_time` int(11) NOT NULL,
`comment` mediumtext NOT NULL,
FOREIGN KEY (`user_id`) REFERENCES users(`user_id`)
) Engine=InnoDB
CREATE TABLE `comments_closure`(
`ancestor_id` int(11) UNSIGNED NOT NULL,
`descendant_id` int(11) UNSIGNED NOT NULL,
`length` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY(`ancestor_id`, `descendant_id`),
KEY `tree_adl`(`ancestor_id`, `descendant_id`, `length`),
KEY `tree_dl`(`descendant_id`, `length`),
FOREIGN KEY (`ancestor_id`) REFERENCES comments(`comment_id`),
FOREIGN KEY (`descendant_id`) REFERENCES comments(`comment_id`)
) Engine=InnoDB
A clearer summary of what I'm trying to do would be to fetch 20 comments that share an ancestor_id, sorted by time. While also fetching each one's comments 2 length deeper (keeping these limited to a much smaller amount 2) also sorted by time.
I'm not looking to always sort by time however and would also like to fetch results sorted by their comment_id Is it possible to do all this in a single query? I'm not quite sure where to begin.
