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.

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

edited December 2010 in Vanilla 2.0 - 2.8
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:

d.DateInserted AS FirstDate,
d.DateLastComment AS LastDate,
d.InsertUserID AS FirstUserID,
d.LastCommentUserID AS LastUserID,
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

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

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

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.


  • Options
    edited December 2010
  • Options
    What is the intention of the "group by" clause? You are not using any grouping functions to make use of it.
  • Options
    edited December 2010
    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.
Sign In or Register to comment.