Welcome Guest! To enable all features please Login or Register.
Options
View
Go to last post Go to first unread
Offline chriscoe71  
#1 Posted : Friday, October 23, 2009 8:29:52 PM(UTC)
chriscoe71

Rank: Advanced Member

Reputation:

Medals: Medal of Open Source Contributer: Given to someone who gets open source and gives back to the community.

Joined: 3/2/2009(UTC)
Posts: 49

Thanks: 4 times
Was thanked: 9 time(s) in 7 post(s)
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.

File Attachment(s):
lastpostedcolumn.zip (1kb) downloaded 102 time(s).
Sponsor
Offline coral  
#2 Posted : Saturday, January 25, 2014 2:20:09 AM(UTC)
coral


Rank: YAF Lover

Reputation:

Joined: 5/9/2013(UTC)
Posts: 37

Thanks: 13 times
Was thanked: 4 time(s) in 3 post(s)
It seems that the issue #1 still exists in YAF version 2.
Offline shide  
#3 Posted : Tuesday, April 8, 2014 3:03:20 AM(UTC)
shide


Rank: YAF Lover

Reputation:

Joined: 3/10/2014(UTC)
Posts: 48
France
Location: Paris

Thanks: 1 times
Was thanked: 5 time(s) in 3 post(s)
i just discovered this too,
there a stored procedure called "yaf_forum_updatelastpost" that is the main cause.

it's originally like this :

Code:
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:
Code:

			
			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

Code:


		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 :
Code:

        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
Rss Feed  Atom Feed
Users browsing this topic
Similar Topics
Last Post column doesn't reflect subforum posts (YAF Issues & Bugs Discussion)
by LaB 3/26/2009 12:23:09 AM(UTC)
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error