[Solved] how to show the most recent comment?

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?

Aolee

nevermind i got it! :)

camo

Please share your solution

Aolee

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.

UnderDog

Aolee said:

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.

camo

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!

Aolee

@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.

Aolee

@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.

camo

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',
)

???? :(

Aolee

What vanilla version are u using?

Aolee

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

camo

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!<br />         ->Select('d.Format') // <-- Need these for rss!<br />         ->Select('d.DateLastComment', '', 'LastDate')
         ->Select('d.LastCommentUserID', '', 'LastUserID')
         ->From('Discussion d');
      
      if ($Join) {
         $this->SQL
            ->Select('iu.Name', '', 'FirstName') // <-- Need these for rss!<br />            ->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
         
      }

camo

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???

Aolee

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')

camo

Im using 2.0.18b4 upgraded to RC2 Ill try that. How to change the bubble css too?

camo

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

Aolee

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

Aolee

view source your homepage and check the path where the main style.css of your theme is located.

camo

veiw source main page css says

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

???? what the?

Aolee

can you provide the url to your site?

btw, did you download my version of the discussionexcerpt plugin?

camo

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

camo

5 minutes please, setting up your version again so you can see ok

@Aolee ok now your version in place but not working

UnderDog

camo said:

How to change the bubble css too?

@camo you need to figure out some stuff by yourself too man, Aolee did plenty by showing us guides and making plugins. Try something, if fails, try again, if then fails, you can say your tried (code) and tried (code) and then ask for some help.

Of course you're trying these things at your localhost, right? Good! Turn on the debug SQL queries (search on the forum how to do that) and try + try to figure out which SQL query is executed on your local forum to retrieve those excerpts (CTRL-F will get you somewhere in your firefox browser)
Then post that SQL that's executed and only the SQL you think is relevant.

camo

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!<br />         ->Select('d.Format') // <-- Need these for rss!<br />         ->Select('d.DateLastComment', '', 'LastDate')
         ->Select('d.LastCommentUserID', '', 'LastUserID')
         ->From('Discussion d');
      
      if ($Join) {
         $this->SQL
            ->Select('iu.Name', '', 'FirstName') // <-- Need these for rss!<br />            ->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
         
      }

camo

Nevermind, Underdog is getting Anal again!

@Aolee, thanks for your time.

Aolee

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!

camo

:) vanilla code makes my head hurt! Thankyou Aolee, I will try this now.

camo

OK its now showing last comment but not last photo.

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

Aolee

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

camo

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! :)

camo

@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? :)