Most efficient way to fetch and output Content with 2-Level Comments?

Question

I have some content with up to 2-levels of replies. I am wondering what the most efficient way to fetch and output the replies. I should note that I am planning on storing the comments with fields content_id and reply_to, where reply_to refers to which comment it is in reply to (if any). Any criticism on this design is welcome.

In pseudo-code (ish), my first attempt would be:

# in outputting content CONTENT_ID
all_comments = fetch all comments where content_id == CONTENT_ID
root_comments =  filter all_comments with reply_to == None
children_comments = filter all_comments with reply_to != None

output_comments = list()
for each root_comment
    children = filter children_comments, reply_to == root_comment.id
    output_coments.append( (root_comment, children) )

send output_comments to template

Is this the best way to do this? Thanks in advance.

Edit: On second thought, I'll want to preserve date-order on the comments, so I'll have to do this a bit differently, or at least just sort the comments afterward.

Answer 1

If you're going to display all the comments anyway, just fetch them all in one operation, then build the tree in Python.