Atom Feed - YAF.NET Support Forum - Topic:Last Post column doesn't reflect subforum posts - 15YAF.NET Support Forum - Atom Feedurn:https:--yetanotherforum-net:AtomFeed:YAF.NETSupportForum:Topic:LastPostcolumndoesn'treflectsubforumposts-15:1Copyright 2021 YAF.NET Support Forum2021-06-24T07:38:44Zhttps://yetanotherforum.net/forum/Images/Logos/YAFLogo.svgForum Adminhttps://yetanotherforum.netforum@yetanotherforum.netshidehttps://yetanotherforum.net/forum/Profile/29425-shideshidehttps://yetanotherforum.net/forum/Profile/29425-shidecoralhttps://yetanotherforum.net/forum/Profile/27423-coralchriscoe71https://yetanotherforum.net/forum/Profile/10063-chriscoe71YetAnotherForum.NETurn:https:--yetanotherforum-net:ftPosts:st1:meid63329:1Last Post column doesn't reflect subforum posts<table class="content postContainer_Alt" width="100%"><tr><td>i just discovered this too,<br />there a stored procedure called "yaf_forum_updatelastpost" that is the main cause.<br /><br />it's originally like this :<br /><br /><div class="code"><pre class="line-numbers"><code class="language-sql">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</code></pre>
</div><br />We have 2 problems, first it doesn't update the "LastUserName" or the same pre calculated fields, <br />and it's too long to execute and doesn't seems to work...<br /><br />So i changed it this way first, to look at the "lastuser" etc.. kind of fields, without doing those "INNER JOIN" on the messages:<br /><div class="code"><pre class="line-numbers"><code class="language-sql">
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</code></pre>
</div><br />Now as you said, we need the have those subforums and parent forums updated correctly, so i changed it again this way <br />to udpate the current forum with the last stats from the latest sub forum and then update each sub forum individually<br /><br /><div class="code"><pre class="line-numbers"><code class="language-sql">
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</code></pre>
</div><br />I've tested and it seems to work fine.<br />it also helps to load the default forum page faster when selecting the last User,message etc.. values.<br /><br />On the main SP (yaf_forum_listread), we have this :<br /><div class="code"><pre class="line-numbers"><code class="language-sql">
LastUser = IsNull(t.LastUserName,(select x.[Name] from [dbo].[yaf_User] x with(nolock) where x.UserID=t.LastUserID)),</code></pre>
</div><br />which is fine but too slow when all the "lastUser" kind of fields are always empty...<br />It's too bad that yaf isn't optimized yet for larger databases.<br /><br />Tell me what you think, i hope it will help others looking at this</td></tr></table>2014-04-08T10:03:20+02:002014-04-08T10:03:20+02:00shide<table class="content postContainer_Alt" width="100%"><tr><td>i just discovered this too,<br />there a stored procedure called "yaf_forum_updatelastpost" that is the main cause.<br /><br />it's originally like this :<br /><br /><div class="code"><pre class="line-numbers"><code class="language-sql">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</code></pre>
</div><br />We have 2 problems, first it doesn't update the "LastUserName" or the same pre calculated fields, <br />and it's too long to execute and doesn't seems to work...<br /><br />So i changed it this way first, to look at the "lastuser" etc.. kind of fields, without doing those "INNER JOIN" on the messages:<br /><div class="code"><pre class="line-numbers"><code class="language-sql">
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</code></pre>
</div><br />Now as you said, we need the have those subforums and parent forums updated correctly, so i changed it again this way <br />to udpate the current forum with the last stats from the latest sub forum and then update each sub forum individually<br /><br /><div class="code"><pre class="line-numbers"><code class="language-sql">
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</code></pre>
</div><br />I've tested and it seems to work fine.<br />it also helps to load the default forum page faster when selecting the last User,message etc.. values.<br /><br />On the main SP (yaf_forum_listread), we have this :<br /><div class="code"><pre class="line-numbers"><code class="language-sql">
LastUser = IsNull(t.LastUserName,(select x.[Name] from [dbo].[yaf_User] x with(nolock) where x.UserID=t.LastUserID)),</code></pre>
</div><br />which is fine but too slow when all the "lastUser" kind of fields are always empty...<br />It's too bad that yaf isn't optimized yet for larger databases.<br /><br />Tell me what you think, i hope it will help others looking at this</td></tr></table>urn:https:--yetanotherforum-net:ftPosts:st1:meid62552:1Last Post column doesn't reflect subforum posts<table class="content postContainer" width="100%"><tr><td>It seems that the issue #1 still exists in YAF version 2.</td></tr></table>2014-01-25T09:20:09+01:002014-01-25T09:20:09+01:00coral<table class="content postContainer" width="100%"><tr><td>It seems that the issue #1 still exists in YAF version 2.</td></tr></table>urn:https:--yetanotherforum-net:ftPosts:st1:meid38065:1Last Post column doesn't reflect subforum posts<table class="content postContainer_Alt" width="100%"><tr><td>Reference: <a target="_blank" rel="nofollow" href="http://forum.yetanotherforum.net/yaf_postst8067_Last-Post-column-doesnt-reflect-subforum-posts.aspx" title="http://forum.yetanotherforum.net/yaf_postst8067_Last-Post-column-doesnt-reflect-subforum-posts.aspx">http://forum.yetanotherforum.net/yaf_postst8067_Last-Post-column-doesnt-reflect-subforum-posts.aspx <i class="fa fa-external-link-alt fa-fw"></i></a><br /><br />From what I can tell, there are 2 issues with this function (forum_lasttopic):<br />Issue #1: It doesn't look any further than the first level beneath it to check for posts<br />Issue #2: If the parent was NULL, the comparison failed and the result was NULL, instead of just using the child post information<br /><br />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.<br /><br />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.<br /><br /></td></tr></table>2009-10-24T03:29:52+02:002009-10-24T03:29:52+02:00chriscoe71<table class="content postContainer_Alt" width="100%"><tr><td>Reference: <a target="_blank" rel="nofollow" href="http://forum.yetanotherforum.net/yaf_postst8067_Last-Post-column-doesnt-reflect-subforum-posts.aspx" title="http://forum.yetanotherforum.net/yaf_postst8067_Last-Post-column-doesnt-reflect-subforum-posts.aspx">http://forum.yetanotherforum.net/yaf_postst8067_Last-Post-column-doesnt-reflect-subforum-posts.aspx <i class="fa fa-external-link-alt fa-fw"></i></a><br /><br />From what I can tell, there are 2 issues with this function (forum_lasttopic):<br />Issue #1: It doesn't look any further than the first level beneath it to check for posts<br />Issue #2: If the parent was NULL, the comparison failed and the result was NULL, instead of just using the child post information<br /><br />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.<br /><br />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.<br /><br /></td></tr></table>