[Andrew] Moderator Panel 2.0.9

Abu Abdullah · Jul 27, 2024

On XF v2.2.13

Code:

ErrorException: Total Notes Found: 0 src/XF/Error.php:77
Generated by: username Jul 28, 2024 at 12:18 AM
Stack trace
#0 src/XF.php(219): XF\Error->logError('Total Notes Fou...', false)
#1 src/addons/Andrew/ModeratorPanel/XF/Entity/User.php(472): XF::logError('Total Notes Fou...')
#2 src/addons/Andrew/ModeratorPanel/XF/Entity/User.php(439): Andrew\ModeratorPanel\XF\Entity\User->getCountForUsableCategories(Object(Datio\AllowedEmails\XF\Entity\User), 4508)
#3 src/XF/Template/Templater.php(1194): Andrew\ModeratorPanel\XF\Entity\User->getUserNoteCount()
#4 internal_data/code_cache/templates/l1/s26/public/member_macros.php(231): XF\Template\Templater->method(Object(Datio\AllowedEmails\XF\Entity\User), 'getUserNoteCoun...', Array)
#5 src/XF/Template/Templater.php(827): XF\Template\Templater->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, NULL)
#6 src/addons/MaZ/AMP/XF/Template/Templater.php(139): XF\Template\Templater->callMacro('member_macros', 'member_stat_pai...', Array, Array, Object(XF\Template\MacroState))
#7 internal_data/code_cache/templates/l1/s26/public/member_tooltip.php(174): MaZ\AMP\XF\Template\Templater->callMacro('member_macros', 'member_stat_pai...', Array, Array)
#8 src/XF/Template/Templater.php(1654): XF\Template\Templater->{closure}(Object(MaZ\AMP\XF\Template\XF22\Templater), Array, NULL)
#9 src/addons/MaZ/AMP/XF/Template/XF22/Templater.php(52): XF\Template\Templater->renderTemplate('member_tooltip', Array, true, NULL)
#10 src/XF/Template/Template.php(24): MaZ\AMP\XF\Template\XF22\Templater->renderTemplate('public:member_t...', Array)
#11 src/XF/Mvc/Renderer/Json.php(86): XF\Template\Template->render()
#12 src/XF/Mvc/Renderer/Json.php(70): XF\Mvc\Renderer\Json->renderHtmlFallback('XF:Member\\Toolt...', 'public:member_t...', Array)
#13 src/XF/Mvc/Dispatcher.php(460): XF\Mvc\Renderer\Json->renderView('XF:Member\\Toolt...', 'public:member_t...', Array)
#14 src/XF/Mvc/Dispatcher.php(442): XF\Mvc\Dispatcher->renderView(Object(KL\EditorManager\XF\Mvc\Renderer\Json), Object(XF\Mvc\Reply\View))
#15 src/XF/Mvc/Dispatcher.php(402): XF\Mvc\Dispatcher->renderReply(Object(KL\EditorManager\XF\Mvc\Renderer\Json), Object(XF\Mvc\Reply\View))
#16 src/XF/Mvc/Dispatcher.php(60): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'json')
#17 src/XF/App.php(2487): XF\Mvc\Dispatcher->run()
#18 src/XF.php(524): XF\App->run()
#19 index.php(20): XF::runApp('XF\\Pub\\App')
#20 {main}
Request state
array(4) {
  ["url"] => string(148) "/members/***-***.4508/?tooltip=true&_xfRequestUri=%2F&_xfWithData=1&_xfToken=1722104137%2C26c922f2fddba2c8ac9e3f2e086ce2fa&_xfResponseType=json"
  ["referrer"] => string(24) "https://domain.com/"
  ["_GET"] => array(5) {
    ["tooltip"] => string(4) "true"
    ["_xfRequestUri"] => string(1) "/"
    ["_xfWithData"] => string(1) "1"
    ["_xfToken"] => string(43) "1722104137,26c922f2fddba2c8ac9e3f2e086ce2fa"
    ["_xfResponseType"] => string(4) "json"
  }
  ["_POST"] => array(0) {
  }
}
Delete

Velatic · Jul 27, 2024

LogicException: Unknown relation or alias xf_andrew_mp_user_note accessed on xf_andrew_mp_user_note in src/XF/Mvc/Entity/Finder.php at line 790

XF\Mvc\Entity\Finder->join() in src/XF/Mvc/Entity/Finder.php at line 1827
XF\Mvc\Entity\Finder->resolveFieldToTableAndColumn() in src/XF/Mvc/Entity/Finder.php at line 623
XF\Mvc\Entity\Finder->columnSqlName() in src/XF/Mvc/Entity/Finder.php at line 287
XF\Mvc\Entity\Finder->buildCondition() in src/XF/Mvc/Entity/Finder.php at line 151
XF\Mvc\Entity\Finder->where() in src/addons/Andrew/ModeratorPanel/Repository/UserNote.php at line 38
Andrew\ModeratorPanel\Repository\UserNote->findUserNote() in src/addons/Andrew/ModeratorPanel/Pub/Controller/Dashboard.php at line 161
Andrew\ModeratorPanel\Pub\Controller\Dashboard->getRecentUserNotes() in src/addons/Andrew/ModeratorPanel/Pub/Controller/Dashboard.php at line 52
Andrew\ModeratorPanel\Pub\Controller\Dashboard->actionIndex() in src/XF/Mvc/Dispatcher.php at line 362
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 264
XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 121
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 63
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2777
XF\App->run() in src/XF.php at line 798
XF::runApp() in index.php at line 23

javilonas · Jul 27, 2024

For anyone who may find this helpful while they come up with an official solution:

I resolved the issue by editing /src/addons/Andrew/ModeratorPanel/Repository/UserNote.php.

Replace:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = $finder->expression("FIND_IN_SET(xf_andrew_mp_user_note.note_category_id, " . $usableCategoryIdsString . ") OR xf_andrew_mp_user_note.note_category_id = 0");
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('xf_andrew_mp_user_note.note_category_id', 0);
}

With:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = "FIND_IN_SET(note_category_id, '$usableCategoryIdsString') OR note_category_id = 0";
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('note_category_id', 0);
}

And also replace further down:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = $finder->expression("FIND_IN_SET(xf_andrew_mp_user_note.note_category_id, " . $usableCategoryIdsString . ") OR xf_andrew_mp_user_note.note_category_id = 0");
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('xf_andrew_mp_user_note.note_category_id', 0);
}

With:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = "FIND_IN_SET(note_category_id, '$usableCategoryIdsString') OR note_category_id = 0";
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('note_category_id', 0);
}

Then in /src/addons/Andrew/ModeratorPanel/XF/Entity/User.php:

Find the function protected function getCountForUsableCategories($visitor, $userId) :

PHP:

protected function getCountForUsableCategories($visitor, $userId)
{
    // Fetch all categories and filter them based on usability by the visitor
    $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
    $categories = $categoryFinder->fetch();

    $usableCategoryIds = [];
    foreach ($categories as $category) {
        if ($category->isUsableByUser($visitor)) {
            $usableCategoryIds[] = $category->note_category_id;
        }
    }

    $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

    $noteFinder->where('note_user_id', $userId);

    if (!empty($usableCategoryIds)) {
        $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
        $inSetExpression = $noteFinder->expression("FIND_IN_SET(xf_andrew_mp_user_note.note_category_id, " . $usableCategoryIdsString . ") OR xf_andrew_mp_user_note.note_category_id = 0");
        $noteFinder->where($inSetExpression);
    } else {
        $noteFinder->where('note_category_id', '=', 0);
    }

    $total = $noteFinder->total();
    \XF::logError("Total Notes Found: $total");

    return $total;
}

and replace it entirely with:

PHP:

protected function getCountForUsableCategories($visitor, $userId)
{
    // Fetch all categories and filter them based on usability by the visitor
    $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
    $categories = $categoryFinder->fetch();

    $usableCategoryIds = [];
    foreach ($categories as $category) {
        if ($category->isUsableByUser($visitor)) {
            $usableCategoryIds[] = $category->note_category_id;
        }
    }

    // If there are no usable categories, return 0 immediately
    if (empty($usableCategoryIds)) {
        return 0;
    }

    $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

    $noteFinder->where('note_user_id', $userId);

    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));

    // Use IN to improve efficiency
    $noteFinder->where('note_category_id', 'IN', $usableCategoryIdsString . ', 0');

    $total = $noteFinder->total();
    \XF::logError("Total Notes Found: $total");

    return $total;
}

The changes made optimize the query for notes based on usable categories, making it more efficient and less prone to errors. These adjustments not only enhance the efficiency of the code but also ensure that the query is executed more directly and securely.

SQL Code Optimization: The use of SQL expressions has been simplified to make the query clearer and more efficient.
Improved Readability: The new implementation is easier to understand and maintain.
Validation and Security: Explicit conversion of IDs to integers helps prevent SQL injections.

This fix worked for XenForo 2.2.15/16, 2.3.

lshyrilser · Jul 27, 2024

javilonas said:

For anyone who may find this helpful while they come up with an official solution:

I resolved the issue by editing /src/addons/Andrew/ModeratorPanel/Repository/UserNote.php.

Replace:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = $finder->expression("FIND_IN_SET(xf_andrew_mp_user_note.note_category_id, " . $usableCategoryIdsString . ") OR xf_andrew_mp_user_note.note_category_id = 0");
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('xf_andrew_mp_user_note.note_category_id', 0);
}

With:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = "FIND_IN_SET(note_category_id, '$usableCategoryIdsString') OR note_category_id = 0";
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('note_category_id', 0);
}

And also replace further down:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = $finder->expression("FIND_IN_SET(xf_andrew_mp_user_note.note_category_id, " . $usableCategoryIdsString . ") OR xf_andrew_mp_user_note.note_category_id = 0");
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('xf_andrew_mp_user_note.note_category_id', 0);
}

With:

PHP:

if (!empty($usableCategoryIds)) {
    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
    $inSetExpression = "FIND_IN_SET(note_category_id, '$usableCategoryIdsString') OR note_category_id = 0";
    $finder->where($inSetExpression);
} else {
    // If no usable categories, only include notes with no category
    $finder->where('note_category_id', 0);
}

Then in /src/addons/Andrew/ModeratorPanel/XF/Entity/User.php:

Find the function protected function getCountForUsableCategories($visitor, $userId) :

PHP:

protected function getCountForUsableCategories($visitor, $userId)
{
    // Fetch all categories and filter them based on usability by the visitor
    $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
    $categories = $categoryFinder->fetch();

    $usableCategoryIds = [];
    foreach ($categories as $category) {
        if ($category->isUsableByUser($visitor)) {
            $usableCategoryIds[] = $category->note_category_id;
        }
    }

    $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

    $noteFinder->where('note_user_id', $userId);

    if (!empty($usableCategoryIds)) {
        $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
        $inSetExpression = $noteFinder->expression("FIND_IN_SET(xf_andrew_mp_user_note.note_category_id, " . $usableCategoryIdsString . ") OR xf_andrew_mp_user_note.note_category_id = 0");
        $noteFinder->where($inSetExpression);
    } else {
        $noteFinder->where('note_category_id', '=', 0);
    }

    $total = $noteFinder->total();
    \XF::logError("Total Notes Found: $total");

    return $total;
}

and replace it entirely with:

PHP:

protected function getCountForUsableCategories($visitor, $userId)
{
    // Fetch all categories and filter them based on usability by the visitor
    $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
    $categories = $categoryFinder->fetch();

    $usableCategoryIds = [];
    foreach ($categories as $category) {
        if ($category->isUsableByUser($visitor)) {
            $usableCategoryIds[] = $category->note_category_id;
        }
    }

    // If there are no usable categories, return 0 immediately
    if (empty($usableCategoryIds)) {
        return 0;
    }

    $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

    $noteFinder->where('note_user_id', $userId);

    $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));

    // Use IN to improve efficiency
    $noteFinder->where('note_category_id', 'IN', $usableCategoryIdsString . ', 0');

    $total = $noteFinder->total();
    \XF::logError("Total Notes Found: $total");

    return $total;
}

The changes made optimize the query for notes based on usable categories, making it more efficient and less prone to errors. These adjustments not only enhance the efficiency of the code but also ensure that the query is executed more directly and securely.

SQL Code Optimization: The use of SQL expressions has been simplified to make the query clearer and more efficient.
Improved Readability: The new implementation is easier to understand and maintain.
Validation and Security: Explicit conversion of IDs to integers helps prevent SQL injections.

This fix worked for XenForo 2.2.15/16, but I’m not sure if it will work the same way for version 2.3.

This fix works flawlessly on XF 2.3

Ambient Vibe · Jul 27, 2024

Newest update gives me errrors.

Front end:

Code:

LogicException: Unknown relation or alias xf_andrew_mp_user_note accessed on xf_andrew_mp_user_note in src/XF/Mvc/Entity/Finder.php at line 790
XF\Mvc\Entity\Finder->join() in src/XF/Mvc/Entity/Finder.php at line 1827
XF\Mvc\Entity\Finder->resolveFieldToTableAndColumn() in src/XF/Mvc/Entity/Finder.php at line 623
XF\Mvc\Entity\Finder->columnSqlName() in src/XF/Mvc/Entity/Finder.php at line 287
XF\Mvc\Entity\Finder->buildCondition() in src/XF/Mvc/Entity/Finder.php at line 151
XF\Mvc\Entity\Finder->where() in src/addons/Andrew/ModeratorPanel/Repository/UserNote.php at line 38
Andrew\ModeratorPanel\Repository\UserNote->findUserNote() in src/addons/Andrew/ModeratorPanel/Pub/Controller/Dashboard.php at line 161
Andrew\ModeratorPanel\Pub\Controller\Dashboard->getRecentUserNotes() in src/addons/Andrew/ModeratorPanel/Pub/Controller/Dashboard.php at line 52
Andrew\ModeratorPanel\Pub\Controller\Dashboard->actionIndex() in src/XF/Mvc/Dispatcher.php at line 362
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 264
XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 121
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 63
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2777
XF\App->run() in src/XF.php at line 798
XF::runApp() in index.php at line 23

Back end:

Code:

LogicException: Unknown relation or alias xf_andrew_mp_user_note accessed on xf_andrew_mp_user_note src/XF/Mvc/Entity/Finder.php:790
Generated by: Raine Jul 27, 2024 at 3:36 PM
Stack trace
#0 src/XF/Mvc/Entity/Finder.php(1827): XF\Mvc\Entity\Finder->join('xf_andrew_mp_us...', false, true)
#1 src/XF/Mvc/Entity/Finder.php(623): XF\Mvc\Entity\Finder->resolveFieldToTableAndColumn('xf_andrew_mp_us...', true)
#2 src/XF/Mvc/Entity/Finder.php(287): XF\Mvc\Entity\Finder->columnSqlName('xf_andrew_mp_us...', true)
#3 src/XF/Mvc/Entity/Finder.php(151): XF\Mvc\Entity\Finder->buildCondition('xf_andrew_mp_us...', 0)
#4 src/addons/Andrew/ModeratorPanel/Repository/UserNote.php(38): XF\Mvc\Entity\Finder->where('xf_andrew_mp_us...', 0)
#5 src/addons/Andrew/ModeratorPanel/Pub/Controller/Dashboard.php(161): Andrew\ModeratorPanel\Repository\UserNote->findUserNote()
#6 src/addons/Andrew/ModeratorPanel/Pub/Controller/Dashboard.php(52): Andrew\ModeratorPanel\Pub\Controller\Dashboard->getRecentUserNotes()
#7 src/XF/Mvc/Dispatcher.php(362): Andrew\ModeratorPanel\Pub\Controller\Dashboard->actionIndex(Object(XF\Mvc\ParameterBag))
#8 src/XF/Mvc/Dispatcher.php(264): XF\Mvc\Dispatcher->dispatchClass('Andrew\\Moderato...', 'Index', Object(XF\Mvc\RouteMatch), Object(Andrew\ModeratorPanel\Pub\Controller\Dashboard), Object(XF\Mvc\Reply\Reroute))
#9 src/XF/Mvc/Dispatcher.php(121): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(Andrew\ModeratorPanel\Pub\Controller\Dashboard), Object(XF\Mvc\Reply\Reroute))
#10 src/XF/Mvc/Dispatcher.php(63): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#11 src/XF/App.php(2777): XF\Mvc\Dispatcher->run()
#12 src/XF.php(798): XF\App->run()
#13 index.php(23): XF::runApp('XF\\Pub\\App')
#14 {main}

Code:

Request state
array(4) {
  ["url"] => string(36) "/swodyssey/index.php?moderatorpanel/"
  ["referrer"] => string(63) "https://www.ambientvibe.com/swodyssey/index.php?approval-queue/"
  ["_GET"] => array(1) {
    ["moderatorpanel/"] => string(0) ""
  }
  ["_POST"] => array(0) {
  }
}

And:

Code:

ErrorException: Total Notes Found: 0 src/XF/Error.php:82
Generated by: Matthias Jul 27, 2024 at 3:10 PM
Stack trace
#0 src/XF.php(257): XF\Error->logError('Total Notes Fou...', false)
#1 src/addons/Andrew/ModeratorPanel/XF/Entity/User.php(472): XF::logError('Total Notes Fou...')
#2 src/addons/Andrew/ModeratorPanel/XF/Entity/User.php(439): Andrew\ModeratorPanel\XF\Entity\User->getCountForUsableCategories(Object(MMO\VerifiedBadge\XF\Entity\User), 3)
#3 src/XF/Template/Templater.php(1283): Andrew\ModeratorPanel\XF\Entity\User->getUserNoteCount()
#4 internal_data/code_cache/templates/l1/s246/public/member_macros.php(248): XF\Template\Templater->method(Object(MMO\VerifiedBadge\XF\Entity\User), 'getUserNoteCoun...', Array)
#5 src/XF/Template/Templater.php(922): XF\Template\Templater->{closure}(Object(MMO\VerifiedBadge\XF\Template\Templater), Array, NULL)
#6 internal_data/code_cache/templates/l1/s246/public/member_tooltip.php(166): XF\Template\Templater->callMacro('member_macros', 'member_stat_pai...', Array, Array)
#7 src/XF/Template/Templater.php(1792): XF\Template\Templater->{closure}(Object(MMO\VerifiedBadge\XF\Template\Templater), Array, NULL)
#8 src/XF/Template/Template.php(24): XF\Template\Templater->renderTemplate('member_tooltip', Array)
#9 src/XF/Mvc/Renderer/Json.php(86): XF\Template\Template->render()
#10 src/XF/Mvc/Renderer/Json.php(70): XF\Mvc\Renderer\Json->renderHtmlFallback('XF:Member\\Toolt...', 'public:member_t...', Array)
#11 src/XF/Mvc/Dispatcher.php(471): XF\Mvc\Renderer\Json->renderView('XF:Member\\Toolt...', 'public:member_t...', Array)
#12 src/XF/Mvc/Dispatcher.php(453): XF\Mvc\Dispatcher->renderView(Object(XF\Mvc\Renderer\Json), Object(XF\Mvc\Reply\View))
#13 src/XF/Mvc/Dispatcher.php(412): XF\Mvc\Dispatcher->renderReply(Object(XF\Mvc\Renderer\Json), Object(XF\Mvc\Reply\View))
#14 src/XF/Mvc/Dispatcher.php(66): XF\Mvc\Dispatcher->render(Object(XF\Mvc\Reply\View), 'json')
#15 src/XF/App.php(2777): XF\Mvc\Dispatcher->run()
#16 src/XF.php(798): XF\App->run()
#17 index.php(23): XF::runApp('XF\\Pub\\App')
#18 {main}

Code:

Request state
array(4) {
  ["url"] => string(183) "/swodyssey/index.php?members/lana-star.3/&tooltip=true&_xfRequestUri=%2Fswodyssey%2Findex.php&_xfWithData=1&_xfToken=1722111053%2C4c40bb192dd3aad096c8ebe74bbb1a87&_xfResponseType=json"
  ["referrer"] => string(43) "https://ambientvibe.com/swodyssey/index.php"
  ["_GET"] => array(6) {
    ["members/lana-star_3/"] => string(0) ""
    ["tooltip"] => string(4) "true"
    ["_xfRequestUri"] => string(20) "/swodyssey/index.php"
    ["_xfWithData"] => string(1) "1"
    ["_xfToken"] => string(43) "1722111053,4c40bb192dd3aad096c8ebe74bbb1a87"
    ["_xfResponseType"] => string(4) "json"
  }
  ["_POST"] => array(0) {
  }
}

AndrewSimm · Jul 28, 2024

What's interesting is the original code isn't producing any errors on my local MAMP server but does produce errors when I update the code. When I install on a prod site then I do get the errors. The only thing I can think would be different is the version of MySQL.

AndrewSimm · Jul 28, 2024

Here is the error:

Code:

public:member_macros :: member_stat_pairs() - Operator IN is not valid in /Users/andrewsimm/Sites/xenbyandrew/src/XF/Mvc/Entity/Finder.php:314

javilonas · Jul 28, 2024

AndrewSimm said:

The problem seems to be related to how the IN operator is handled in different versions of MySQL.
Maybe splitting the query into two parts can solve the problem. First, using the IN and then adding the OR.

I'll try a couple of changes and let you know if it still works the same way in a few minutes.

AndrewSimm · Jul 28, 2024

javilonas said:
The problem seems to be related to how the IN operator is handled in different versions of MySQL.
Maybe splitting the query into two parts can solve the problem. First, using the IN and then adding the OR.

I'll try a couple of changes and let you know if it still works the same way in a few minutes.

This seems to work on my MAMP and 2 live sites.

PHP:

   protected function getCountForUsableCategories($visitor, $userId)
    {
        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

        $noteFinder->where('note_user_id', $userId);

        if (!empty($usableCategoryIds)) {
            $noteFinder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $noteFinder->where('note_category_id', 0);
        }

        return $noteFinder->total();
    }

PHP:

  public function findUserNote()
    {
        $visitor = \XF::visitor();
        $finder = $this->finder('Andrew\ModeratorPanel:UserNote')
            ->with('Category');
        $finder->setDefaultOrder('note_id', 'DESC');

        if (!$visitor->canViewPrivilegedUserNotes()) {
            $finder->where('is_privileged', false);
        }

        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        if (!empty($usableCategoryIds)) {
            $finder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $finder->where('note_category_id', 0);
        }

        return $finder;
    }

PHP:

  public function fetchUserNotes($visitor, $username = null, $givenBy = null, $isPrivileged = null, $category = null, $page = 1, $perPage = 20, $sortby = 'date', $order = 'desc')
    {
        $finder = $this->finder('Andrew\ModeratorPanel:UserNote')
            ->with('User')
            ->with('Category')
            ->limitByPage($page, $perPage);

        if ($username) {
            $finder->where('NoteUser.username', $username);
        }

        if ($givenBy) {
            $finder->where('User.username', $givenBy);
        }
        if ($isPrivileged) {
            $finder->where('is_privileged', true);
        }

        if ($category) {
            $finder->where('Category.title', $category);
        }

        if (!$visitor->canViewPrivilegedUserNotes()) {
            $finder->where('is_privileged', false);
        }

        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        if (!empty($usableCategoryIds)) {
            $finder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $finder->where('note_category_id', 0);
        }

        switch ($sortby) {
            case 'given_to':
                $finder->order('NoteUser.username', $order == 'asc' ? 'ASC' : 'DESC');
                break;
            case 'created_by':
                $finder->order('User.username', $order == 'asc' ? 'ASC' : 'DESC');
                break;
            case 'date':
            default:
                $finder->order('create_date', $order == 'asc' ? 'ASC' : 'DESC');
                break;
        }

        return $finder;
    }

javilonas · Jul 28, 2024

AndrewSimm said:

This seems to work on my MAMP and 2 live sites.

PHP:

   protected function getCountForUsableCategories($visitor, $userId)
    {
        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

        $noteFinder->where('note_user_id', $userId);

        if (!empty($usableCategoryIds)) {
            $noteFinder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $noteFinder->where('note_category_id', 0);
        }

        return $noteFinder->total();
    }

PHP:

  public function findUserNote()
    {
        $visitor = \XF::visitor();
        $finder = $this->finder('Andrew\ModeratorPanel:UserNote')
            ->with('Category');
        $finder->setDefaultOrder('note_id', 'DESC');

        if (!$visitor->canViewPrivilegedUserNotes()) {
            $finder->where('is_privileged', false);
        }

        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        if (!empty($usableCategoryIds)) {
            $finder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $finder->where('note_category_id', 0);
        }

        return $finder;
    }

PHP:

  public function fetchUserNotes($visitor, $username = null, $givenBy = null, $isPrivileged = null, $category = null, $page = 1, $perPage = 20, $sortby = 'date', $order = 'desc')
    {
        $finder = $this->finder('Andrew\ModeratorPanel:UserNote')
            ->with('User')
            ->with('Category')
            ->limitByPage($page, $perPage);

        if ($username) {
            $finder->where('NoteUser.username', $username);
        }

        if ($givenBy) {
            $finder->where('User.username', $givenBy);
        }
        if ($isPrivileged) {
            $finder->where('is_privileged', true);
        }

        if ($category) {
            $finder->where('Category.title', $category);
        }

        if (!$visitor->canViewPrivilegedUserNotes()) {
            $finder->where('is_privileged', false);
        }

        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        if (!empty($usableCategoryIds)) {
            $finder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $finder->where('note_category_id', 0);
        }

        switch ($sortby) {
            case 'given_to':
                $finder->order('NoteUser.username', $order == 'asc' ? 'ASC' : 'DESC');
                break;
            case 'created_by':
                $finder->order('User.username', $order == 'asc' ? 'ASC' : 'DESC');
                break;
            case 'date':
            default:
                $finder->order('create_date', $order == 'asc' ? 'ASC' : 'DESC');
                break;
        }

        return $finder;
    }

This also works for me and I suppose it will for you or other versions of MySQL. Everything as I mentioned in my other post, but in the protected function getCountForUsableCategories($visitor, $userId) of /src/addons/Andrew/ModeratorPanel/XF/Entity/User.php:
Leave this:

PHP:

    protected function getCountForUsableCategories($visitor, $userId)
    {
        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        // If there are no usable categories, return 0 immediately
        if (empty($usableCategoryIds)) {
            return 0;
        }

        $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

        $noteFinder->where('note_user_id', $userId);

        if (!empty($usableCategoryIds)) {
            $usableCategoryIdsString = implode(',', array_map('intval', $usableCategoryIds));
            // Using whereSql for a direct query
            $noteFinder->whereSql("note_category_id IN ($usableCategoryIdsString) OR note_category_id = 0");
        } else {
            // If no usable categories, only include notes with no category
            $noteFinder->where('note_category_id', 0);
        }

        $total = $noteFinder->total();
        \XF::logError("Total Notes Found: $total");

        return $total;
    }

I haven't tried what you suggest, but I can do it now too, give me a few minutes and I'll confirm your changes and see if I see any other errors with them.

javilonas · Jul 28, 2024

AndrewSimm said:

This seems to work on my MAMP and 2 live sites.

PHP:

   protected function getCountForUsableCategories($visitor, $userId)
    {
        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        $noteFinder = \XF::finder('Andrew\ModeratorPanel:UserNote');

        $noteFinder->where('note_user_id', $userId);

        if (!empty($usableCategoryIds)) {
            $noteFinder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $noteFinder->where('note_category_id', 0);
        }

        return $noteFinder->total();
    }

PHP:

  public function findUserNote()
    {
        $visitor = \XF::visitor();
        $finder = $this->finder('Andrew\ModeratorPanel:UserNote')
            ->with('Category');
        $finder->setDefaultOrder('note_id', 'DESC');

        if (!$visitor->canViewPrivilegedUserNotes()) {
            $finder->where('is_privileged', false);
        }

        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        if (!empty($usableCategoryIds)) {
            $finder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $finder->where('note_category_id', 0);
        }

        return $finder;
    }

PHP:

  public function fetchUserNotes($visitor, $username = null, $givenBy = null, $isPrivileged = null, $category = null, $page = 1, $perPage = 20, $sortby = 'date', $order = 'desc')
    {
        $finder = $this->finder('Andrew\ModeratorPanel:UserNote')
            ->with('User')
            ->with('Category')
            ->limitByPage($page, $perPage);

        if ($username) {
            $finder->where('NoteUser.username', $username);
        }

        if ($givenBy) {
            $finder->where('User.username', $givenBy);
        }
        if ($isPrivileged) {
            $finder->where('is_privileged', true);
        }

        if ($category) {
            $finder->where('Category.title', $category);
        }

        if (!$visitor->canViewPrivilegedUserNotes()) {
            $finder->where('is_privileged', false);
        }

        // Fetch all categories and filter them based on usability by the visitor
        $categoryFinder = \XF::finder('Andrew\ModeratorPanel:UserNoteCategory');
        $categories = $categoryFinder->fetch();

        $usableCategoryIds = [];
        foreach ($categories as $category) {
            if ($category->isUsableByUser($visitor)) {
                $usableCategoryIds[] = $category->note_category_id;
            }
        }

        if (!empty($usableCategoryIds)) {
            $finder->whereOr([
                ['note_category_id', 0],
                ['note_category_id', $usableCategoryIds]
            ]);
        } else {
            // If no usable categories, only include notes with no category
            $finder->where('note_category_id', 0);
        }

        switch ($sortby) {
            case 'given_to':
                $finder->order('NoteUser.username', $order == 'asc' ? 'ASC' : 'DESC');
                break;
            case 'created_by':
                $finder->order('User.username', $order == 'asc' ? 'ASC' : 'DESC');
                break;
            case 'date':
            default:
                $finder->order('create_date', $order == 'asc' ? 'ASC' : 'DESC');
                break;
        }

        return $finder;
    }

Confirmed, with the changes you mentioned, everything works perfectly for me as well.

AndrewSimm · Jul 28, 2024

AndrewSimm updated [Andrew] Moderator Panel with a new update entry:

Version 2.0.5

FIXED: Bug causes errors to appear when viewing user notes for certain versions of MySQL and MariaDB.
FIXED: Removed script that wrote number of notes to error log that was used for testing

Read the rest of this update entry...

AndrewSimm · Jul 28, 2024

Big thanks to @javilonas for helping test. I've also tested on XF 2.1 and 2.3 as well as MySQL 5.7 (MAMP) as well as MariaDB 10.6 and 10.4.

AndrewSimm · Jul 28, 2024

Mr Lucky said:
Does this fix the spam cleaner log issue I mentioned for 2.2.16?

I am going to get this fixed once I get home from vacation tomorrow.

AndrewSimm · Jul 31, 2024

@Mr Lucky does the reason your spam users show as banned match the results of this phrase? "spam_cleaner_ban_reason"

Enes3078 · Jul 31, 2024

@AndrewSimm message edit box has privileged option. It's a bug huh?

Mr Lucky · Jul 31, 2024

AndrewSimm said:
@Mr Lucky does the reason your spam users show as banned match the results of this phrase? "spam_cleaner_ban_reason"

The latest one does, previous ones have what was a previous wording, ie what shows as the ban reason on the account.

AndrewSimm · Jul 31, 2024

Enes3078 said:
@AndrewSimm message edit box has privileged option. It's a bug huh?

View attachment 307577

where is this box appearing?

Enes3078 · Jul 31, 2024

AndrewSimm said:
where is this box appearing?

Message edit box. Send a message and then click edit.

AndrewSimm · Jul 31, 2024

Mr Lucky said:
The latest one does, previous ones have what was a previous wording, ie what shows as the ban reason on the account.

What was the previous wording?

[Andrew] Moderator Panel 2.0.9

Member

Member

Active member

Member

Active member

Well-known member

Well-known member

Active member

Well-known member

Active member

Active member

Well-known member

Well-known member

​

Well-known member

Well-known member

Active member

Well-known member

Well-known member

Active member

Well-known member

Similar threads

We value your privacy