Partial fix Multiple N+1 query behaviour when viewing reports

[Xon]

Affected version

2.0.10

A separate query per forum is triggered when view a large number of reports

SELECT cache_value
FROM xf_permission_cache_content
WHERE permission_combination_id = ?
    AND content_type = ?
    AND content_id = ?

XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 79
XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 91
XF\Db\AbstractAdapter->fetchOne() in src/XF/PermissionCache.php at line 72
XF\PermissionCache->getContentPerms() in src/XF/PermissionSet.php at line 51
XF\PermissionSet->hasContentPermission() in src/XF/Entity/User.php at line 913
XF\Entity\User->hasNodePermission() in src/XF/Report/Post.php at line 12
XF\Report\Post->canViewContent() in src/XF/Report/AbstractHandler.php at line 19
XF\Report\AbstractHandler->canView() in src/XF/Repository/Report.php at line 110
XF\Repository\Report->XF\Repository\{closure}()
array_filter() in src/XF/Mvc/Entity/AbstractCollection.php at line 167
XF\Mvc\Entity\AbstractCollection->filter() in src/XF/Repository/Report.php at line 111
XF\Repository\Report->filterViewableReports() in src/XF/Pub/Controller/Report.php at line 35
XF\Pub\Controller\Report->actionIndex() in src/XF/Mvc/Dispatcher.php at line 249
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 88
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 41
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1931
XF\App->run() in src/XF.php at line 329
XF::runApp() in index.php at line 13

Each user (content user & last commenter) is also being pulled in via individual queries;

SELECT `xf_user`.*
FROM `xf_user`

WHERE (`xf_user`.`user_id` = 4918)


LIMIT 1
Run Time: 0.000570
Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_user	const	PRIMARY	PRIMARY	4	const	1	 

XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 79
XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1130
XF\Mvc\Entity\Finder->fetchOne() in src/XF/Mvc/Entity/Manager.php at line 149
XF\Mvc\Entity\Manager->find() in src/XF/Mvc/Entity/Manager.php at line 352
XF\Mvc\Entity\Manager->getRelation() in src/XF/Mvc/Entity/Entity.php at line 373
XF\Mvc\Entity\Entity->getRelation() in src/XF/Mvc/Entity/Entity.php at line 171
XF\Mvc\Entity\Entity->get() in src/XF/Mvc/Entity/Entity.php at line 100
XF\Mvc\Entity\Entity->offsetGet() in internal_data/code_cache/templates/l1/s31/public/report_list_macros.php at line 19
XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 663
XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s31/public/report_list.php at line 38
XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 1250
XF\Template\Templater->renderTemplate() in src/XF/Template/Template.php at line 24
XF\Template\Template->render() in src/XF/Mvc/Renderer/Html.php at line 48
XF\Mvc\Renderer\Html->renderView() in src/XF/Mvc/Dispatcher.php at line 332
XF\Mvc\Dispatcher->renderView() in src/XF/Mvc/Dispatcher.php at line 303
XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 44
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1931
XF\App->run() in src/XF.php at line 329
XF::runApp() in index.php at line 13

 

[Mike]

The first bit here sort of overlaps with your previous bug report here. The second bit wasn't, though I have changed that now (along with a few other areas where we can easily reduce the number of queries).

It's relatively unlikely that the permission cache approach will change, mostly due to how the report system works. There's no clear place to say "and give me the permissions" because reports work solely on cached data (rather than querying with a join to get the data). It would be possibly by building extra elements into the report handler system, but given the relative infrequency of these pages being hit, I don't think it's really a worthwhile change. (Similar things apply when it comes to getting permissions for what moderators can action a report.)