Here are a couple of ideas I have come up with to solve this problem:
1) Poll the database (using a stored procedure that returns the number I'm looking for) for each forum when I loop all the fourms.
- I suspect this approach isn't optimal, since it creates more traffic to the database.
2) Have fields in my Forum and ForumPost tables for 'Number of threads' and so on. Now, create triggers that updates these fields every time a post is made.
- I guess this would be much more effective than the first apporach, since everything is done on the database server directly.
Are there any other ways that are better? Please advice!
Thanks a bunch for any help!
You need to look into using aggregation functions like count and sum, you might want to look into max for you last date time.
select f.ForumName, tc.PostCount
from Forum f,
( Select Count(*) AS PostCount, ForumId
From ForumPost
Group By ForumId
) tc
where tc.ForumId = f.ForumId
This will give you a count of all the posts for a forum for example.
Another idea is download the code for Community Server Forum and see how they do it as they seem to have it working ok.http://www.telligentsystems.com/Solutions/Forums/|||Yup, I know how to write the SQL to retrieve the values I need. What I was looking for was more what way is more efficient. Retrieving the values from the database for each forum I have (doing it while I loop the forums and draw out my page) or if maybe it would be wiser to create some triggers on the tables that update some fields every time a new post has been saved.
I will take a look at the community server forum to see how they did it. It might give me a good idea.
Thanks!|||Sorry, I misunderstood.
I can only speculate, which I will do. It think your trigger option would be the fastest in retreival. More people tend to look at threads and posts over actually posting. I am not sure why you would need to iterate through for each forum. Your SQL should be able to return all this information in one recordset.
Good luck.|||
It's all good!
I think you have a good point there. I guess I could return the number of posts etc. when I query for forums and wherever I need to sum up values like that. I don't really need to store that stuff anywhere.
Thanks alot for that input!
|||I'm struggeling a little bit with my sql statement. I need it to return all records from the Forum table regardless of data in other tables (ForumMessage and Member). This statement doesn't do so. Any ideas?
Here's the statement:
SELECT F.ForumID, F.ForumCategoryID, F.Name, F.Description, F.FromDate, F.ToDate,
TC.Threads, TC.ForumId AS LastPostId, TC.MemberId AS LastPostByMemberId,
MC.Posts, MN.LastPostUserName
FROM
Forum F,
(
SELECT COUNT(*) AS Threads, ForumId, MemberId FROM ForumMessage
WHERE ParentForumMessageId = 0
GROUP BY ForumId, MemberId
) TC,
(
SELECT COUNT(*) AS Posts FROM ForumMessage
) MC,
(
SELECT MemberId, DisplayName AS LastPostUserName FROM Member
) MN
WHERE TC.ForumId = F.ForumId
AND MN.MemberId = TC.MemberId
ORDER BY F.Name
Try it like this instead:
DECLARE @.PostCount INT
SELECT @.PostCount = COUNT(*) FROM ForumMessage
SELECT F.ForumID, F.ForumCategoryID, F.Name, F.Description, F.FromDate, F.ToDate,
TC.Threads, TC.ForumId AS LastPostId, TC.MemberId AS LastPostByMemberId,
@.PostCount, MN.LastPostUserName
FROM
Forum F
LEFT OUTER JOIN
(
SELECT COUNT(*) AS Threads, ForumId, MemberId FROM ForumMessage
WHERE ParentForumMessageId = 0
GROUP BY ForumId, MemberId
) TC ON F.ForumID = TC.ForumID
LEFT OUTER JOIN
(
SELECT MemberId, DisplayName AS LastPostUserName FROM Member
) MN ON TC.MemberID = MN.MemberID
ORDER BY F.Name
|||Excellent! Thank you!
