I have a simple guestbook script which just has a chronological list of comments, but I would like to add the ability to reply to a comment so I will have threaded comments.
The following is my schema and I’m struggling to work out what to add to get my threaded comments. I get that I need an index of indexes but I can’t get my head around it.
when you say “threaded”:… do you want reply-to-replies, or just… reply-to-parent “threading”?
if the latter, just stick a parent in there with type integer - if parent is null, it was a root guestbook entry. If parent is not null, it was a response to a root guestbook entry.
if the former…bleh. (There are ways to do it, but… bleh.)
Then you build a recursive function in your code which pulls a post and then calls itself to find any posts that have it as the parentRecordId. You CAN do it in SQL if you really want to, but in code is easier and faster.
I now realise why I was thinking it was more complicated.
A parent entry will always occur before a child entry, so when listing all entries, having output a parent entry I will need to search the entire table to find any children to output. Isn’t that so?
I was therefore thinking I needed an additional table giving child posts.
An initial guestbook entry will be placed here with no parent id (NULL or something to that effect)
Any comments to a guestbook entry will have a parent id of the initial guest book entry.
Any comment to a comment will have a parent id to the comment being commented on
And so on ()
Pseudo logic
// an initial entry will have a value of -1 since it's the first post
// I'd probably use NULL instead but using -1 for simplicity
initialEntry = -1;
ShowGuestBook(initialEntry)
ShowGuestBook(int parent) {
// pull the entries from the guestbook
SELECT id, name, email, comment FROM guestbook where parentRecordId = parent
for each entry found {
// Guestbook Entry Display Code goes here...
:
// now show all entries which are responses to this particular entry
ShowGuestBook(id);
}
}
So this is going to show something like:
Entry
Comment
Comment
Comment
Comment
Entry
Comment
Comment
Comment
I’d probably have ShowGuestbook() return a string, so the sub-portions can be wrapped to help with display issues (inner list/div/whatever), etc.
Much to my relief I have turned your pseudo-code into real code, using an empty string to mean ‘no parent’.
Yes, I think I need to know how many levels in I am so I can apply the right class to the comment. I was thinking just a number, but perhaps your suggested string has more to it than just the level…
If you have a Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Your Guestbook post…
#1: You’re making a call to the database for every one of those Re’s. #2: You’re trying to style an element that many layers deep on your webpage.
Unless your guestbook is going to be… rediculously large, you may want to consider dumping the entire table into memory and having javascript or whatever backend you’re using reform it into a nested object that you can more easily navigate.
Note that to create an output like you get here, where the display is flat, but it allows responding to posts, you actually only need 1 call, with 2 pieces of information:
Instead of just storing a parent, store a parent and a thread.
When you need to display the thread, pull all entries with the thread ID.
Any post with a parent can be referenced in-memory, because the parent must be in the thread.
When creating a response, you need the parent id; you can pull the thread from the parent’s entry and duplicate it (or pass it as well. either way).
Yes, too many levels would be silly, but it should be easy enough to limit the number of levels - once I can figure out where to increment and reset the level count.
I should like to add infinite scrolling so the initial page displays quickly, but I’ve not found a tutorial for infinite scrolling when extracting information from a database, and I’m not sure how the LIMIT clause would work practically with nested queries.