Child Recordcount

[Vorsuc]

hi,

I have a multithreaded discussion board and what I want to do is count up the TOTAL number of replies to a topic.

The board is multilevel, so people can reply to replies of replies.

I'm using ASP to connect to an Access database.

At the moment I'm getting the count like this :

getReplies(ID)
get all replies to ID
loop through replies
for each reply, call getReplies with ID of reply
increment replyCount
end loop
end get replies

Now this means opening a new recordset for each level. So if a topic is bounced between two people, say for 20 posts, that's 20 recordsets opened, each with a count of 1.

To do this for for every post, when there's 20 posts on a page must surely be pretty demanding of resources.

I've seen SQL that will get a count of replies to a message, but what I want is not only replies to the parent message, but replies to the replies, replies to the replies of the replies and so on.

Any ideas ???

 

[ClydeDoggie]

It depends on how your table structure is set up but this is a simple example I just whipped up. You have one table with three fields, ThreadID (autonumber), ParentID (long), ChildID (long). ParentID refers to ThreadID of post you are responding to, ChildID is assigned sequentially for each response to a post. ChildID is blank for Original Post.
EXAMPLE
Original Post(OP)
Reply1(R1)
Reply2(R2)
Reply3(R3)
Reply4(R4)
Reply5(R5)
Table looks like this:
ThreadID, ParentID, ChildID
1,1,
2,1,1
3,1,2
4,3,1
5,3,2
6,1,3

Count should return 5 if I understand you correctly. This is the SQL code to do this:
SELECT Count(tblThreadMonitor.ThreadID) AS CountOfThreadID
FROM tblThreadMonitor INNER JOIN tblThreadMonitor AS tblThreadMonitor_1 ON tblThreadMonitor.ThreadID = tblThreadMonitor_1.ParentID
WHERE (((tblThreadMonitor_1.ChildId) Is Not Null));

I hope this helps you. If it doesn't conform to the table structure you are using let me know what it is and I will see if I can get it to work.

 

[Vorsuc]

Thank you for taking the time to help me.


Ok, my DB setup is as follows...

tblTopics (a table containing only "top level" posts)
tblComments (table containing replies)

The reason is because the board is actually a Weblog and the "reply to post" ability was tacked on.

structure for tblComments :

RecID (autonumber)
ReplyID (Parent RecID)
Comment (Yes/No field, indicating if message is a reply to )
UserID (who posted the message)
Subject
Body
Date_Posted

Now, if this is a problem, then it's a simple enough job to re-do the tables from scratch, if there's a more "agreeable" format.

I did notice though, in the solution you posted, if the db contains more than one original post, the query counts replies to both topics.

I can see a solution in throwing in a "Original Post ID" field, and just doing a SELECT for that O.P ID, but I'm just curious to see if it can be done in SQL.

-V.

 

[ClydeDoggie]

No way in Access SQL (at least as far as I know), you can do it pretty easily in SQL Server using a recursive function. You could also write a recursive function in VBA that returned a value, but I don't know if that would be useful to you. I can show you an example of one if you are interested.

 

[Vorsuc]

It's ok, I'll just do as I posted previously, and slap in a "thread ID" field. Will be only a minor tweak to the DB and pages.

Thanks for the help though. Much appreciated.