Status Label asset count exhausts memory with many assets

[travismiller]

Please confirm you have done the following before posting your bug report:

• I have enabled debug mode
• I have read checked the Common Issues page

Describe the bug

The api/v1/statuslabels/assets endpoint exhausts memory when there are a “large” number of assets.

To Reproduce
Steps to reproduce the behavior:

1. Load ~160k assets
2. Visit the dashboard
3. See that the statuslabels/assets endpoint returns a 500 error

Expected behavior

The endpoint should not exhaust memory.

Screenshots

Here’s a screenshot of the feature that uses this API endpoint (when a fix is applied):

Server (please complete the following information):

• Snipe-IT Version: develop
• OS: Docker (php:7.4.20-apache)
• Web Server: Docker (php:7.4.20-apache)
• PHP Version: 7.4.20

Apache log

app_1       | [Wed Jun 09 16:23:19.996346 2021] [php7:error] [pid 86] [client 192.168.16.1:63222] PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 8192 bytes) in /var/www/snipe-it/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php on line 255, referer: http://localhost:3051/
app_1       | [Wed Jun 09 16:23:20.004889 2021] [php7:error] [pid 86] [client 192.168.16.1:63222] PHP Fatal error:  Allowed memory size of 1073741824 bytes exhausted (tried to allocate 32768 bytes) in /var/www/snipe-it/vendor/symfony/debug/Exception/FatalErrorException.php on line 1, referer: http://localhost:3051/
app_1       | 192.168.16.1 - - [09/Jun/2021:16:23:17 +0000] "GET /api/v1/statuslabels/assets HTTP/1.1" 500 185 "http://localhost:3051/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/201001

Additional context

• We have ~160k assets

• We have 46 asset labels

• Tested with PHP memory limit set from default 128M up to 1024M

• It appears the counting query may be eager loading all assets:

  snipe-it/app/Http/Controllers/Api/StatuslabelsController.php

  Lines 170 to 173 in 6c1dd81

  	$statuslabels = Statuslabel::with('assets')
  	->groupBy('id')
  	->withCount('assets as assets_count')
  	->get();

• Ideal way to count related models: https://laravel.com/docs/6.x/eloquent-relationships#counting-related-models

  $statuslabels = Statuslabel::withCount('assets')->get();

• I will submit a PR