Refresh "Most recent by" caches

elsdoerfer

Due to manual fiddling with the database (pre-import into Vanilla from punbb, actually). a good number of Discussion in the new Vanilla display the wrong information about the most recent comment.

Is there any functionality / code out there or in Vanilla which would refresh these caches?

elsdoerfer

Thanks for the starting point. Here's what I ended up using:

-- Set last comment ids
update GDN_Discussion d 
set LastCommentID = (select c.CommentID from GDN_Comment c where c.DiscussionID = d.DiscussionID order by c.DateInserted desc limit 1);

-- Set last comment user ids
update GDN_Discussion d
join GDN_Comment c
  on d.LastCommentID = c.CommentID
set d.LastCommentUserID = c.InsertUserID;

-- Set last comment dates
update GDN_Discussion d
join GDN_Comment c
  on d.LastCommentID = c.CommentID
set d.DateLastComment = c.DateInserted;

-- set comment dates for zero-response discussions
update GDN_Discussion set DateLastComment = DateInserted WHERE LastCommentID is NULL;

Todd

There isn't a way to do this within Vanilla, but try the following SQL.

update GDN_Discussion d
set LastCommentID = (select max(c.CommentID) from GDN_Comment c where c.DiscussionID = d.DiscussionID);

update GDN_Discussion d
join GDN_Comment c
  on d.LastCommentID = c.CommentID
set d.DateLastComment = c.DateInserted;

elsdoerfer

Thanks for the starting point. Here's what I ended up using:

-- Set last comment ids
update GDN_Discussion d 
set LastCommentID = (select c.CommentID from GDN_Comment c where c.DiscussionID = d.DiscussionID order by c.DateInserted desc limit 1);

-- Set last comment user ids
update GDN_Discussion d
join GDN_Comment c
  on d.LastCommentID = c.CommentID
set d.LastCommentUserID = c.InsertUserID;

-- Set last comment dates
update GDN_Discussion d
join GDN_Comment c
  on d.LastCommentID = c.CommentID
set d.DateLastComment = c.DateInserted;

-- set comment dates for zero-response discussions
update GDN_Discussion set DateLastComment = DateInserted WHERE LastCommentID is NULL;