For those SQL gurus out there

threaded/nested comments in one sql query

[eaden]

Hi there,
I would like to make a threaded discussion in php/mysql ( with unlimited levels), and I have done a google and found a few howtos, but they all use recursive functions/mysql queries, which means on a page with 50 comments, thats 50 sql queries.

To avoid this, you need to get the comments out of the database in the order in which they are to be displayed. Take the following example

1 Comment
3 ¦- reply to Comment 1
8 -¦-- reply to comment of a comment
7 ¦- another reply to comment 1
2 Comment
5 ¦- reply to comment 2
6 ¦- another reply to comment 2
4 Comment

The numbers down the left are the autoincremented ID numbers, and are the order in which the comments are posted. The varibles I see that make sense to be stored in the database are ID - id of comment, ParentID ( i.e. comment 5's parent is 2 ) , RootID - the id of the comment's root ( i.e. comment 8's root is 1 )

Now, can I somehow, in one sql query, using that data, order the records as above? Or alternatly, if someone else has a way to do this with a slightly different table layout that would be cool too.

[stevedob]

Would you really want to expand all threads initially? That presupposes that all threads are equally important to the viewer and, by doing that, limits the amount of new information that is displayed per 'screen'.

Is it not preferable to select and expand only user-chosen threads? Start with the latest messages that are immediate children of the forum, and allow the user to dictate what gets opened further.

So your query could be to select a certain number of messages, sorted by date, that have the particular forum as their parent.

I apologise if that sounds somewhat "you don't want that, and you don't start here". My only excuse is time spent as an Oracle consultant :)

[eaden]

Would you really want to expand all threads initially? That presupposes that all threads are equally important to the viewer and, by doing that, limits the amount of new information that is displayed per 'screen'.

Well, the first 3 levels will display the title and the content of the comment, and the deeper levels will just display the title, but it doesn't really affect the query.

Basicly like how slashdot etc works

[stevedob]

Ah. In which case you might want to take a look at Slash (the code that runs slashdot) for some ideas.

[eaden]

Yeah I have looked at quite a bit of code. Problem is the scripts that do it properly are in perl, and while i try to understand them it's not that easy.

And the scripts for php use recursive sql queries, or do something a bit different. I have read about 100 pages but still no luck!

[eaden]

for example this great article :
devshed article [216.239.53.100]

does exactly what I want, but uses recursive mysql queries! which means on a thread of 100 there'll be 100 queries. not exactly scalable!

[edited by: jatar_k at 3:59 pm (utc) on Mar. 12, 2003]
[edit reason] fixed sidescroll link [/edit]

[aspr1n]

Why couldn't you create two tables one for threads one for comments

Threads:
ThreadID ( auto-increment )

Comments:
CommentID ( auto-increment )
ThreadID
ReplyTo
DatePosted

SELECT * FROM comments
WHERE ThreadID = '".$threadid."'
ORDER BY ReplyTo, DatePosted

The first comment in a thread has its ReplyTo value set to itself. As the CommentID is auto-incremented you can guarentee any reply will have a higher number than the previous comment.

By sorting by the date/time posted you get the correct order. The just use a CSS rule to indent them all.

asp

[Allen]

The following structure also works for nested folders / categories:

tblcategory:
id
name
owner <--- tblcategory.id - this category is a subcategory of tblcategory.id

Allen

[eaden]

Thanks for all the replies, however I have since discovered that it is impossable to do in SQL. So I will be starting a new thread on how to make a recursive array.