NEW posts query: works outside SQL handler but not within it

Familiar

Hey guys,

I've been writing some updates to my forum, and wanted to create a feature that allowed users to filter by new posts.

I did this, but it necessitated a work-around since the query didn't want to function within the SQL handler.

The query is as follows:

SELECT
d.Body,
d.CountBookmarks,
d.CountComments,
d.CountViews,
d.DateInserted AS FirstDate,
d.DateLastComment AS LastDate,
d.DiscussionID,
d.Format,
d.InsertUserID AS FirstUserID,
d.LastCommentUserID AS LastUserID,
d.Name,
lcu.Name AS LastName,
iu.Name AS FirstName,
iu.Photo AS FirstPhoto,
ca.UrlCode AS CategoryUrlCode,
CONCAT_WS(\' → \', pc.Name, ca.Name) AS Category
FROM GDN_Discussion d
INNER JOIN GDN_UserDiscussion u ON u.DiscussionID = d.DiscussionID
LEFT JOIN GDN_User iu ON d.InsertUserID = iu.UserID
LEFT JOIN GDN_User lcu ON d.LastCommentUserID = lcu.UserID
LEFT JOIN GDN_Category ca ON d.CategoryID = ca.CategoryID
LEFT JOIN GDN_Category pc ON ca.ParentCategoryID = pc.CategoryID
WHERE u.UserID = ' . $UserID . '
AND u.CountComments < d.CountComments
GROUP BY d.DiscussionID
LIMIT ' . $Offset . ', ' . $Limit

This DOES NOT work:

// Instantiate query
$Sender->DiscussionSummaryQuery();

// Modify query
Sender->SQL
->Select('d.*')
->Join('UserDiscussion u', 'd.DiscussionID = u.DiscussionID')
->Where('u.UserID', $UserID)
->Where('u.CountComments <', 'd.CountComments')

->GroupBy('d.DiscussionID')
->OrderBy('d.DiscussionID', 'desc')
->Limit($Limit, $Offset)
->Get();

As you can see, I bypassed the handlers you have in place for Discussions and sent my request through as a standard MySQL query.

I'd be happy to release this as a plug-in but only once I can figure out why the Garden framework isn't accepting the correct method. The results are consistently empty.

I also modified the Mark All Viewed plug-in so that it updates the database correctly for each Discussion a user has read instead of just resetting all their counters to 0. In combination with the [ NEW ] filter, the modified plug-in works nicely. The last step here is to link the NEW notifier to the last post the user as read in the Discussion in question, but I'm still trying to figure out the most efficient way to do that.

Familiar

Nvm

judgej

What is the intention of the "group by" clause? You are not using any grouping functions to make use of it.

Familiar

I felt the same way but I was duplicating the standard Vanilla functionality (as per the Participated Discussion plug-in). It's just an order by in the final code I wrote.