Eager instead of lazy but how about: pull all the comments in chronological order starting from the beginning and then build the tree downstream of the database as items come in?

See also the nested set representation of trees which makes it fast to get subtrees.

[1] https://github.com/etrepat/baum [2] https://www.amazon.com/Hierarchies-Smarties-Kaufmann-Managem...

Eager instead of lazy but how about: pull all the comments in chronological order starting from the beginning and then build the tree downstream of the database as items come in?

See also the nested set representation of trees which makes it fast to get subtrees.

[1] https://github.com/etrepat/baum [2] https://www.amazon.com/Hierarchies-Smarties-Kaufmann-Managem...

Threaded comments are surprisingly difficult to model in a relational database. I spent about a month on the problem when I rolled my own threaded comment system for newsley. Relational Databases really don't lend themselves to easily modeling tree structures.

If you're interseted in doing something like this, I'd highly recommend this book: http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Manageme...

He has two really good chapters that specifically building a comment system. It takes a bit of work, and you end up pre-computing the comment system on INSERT's instead of SELECT's. But, you can build a comment system that pulls your entire comment tree in order using a single SELECT ... FROM COMMENTS ... ORDER BY comment.node_left;