YAFLogo

chriscoe71
  • chriscoe71
  • 79.4% (Friendly)
  • YAF Forumling Topic Starter
15 years ago
Reference: http://forum.yetanotherforum.net/yaf_postst8067_Last-Post-column-doesnt-reflect-subforum-posts.aspx 

From what I can tell, there are 2 issues with this function (forum_lasttopic):

Issue #1: It doesn't look any further than the first level beneath it to check for posts

Issue #2: If the parent was NULL, the comparison failed and the result was NULL, instead of just using the child post information

The attached patch fixes Issue #2. This will probably work for most sites. For other sites that have more than 2 levels below the parent forum, then Issue #1 will still be a problem. When I have more free time, I will look into altering it so that it chases every branch down the tree.

This is the same information that I put in the post above, however, I am getting familiar with the "Patch" function in SVN and thought I would make it a little easier on everybody.


Sponsor
coral
  • coral
  • 72% (Friendly)
  • YAF Lover
10 years ago
It seems that the issue #1 still exists in YAF version 2.
shide
  • shide
  • 80.6% (Honored)
  • YAF Lover
10 years ago
i just discovered this too,

there a stored procedure called "yaf_forum_updatelastpost" that is the main cause.

it's originally like this :

ALTER procedure [dbo].[yaf_forum_updatelastpost](@ForumID int) as
begin
        update [dbo].[yaf_Forum] set
        LastPosted = (select top 1 y.Posted from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
        LastTopicID = (select top 1 y.TopicID from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
        LastMessageID = (select top 1 y.MessageID from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
        LastUserID = (select top 1 y.UserID from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
        LastUserName = (select top 1 y.UserName from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc),
        LastUserDisplayName = (select top 1 y.UserDisplayName from [dbo].[yaf_Topic] x join [dbo].[yaf_Message] y on y.TopicID=x.TopicID where x.ForumID = @ForumID and (y.Flags & 24)=16 and x.IsDeleted = 0 order by y.Posted desc)
    where ForumID = @ForumID
end

We have 2 problems, first it doesn't update the "LastUserName" or the same pre calculated fields,

and it's too long to execute and doesn't seems to work...

So i changed it this way first, to look at the "lastuser" etc.. kind of fields, without doing those "INNER JOIN" on the messages:


			
			update [dbo].[yaf_Forum] set
				LastPosted = (select top 1 x.LastPosted 
from [dbo].[yaf_Topic] x with(nolock) 
where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),[/indent][/indent]
				LastTopicID = (select top 1 x.TopicID
from [dbo].[yaf_Topic] x with(nolock) 
where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastMessageID = (select top 1 x.LastMessageID 
from [dbo].[yaf_Topic] x with(nolock) 
where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastUserID = (select  top 1 x.LastUserID  
from [dbo].[yaf_Topic] x with(nolock) 
where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastUserName = (select top 1 x.LastUserName
 from [dbo].[yaf_Topic] x with(nolock) 
where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastUserDisplayName = (select top 1 x.LastUserDisplayName 
from [dbo].[yaf_Topic] x with(nolock) 
where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc)
			where ForumID = @ForumID

Now as you said, we need the have those subforums and parent forums updated correctly, so i changed it again this way

to udpate the current forum with the last stats from the latest sub forum and then update each sub forum individually



		IF NOT EXISTS(SELECT 1 FROM yaf_Forum WHERE ParentID=@ForumID) 
			update [dbo].[yaf_Forum] set
				LastPosted = (select top 1 x.LastPosted from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and x.IsDeleted = 0 order by x.Posted desc),
				LastTopicID = (select top 1 x.TopicID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastMessageID = (select top 1 x.LastMessageID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastUserID = (select  top 1 x.LastUserID  from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastUserName = (select top 1 x.LastUserName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc),
				LastUserDisplayName = (select top 1 x.LastUserDisplayName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = yaf_Forum.ForumID and  x.IsDeleted = 0 order by x.Posted desc)
			where ForumID = @ForumID
		ELSE
			BEGIN

				DECLARE @ChildID int
				SET @ChildID = (SELECT TOP(1) ForumID FROM yaf_Forum WHERE ParentID=@ForumID ORDER BY LastPosted DESC)
				--Update Current Forum last stats
				UPDATE [dbo].[yaf_Forum] SET
					LastPosted = (select top 1 x.LastPosted from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and x.IsDeleted = 0 order by x.Posted desc),
					LastTopicID = (select top 1 x.TopicID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and  x.IsDeleted = 0 order by x.Posted desc),
					LastMessageID = (select top 1 x.LastMessageID from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and  x.IsDeleted = 0 order by x.Posted desc),
					LastUserID = (select  top 1 x.LastUserID  from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and  x.IsDeleted = 0 order by x.Posted desc),
					LastUserName = (select top 1 x.LastUserName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and  x.IsDeleted = 0 order by x.Posted desc),
					LastUserDisplayName = (select top 1 x.LastUserDisplayName from [dbo].[yaf_Topic] x with(nolock) where x.ForumID = @ChildID and  x.IsDeleted = 0 order by x.Posted desc)
				WHERE ForumID = @ForumID

				--Update Children Forums last stats
				DECLARE @TblForum TABLE(ForumdID int)
				INSERT INTO @TblForum
					SELECT ForumID 
					FROM yaf_Forum
					WHERE ParentID=@ForumID
				
				WHILE EXISTS(SELECT 1 FROM @TblForum) 
					BEGIN
						DECLARE @ID int
						EXEC [yaf_forum_updatelastpost] @ID
						DELETE FROM @TblForum WHERE ForumdID = @ID
					END
			END

I've tested and it seems to work fine.

it also helps to load the default forum page faster when selecting the last User,message etc.. values.

On the main SP (yaf_forum_listread), we have this :


        LastUser = IsNull(t.LastUserName,(select x.[Name] from [dbo].[yaf_User] x with(nolock) where x.UserID=t.LastUserID)),

which is fine but too slow when all the "lastUser" kind of fields are always empty...

It's too bad that yaf isn't optimized yet for larger databases.

Tell me what you think, i hope it will help others looking at this