Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL for Threaded Discussion Forums

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2000-11-14 : 20:18:18
One of questions we get on a regular basis involves threaded discussion forums. Everyone wants to know an easy way to do this. Everyone is also trying to do recursive SQL or self joins to make this work. That's hard. Here's an easy way to do this. (UPDATE: Fixed a problem and thought I'd repost for everyone to see the changes)

Article Link.

EvilSyn
Starting Member

1 Post

Posted - 2002-02-18 : 15:36:39
Hi. I recently wrote a message board using the code from this... (http://www.sqlteam.com/item.asp?ItemID=1353) and i had to write in an admin for the client to be able to edit and delete the posts... Editing was no problem, but I had a heck of a time trying to figure out how to delete a post *and* it's children. I spent the better part of all day coding this and I finally finished... So in case anyone ever needs to know how to remove children posts, here's how.

Also, I'm sure i overlooked something and there's probably a *much* simpler way to do so. If this is the case, please let me know!!! Thanks

CODE
***********************************************************

set rs = server.createobject("adodb.recordset")
sql = "SELECT ListServ_Members.Name, ListServ_Members.Email, ListServ_Posts.MemberID, ListServ_Posts.TopicID, ListServ_Posts.Subject, ListServ_Posts.PostID, ListServ_Posts.ThreadID, ListServ_Posts.ParentID, ListServ_Posts.PostLevel, ListServ_Posts.SortOrder, ListServ_Posts.PostDate from ListServ_Posts inner join ListServ_Members on ListServ_Members.id = ListServ_Posts.MemberID where ListServ_Posts.ThreadID = " & request("ThreadID") & " ORDER BY ListServ_Posts.SortOrder"

FieldList = "PostID,ParentID"
FieldsArray = split(FieldList, ",")

rs.open sql, strconn, 1, 2, 1
ThreadArray = rs.GetRows(-1, 0, FieldsArray)
rs.close
set rs = nothing

PostIDList = ""
Level = 1
PostID = cint(request("PostID"))
GetRecords ThreadArray, PostID, Level
PostIDList = PostIDList & PostID

response.write("<br><br>" & PostIDList)

function GetRecords(RecordArray, LookingFor, Level)

if level = 20 then
exit function
end if


for z = 0 to ubound(RecordArray, 2)


CurrentID = RecordArray(1, z)
CurrentParent = RecordArray(0, z)

if CurrentID = LookingFor then

'uncomment for nice debugging output
'response.write("<br>" & replace( space(Level), " ", " " ) & "Pass: " & Level & " - " & z & ", FOUND - " & CurrentID & " | GO FIND: " & CurrentParent)
PostIDList = PostIDList & CurrentParent & ","

if CurrentID <> CurrentParent then
GetRecords RecordArray, CurrentParent, Level + 1
end if

'uncomment for nice debugging output
'else

'uncomment for nice debugging output
'response.write("<br>" & replace( space(Level), " ", " " ) & "Pass: " & Level & " - " & z & ", NOT FOUND")

end if

next

end function


Go to Top of Page

xstream
Starting Member

11 Posts

Posted - 2002-04-19 : 10:15:39
My feeble attempt at getting this to work.

http://www.piestactics.com/about/xstream/dev/

X

Go to Top of Page

xstream
Starting Member

11 Posts

Posted - 2002-08-30 : 21:07:17
In the stored procedure where it says:

IF EXISTS (SELECT *
FROM Posts
WHERE PostLevel <= @ParentLevel
AND SortOrder > @ParentSortOrder
AND ThreadID = @ThreadID )
BEGIN


Shouldn't it be:

WHERE PostLevel >= @ParentLevel

If you selected all the posts in the thread that were less than the parent post level but larger than the parent sort order - wouldn't that be none of the posts?

X





Go to Top of Page

Jo-E
Starting Member

1 Post

Posted - 2003-03-15 : 20:49:37
I got this working on a production site that serves many users, and reached the conclusion that there's one thing that was overlooked and has a lot to do with performance.
If you only display X root posts on every page of your discussion forum, you need to make sure the SP doesn't run on all root posts in the requested forum but only on the X posts relevant to that page, otherwise the temporary table will always contain the entire forum even though you only need several posts, which means a waste of server resources.
Another thing is if you want to avoid many deadlocks make sure you create the cursor as READ_ONLY!
This is how it looks now:

CREATE PROC GetChildren
(@ParentID int, @forumID int, @PostLevel int, @ParentPostKey varchar(200), @forumRequestedPage int, @forumMessagesPerPage int) AS

SET NOCOUNT ON

DECLARE @NextLevel int, @Counter int, @PostKey varchar(200), @jumpRows int

SET @Counter = 1

-- Build a cursor to loop through all the kids of this post based on the fact if it's a root post or not
IF @ParentID = 0 BEGIN
DECLARE c1 CURSOR LOCAL SCROLL READ_ONLY FOR
SELECT messageID
FROM messages
WHERE messageParent = @ParentID and messageForum = @forumID and deleted=0 and authorized=1
ORDER BY messageTime DESC

-- since this is a root posts query, gets only the rows that are relevant to the requested page
OPEN c1
IF @forumRequestedPage > 1
BEGIN
SET @jumpRows = ((@forumRequestedPage - 1) * @forumMessagesPerPage) + 1
FETCH ABSOLUTE @jumpRows FROM c1
into @ParentID
END
ELSE BEGIN
FETCH NEXT FROM c1
INTO @ParentID
END

-- this loop runs on all parents until there are no more on this page or at all
WHILE @@FETCH_STATUS = 0 AND @Counter <= @forumMessagesPerPage
BEGIN

-- Put this record in the temp table
INSERT #NestedPosts (PostID, PostKey, messageLevel) VALUES (@ParentID, @PostKey, @PostLevel)

SET @NextLevel = @PostLevel + 1

-- Process all the children for this post
EXEC GetChildren @ParentID, @forumID, @NextLevel, @PostKey, @forumRequestedPage, @forumMessagesPerPage

SET @Counter = @Counter + 1

-- And get the next record at this level
FETCH NEXT FROM c1
INTO @ParentID
END
END
ELSE BEGIN
DECLARE c1 CURSOR LOCAL SCROLL READ_ONLY FOR
SELECT messageID
FROM messages
WHERE messageParent = @ParentID and messageForum = @forumID and deleted=0 and authorized=1
-- Here we can put ASC so that the inner thread will be ordered by the order of insertion and not new comes first
ORDER BY messageTime DESC

-- gets all the kids for this post since it's not a root one, no need for jumping
OPEN c1
FETCH NEXT FROM c1
INTO @ParentID

WHILE @@FETCH_STATUS = 0
BEGIN

-- Put this record in the temp table
INSERT #NestedPosts (PostID, PostKey, messageLevel) VALUES (@ParentID, @PostKey, @PostLevel)

SET @NextLevel = @PostLevel + 1

-- Process all the children for this post
EXEC GetChildren @ParentID, @forumID, @NextLevel, @PostKey, @forumRequestedPage, @forumMessagesPerPage

SET @Counter = @Counter + 1

-- And get the next record at this level
FETCH NEXT FROM c1
INTO @ParentID
END
END

CLOSE c1
DEALLOCATE c1

SET NOCOUNT OFF
GO


Edited by - Jo-E on 03/15/2003 20:51:02
Go to Top of Page

famdylan
Starting Member

1 Post

Posted - 2005-05-17 : 11:31:11
Okay on the same type of question say I wanted to move one of the lower replyies up to a upper level or move it to another thread how would I update all the children? Can I do this in the database or do I need to do some code in my application?
Go to Top of Page

yaojinzhen
Starting Member

1 Post

Posted - 2006-07-26 : 13:08:25
I ran into same issue. I am developing Edit/delete code for admin site, editors want to change parent within/between threads for certain post, I have hard time to figure out new sorting. Help please.

~Gin

quote:
Originally posted by famdylan

Okay on the same type of question say I wanted to move one of the lower replyies up to a upper level or move it to another thread how would I update all the children? Can I do this in the database or do I need to do some code in my application?

Go to Top of Page

minou30
Starting Member

2 Posts

Posted - 2009-05-17 : 21:14:28
Having difficult time translating to MYSQL. Can anyone help?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-17 : 21:17:07
quote:
Originally posted by minou30

Having difficult time translating to MYSQL. Can anyone help?


try posting at MYSQL forum. This is a MS SQL Server forum


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -