eadz (eadz) wrote in lj_dev,

A discussion on recursive thread sql queries

Hi there,
I have been thinking about recursive thread sql queries, e.g. the comments thread on live journal.

The problem :
there is no simple way to order the threads in the order they need to be displayed in. So there are workarounds, and I'm wondering what is the best. I have looked at some livejournal code, but I couldn't quite get what model is in use.

The candidates:

( incomplete if there are some methods I have missed please let me know )

  • Recursive functions in perl/php

this is a method used in quite a few php forums, and involves doing an sql query for each comment to see if there is a child comment. simple but way too many sql queries. Limitless in depth and number of comments.

  • Sorting in the code

this is getting all the rows, and then sorting them using arrays etc. Limitless in depth and number of comments.

  • Adding some sort of order field to the table

this involves adding some sort of index to the comments table, that can be used in an order by statement. This is the one I am leaning towards, mainly becasue it allows you to get all the comments in one query, and in correct order. The disadvantage is that it uses quite a bit of space on the table.

What does live journal use? what do other journals use?
Also If this is the wrong journal to post this in sorry..

  • Post a new comment


    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded