Fork me on GitHub
Current releases are 2.1.5 (31 Oct 2014) and 2.0.18.13 (5 Aug 2014)

Ready for 2.1? Find out if your plugins are compatible. 2.0 will no longer be updated after Dec 2014.

[Solved] how to show the most recent comment?

AoleeAolee Hobbyist & Coder ✭✭
edited October 2011 in Vanilla 2.0 Help
This discussion is related to the DiscussionExcerpt2 addon.
is there a way to show the last or most recent comment of user? instead of the First?
Tagged:

Best Answer

«1

Answers

  • AoleeAolee Hobbyist & Coder ✭✭
  • AoleeAolee Hobbyist & Coder ✭✭
    well probably you wont like my solution because i pulled the last user's comment by adding a LEFT JOIN to the comment table and adding the BODY field to the Select statement in the core class.discussionmodel.php file. (again breaking the core)

    not sure if you're up to it as well, but as for me i dont want modify the plugin by doing another round of sql call just to fetch the last comment. so might as well fetch it along during the select to conserve server resource.

    check how it looks like here => www.pinoyau.info (scroll down to the non-announcement portion)

    if you want i can create a guide how i did it.
  • UnderDogUnderDog Moderator
    edited October 2011
    if you want i can create a guide how i did it.
    O WOW. That is a nice example of how to really improve vanilla.
    Thank you for offering to make a guide (we need to improve guides, tutorials etc anyway). Now I think we have to do something back for you, but I'm trying to figure out what's best.

  • I do like the speech bubble effect, is that part of your hack?
    Whatever you just explained went way over my head though. Nice job!
  • AoleeAolee Hobbyist & Coder ✭✭
    @underdog will try to make tomorrow :)

    @camo bubble effect is a css trick, ill create a guide tom for my version of the last user comment.
  • AoleeAolee Hobbyist & Coder ✭✭
    @camo - i created a guide temporarily here =>

    http://www.goodoldmemories.com/2011/10/last-comment-in-discussionexcerpt2/

    i need to upload the my modified version of discussion excerpt so i thought of creating it in my blog site.

    ill soon add it here as well so it will be easily searchable by others.
  • I followed your blog instructions but got error...

    Fatal Error in Gdn_Database.Query();

    Unknown column 'lc.Body' in 'field list'
    select d.Type as `Type`, d.InsertUserID as `FirstUserID`, lc.Body as `LastUserComment`, d.DateInserted as `FirstDate`, d.CountBookmarks as `CountBookmarks`, d.Body as `Body`, d.Format as `Format`, d.DateLastComment as `LastDate`, d.LastCommentUserID as `LastUserID`, d.*, w.UserID as `WatchUserID`, w.DateLastViewed as `DateLastViewed`, w.Dismissed as `Dismissed`, w.Bookmarked as `Bookmarked`, w.CountComments as `CountCommentWatch`
    from GDN_Discussion d
    left join GDN_UserDiscussion w on d.DiscussionID = w.DiscussionID and w.UserID = 13
    where d.DiscussionID in ('3', '8')
    and coalesce(w.Dismissed, '0') = :coalescewDismissed0
    order by d.DateLastComment desc
    limit 15

    276:
    277: if (!is_object($PDOStatement)) {
    278: trigger_error(ErrorMessage('PDO Statement failed to prepare', $this->ClassName, 'Query', $this->GetPDOErrorMessage($this->Connection()->errorInfo())), E_USER_ERROR);
    279: } else if ($PDOStatement->execute($InputParameters) === FALSE) {
    280: trigger_error(ErrorMessage($this->GetPDOErrorMessage($PDOStatement->errorInfo()), $this->ClassName, 'Query', $Sql), E_USER_ERROR);
    281: }
    282: } else {
    283: $PDOStatement = $this->Connection()->query($Sql);
    284: }

    [Sql] 'select d.Type as `Type`, d.InsertUserID as `FirstUserID`, lc.Body as `LastUserComment`, d.DateInserted as `FirstDate`, d.CountBookmarks as `CountBookmarks`, d.Body as `Body`, d.Format as `Format`, d.DateLastComment as `LastDate`, d.LastCommentUserID as `LastUserID`, d.*, w.UserID as `WatchUserID`, w.DateLastViewed as `DateLastViewed`, w.Dismissed as `Dismissed`, w.Bookmarked as `Bookmarked`, w.CountComments as `CountCommentWatch`
    from GDN_Discussion d
    left join GDN_UserDiscussion w on d.DiscussionID = w.DiscussionID and w.UserID = 13
    where d.DiscussionID in (\'3\', \'8\')
    and coalesce(w.Dismissed, \'0\') = :coalescewDismissed0
    order by d.DateLastComment desc
    limit 15'
    [InputParameters] array (
    ':coalescewDismissed0' => '0',
    )
    [Options] array (
    'ReturnType' => 'DataSet',
    )
    [ReturnType] 'DataSet'
    [PDOStatement] array (
    'queryString' => 'select d.Type as `Type`, d.InsertUserID as `FirstUserID`, lc.Body as `LastUserComment`, d.DateInserted as `FirstDate`, d.CountBookmarks as `CountBookmarks`, d.Body as `Body`, d.Format as `Format`, d.DateLastComment as `LastDate`, d.LastCommentUserID as `LastUserID`, d.*, w.UserID as `WatchUserID`, w.DateLastViewed as `DateLastViewed`, w.Dismissed as `Dismissed`, w.Bookmarked as `Bookmarked`, w.CountComments as `CountCommentWatch`
    from GDN_Discussion d
    left join GDN_UserDiscussion w on d.DiscussionID = w.DiscussionID and w.UserID = 13
    where d.DiscussionID in (\'3\', \'8\')
    and coalesce(w.Dismissed, \'0\') = :coalescewDismissed0
    order by d.DateLastComment desc
    limit 15',
    )

    ???? :(
  • AoleeAolee Hobbyist & Coder ✭✭
    What vanilla version are u using?
  • AoleeAolee Hobbyist & Coder ✭✭
    edited October 2011
    i think based on the errror code u missed the "LEFT JOIN"

    add this code
    ->Join('Comment lc', 'd.LastCommentID = lc.CommentID', 'left') // Last comment
    after the line
    ->Join('User lcu', 'd.LastCommentUserID = lcu.UserID', 'left') // Last comment user

    that's why it's complaining for the field => lc.Body
  • This is correct now?
     public function DiscussionSummaryQuery($AdditionalFields = array(), $Join = TRUE) {
    // Verify permissions (restricting by category if necessary)
    if ($this->Watching)
    $Perms = CategoryModel::CategoryWatch();
    else
    $Perms = self::CategoryPermissions();
    if($Perms !== TRUE) {
    $this->SQL->WhereIn('d.CategoryID', $Perms);
    }

    // Buid main query
    $this->SQL
    ->Select('d.Type')
    ->Select('d.InsertUserID', '', 'FirstUserID')
    ->Select('lc.Body','','LastUserComment')
    ->Select('d.DateInserted', '', 'FirstDate')
    ->Select('d.CountBookmarks')

    ->Select('d.Body') // <-- Need these for rss!
    ->Select('d.Format') // <-- Need these for rss!
    ->Select('d.DateLastComment', '', 'LastDate')
    ->Select('d.LastCommentUserID', '', 'LastUserID')
    ->From('Discussion d');

    if ($Join) {
    $this->SQL
    ->Select('iu.Name', '', 'FirstName') // <-- Need these for rss!
    ->Select('iu.Photo', '', 'FirstPhoto')
    ->Select('iu.Email', '', 'FirstEmail')
    ->Join('User iu', 'd.InsertUserID = iu.UserID', 'left') // First comment author is also the discussion insertuserid

    ->Select('lcu.Name', '', 'LastName')
    ->Select('lcu.Photo', '', 'LastPhoto')
    ->Select('lcu.Email', '', 'LastEmail')
    ->Join('User lcu', 'd.LastCommentUserID = lcu.UserID', 'left') // Last comment user
    ->Join('Comment lc', 'd.LastCommentID = lc.CommentID', 'left') // Last comment
    ->Select('ca.Name', '', 'Category')
    ->Select('ca.UrlCode', '', 'CategoryUrlCode')
    ->Select('ca.PermissionCategoryID')
    ->Join('Category ca', 'd.CategoryID = ca.CategoryID', 'left'); // Category

    }
  • camocamo New
    edited October 2011
    Also, how to make bubble speech css to show inside bubble background not white, but transparent so my page background image can be seen?
    First time I uploaded your version, speech bubble showed, with background white. I added the buuble css to themes/mytheme/design/css but couldnt make it change???
  • AoleeAolee Hobbyist & Coder ✭✭
    edited October 2011
    have a different version from mine are you using 2.0.18b?

    mine is 2.0.17.8

    try moving the
    ->Select('lc.Body','','LastUserComment')
    below
    ->Select('lcu.Photo', '', 'LastPhoto')
    ->Select('lc.Body','','LastUserComment')

  • camocamo New
    edited October 2011
    Im using 2.0.18b4 upgraded to RC2 Ill try that. How to change the bubble css too?
  • Im using RC2 Ill try that.

    Ok: This shows excerpt but not last comment or photo still, but atleast no fatal error.

    ??? Cant change bubble css either
  • AoleeAolee Hobbyist & Coder ✭✭
    edited October 2011
    for the bubble i admit im not good at css just tweaked the code from this example/article

    http://nicolasgallagher.com/pure-css-speech-bubbles/demo/

    and if you want to copy my css code its in

    http://www.pinoyau.info/applications/dashboard/design/style.css

  • AoleeAolee Hobbyist & Coder ✭✭
    edited October 2011
    view source your homepage and check the path where the main style.css of your theme is located.
  • veiw source main page css says

    /forum/plugins/Minify/min/?b=forum&token=10fcb15fd759198c57c9c9682b5ee396.css

    ???? what the?
  • AoleeAolee Hobbyist & Coder ✭✭
    can you provide the url to your site?

    btw, did you download my version of the discussionexcerpt plugin?
  • camocamo New
    edited October 2011
    Yes I downloaded your version, tried the changes you suggested, last try, the excerpt appeared, but was still first comment and first photo.

    Have yours saved with changes you suggested here

    http://vanillaforums.org/discussion/comment/145161#Comment_145161

    Now have reverted to original plugin, but want to get the speech bubble happening.

    In the plugin default.php < div class=bubble speech etc) is already in place, so if i insert the css in the right place, I should get the bubble yes?

    here is link (original plugin current)

    http://www.camosreptiles.com.au/forum/

    If you want me reinstall your version, I can, Ill just upload my ready altered

    class.discussionmodel.php and away we go
  • camocamo New
    edited October 2011
    5 minutes please, setting up your version again so you can see ok

    @Aolee ok now your version in place but not working
  • camocamo New
    edited October 2011
    No, im doing all this on my actual site. Ive followed all aolee's instructions to the letter and I have shown here what ive done. I have no idea what your saying about debugging sql queries, none whatsoever, its all chinese to me.
    If you want me to just leave this be, just say so and I will revert to the original plugin.

    Everything in place, here is classdiscussionmodel.php

    still dosent work

    http://www.camosreptiles.com.au/forum/index.php?p=/
       public function DiscussionSummaryQuery($AdditionalFields = array(), $Join = TRUE) {
    // Verify permissions (restricting by category if necessary)
    if ($this->Watching)
    $Perms = CategoryModel::CategoryWatch();
    else
    $Perms = self::CategoryPermissions();
    if($Perms !== TRUE) {
    $this->SQL->WhereIn('d.CategoryID', $Perms);
    }

    // Buid main query
    $this->SQL
    ->Select('d.Type')
    ->Select('d.InsertUserID', '', 'FirstUserID')
    ->Select('d.DateInserted', '', 'FirstDate')
    ->Select('d.CountBookmarks')

    ->Select('d.Body') // <-- Need these for rss!
    ->Select('d.Format') // <-- Need these for rss!
    ->Select('d.DateLastComment', '', 'LastDate')
    ->Select('d.LastCommentUserID', '', 'LastUserID')
    ->From('Discussion d');

    if ($Join) {
    $this->SQL
    ->Select('iu.Name', '', 'FirstName') // <-- Need these for rss!
    ->Select('iu.Photo', '', 'FirstPhoto')
    ->Select('iu.Email', '', 'FirstEmail')
    ->Join('User iu', 'd.InsertUserID = iu.UserID', 'left') // First comment author is also the discussion insertuserid

    ->Select('lcu.Name', '', 'LastName')
    ->Select('lcu.Photo', '', 'LastPhoto')
    >Select('lc.Body','','LastUserComment')
    ->Select('lcu.Email', '', 'LastEmail')
    ->Join('User lcu', 'd.LastCommentUserID = lcu.UserID', 'left') // Last comment user
    ->Join('Comment lc', 'd.LastCommentID = lc.CommentID', 'left') // Last comment
    ->Select('ca.Name', '', 'Category')
    ->Select('ca.UrlCode', '', 'CategoryUrlCode')
    ->Select('ca.PermissionCategoryID')
    ->Join('Category ca', 'd.CategoryID = ca.CategoryID', 'left'); // Category

    }
  • Nevermind, Underdog is getting Anal again!

    @Aolee, thanks for your time.
  • AoleeAolee Hobbyist & Coder ✭✭
    edited October 2011
    ok i got it. use this


    public function DiscussionSummaryQuery($AdditionalFields = array(), $Join = TRUE) {
    // Verify permissions (restricting by category if necessary)
    if ($this->Watching)
    $Perms = CategoryModel::CategoryWatch();
    else
    $Perms = self::CategoryPermissions();
    if($Perms !== TRUE) {
    $this->SQL->WhereIn('d.CategoryID', $Perms);
    }

    // Buid main query
    $this->SQL
    ->Select('d.Type')
    ->Select('d.InsertUserID', '', 'FirstUserID')
    ->Select('d.DateInserted', '', 'FirstDate')
    ->Select('d.CountBookmarks')

    ->Select('d.Body') // <-- Need these for rss!
    ->Select('d.Format') // <-- Need these for rss!
    ->Select('d.Format') // <-- Need these for rss!
    ->Select('lc.Body','','LastUserComment')
    ->Select('d.DateLastComment', '', 'LastDate')
    ->Select('d.LastCommentUserID', '', 'LastUserID')
    ->From('Discussion d')
    ->Join('Comment lc', 'd.LastCommentID = lc.CommentID', 'left');

    if ($Join) {
    $this->SQL
    ->Select('iu.Name', '', 'FirstName') // <-- Need these for rss!
    ->Select('iu.Photo', '', 'FirstPhoto')
    ->Select('iu.Email', '', 'FirstEmail')
    ->Join('User iu', 'd.InsertUserID = iu.UserID', 'left') // First comment author is also the discussion insertuserid

    ->Select('lcu.Name', '', 'LastName')
    ->Select('lcu.Photo', '', 'LastPhoto')
    ->Select('lcu.Email', '', 'LastEmail')
    ->Join('User lcu', 'd.LastCommentUserID = lcu.UserID', 'left') // Last comment user

    ->Select('ca.Name', '', 'Category')
    ->Select('ca.UrlCode', '', 'CategoryUrlCode')
    ->Select('ca.PermissionCategoryID')
    ->Join('Category ca', 'd.CategoryID = ca.CategoryID', 'left'); // Category

    }

    // Add any additional fields that were requested
    if(is_array($AdditionalFields)) {
    foreach($AdditionalFields as $Alias => $Field) {
    // See if a new table needs to be joined to the query.
    $TableAlias = explode('.', $Field);
    $TableAlias = $TableAlias[0];
    if(array_key_exists($TableAlias, $Tables)) {
    $Join = $Tables[$TableAlias];
    $this->SQL->Join($Join[0], $Join[1]);
    unset($Tables[$TableAlias]);
    }

    // Select the field.
    $this->SQL->Select($Field, '', is_numeric($Alias) ? '' : $Alias);
    }
    }

    $this->FireEvent('AfterDiscussionSummaryQuery');
    }



    Actually UnderDog just want you to play more with vanilla code so you will be more familiar with it :) cheers!
  • :) vanilla code makes my head hurt! Thankyou Aolee, I will try this now.
  • camocamo New
    edited October 2011
    OK its now showing last comment but not last photo.

    http://www.camosreptiles.com.au/forum/
  • AoleeAolee Hobbyist & Coder ✭✭
    edited October 2011
    sorry that is a different one, i think you're using lincoln's IndexPhotos plugin, not sure how to tweak that one. mine is a direct hack again ^^ using an old vanilla version
  • camocamo New
    edited October 2011
    Oh ofcourse silly me. Thankyou so much for your time and patience, its working very well now! :)
    Thankyou Thankyou Aolee!

    Edit: I got that one tweaked, very easy! Woohoo! :)
  • camocamo New
    edited October 2011
    @Aolee

    was just testing possible conflicts between this plugin (your core hack)
    http://vanillaforums.org/discussion/comment/145182#Comment_145182

    and feed discussions, where p[ost in feeddiscussions were being truncated.

    I removed this file amnd uploaded the original from RC2 and did utility update and it still works! E.G still shows last comment in the excerpt. Strange no? :)
«1
Sign In or Register to comment.