Interesting post on how to use a different schema strategy for nested comments that doesn’t involve a recursive hierarchy:
One approach I remember seeing somewhere was rather than using a Comment ID and a Parent ID , comments had a Comment ID and a “sort key” which was a concatenation of all the Comment IDs of their ancestors.
E.g. If comment 1 had two replies, comments 2 and 3, the sort keys would be:
1 : 0001
2 : 0001.0002
3 : 0001.0003
Then if someone replied to comment 2, it would be..
4 : 0001.0002.0004
So if you select all comments and sort by this sort key, they’ll fall out in the right order.
Then, to do the indenting, you simply look at the length of the sort key to see how many levels deep the comment is, and indent an appropriate amount.
Adding comments is easy: the sort key of the new comment is simply it’s parent’s sort key, with its own ID added on to the end.
Obviously you’d have to pad the ID values sufficiently. In addition, if you wanted to just show comment 12345 and all children, you could just display the thread where code like ‘%00012345%’.