Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Refresh "Most recent by" caches

edited December 2011 in Vanilla 2.0 - 2.8

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?

Best Answer

  • 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;
    

Answers

  • ToddTodd Chief Product Officer Vanilla Staff

    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;
    
  • 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;
    
Sign In or Register to comment.