LastCommentID

davidk43

Wondering if this is possible - I'm terrible at SqlBuilder.

I'm using the RSS2 add-on but would like to return in the join the most recent comment rather than the first comment. At present the code joins the first comment obviously based on the FirstCommentID field in the Discussion table like this:

function DiscussionManager_GetFirstCommentForRSS2($DiscussionManager) { $SqlBuilder = &$DiscussionManager->DelegateParameters['SqlBuilder']; $SqlBuilder->AddJoin('Comment', 'fc', 'CommentID', 't', 'FirstCommentID', 'left join'); $SqlBuilder->AddSelect(array('FormatType', 'Body'), 'fc'); }

How can this be tweaked to join the latest Comment content in that discussion rather than the first? I'm assuming its a sub query but I'm lost.

thanks for any help

Mark

Untested code below. Use at your own risk:
function DiscussionManager_GetFirstCommentForRSS2($DiscussionManager) { $SqlBuilder = &$DiscussionManager->DelegateParameters['SqlBuilder']; $SqlBuilder->AddJoin('Comment', 'lc', 'CommentID', 't', 'LastCommentID', 'left join'); $SqlBuilder->AddSelect(array('FormatType', 'Body'), 'lc'); }

davidk43

Thanks Mark, doesn't seem to work, but there isn't a LastCommentID field in the Discussion table to join on is there? Or has there been a table change between 1.1.5a to 1.1.6?

davidk43

Seriously, can this not be done? Can a subquery say like:

SELECT CommentID FROM `LUM_Comment` WHERE DiscussionID = XXX ORDER BY DateCreated DESC LIMIT 1
not be used to get the LastCommentID and then joined to the Discussion Manager? Mark seems to be referring to a 'LastCommentID' but I don't see that existing anywhere like 'FirstCommentID' exists in the Discussion table...?

Confused.