BUG: JSON API v1 - error when filtering by custom fields

re8260 commented 8 months ago

Issue

SQL error when fetching beans by JSON v1 API with filter on custom filed because in count query there is no join for '*_cstm' table. Without filter on custom fields no error.

Issue effect on pagination, without pagination only error messages are printed in logs.

Expected Behavior

No error expected

Actual Behavior

PHP Error log:

Notice:  Trying to access array offset on value of type bool in /www/public/Api/V8/BeanDecorator/BeanManager.php on line 268
Stack trace:
   1. {main}() /www/public/Api/index.php:0
   2. Slim\\App->run() /www/public/Api/index.php:4
   3. Slim\\App->process() /www/public/vendor/slim/slim/Slim/App.php:297
   4. Slim\\App->callMiddlewareStack() /www/public/vendor/slim/slim/Slim/App.php:392
   5. Slim\\App->__invoke() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:117
   6. Slim\\Route->run() /www/public/vendor/slim/slim/Slim/App.php:503
   7. Slim\\Route->callMiddlewareStack() /www/public/vendor/slim/slim/Slim/Route.php:268
   8. Slim\\Route->Slim\\{closure:/www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:63-78}() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:117
   9. call_user_func:{/www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70}() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
  10. Slim\\DeferredCallable->__invoke() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
  11. call_user_func_array:{/www/public/vendor/slim/slim/Slim/DeferredCallable.php:57}() /www/public/vendor/slim/slim/Slim/DeferredCallable.php:57
  12. League\\OAuth2\\Server\\Middleware\\ResourceServerMiddleware->__invoke() /www/public/vendor/slim/slim/Slim/DeferredCallable.php:57
  13. Slim\\Route->Slim\\{closure:/www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:63-78}() /www/public/vendor/league/oauth2-server/src/Middleware/ResourceServerMiddleware.php:53
  14. call_user_func:{/www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70}() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
  15. Slim\\DeferredCallable->__invoke() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
  16. call_user_func_array:{/www/public/vendor/slim/slim/Slim/DeferredCallable.php:57}() /www/public/vendor/slim/slim/Slim/DeferredCallable.php:57
  17. Api\\V8\\Middleware\\SetLanguageMiddleware->__invoke() /www/public/vendor/slim/slim/Slim/DeferredCallable.php:57
  18. Slim\\Route->Slim\\{closure:/www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:63-78}() /www/public/custom/application/Ext/Api/V8/Middleware/SetLanguageMiddleware.php:32
  19. call_user_func:{/www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70}() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
  20. Slim\\DeferredCallable->__invoke() /www/public/vendor/slim/slim/Slim/MiddlewareAwareTrait.php:70
  21. call_user_func_array:{/www/public/vendor/slim/slim/Slim/DeferredCallable.php:57}() /www/public/vendor/slim/slim/Slim/DeferredCallable.php:57
  22. Slim\\Container->Api\\V8\\Factory\\{closure:/www/public/Api/V8/Factory/ParamsMiddlewareFactory.php:34-38}() /www/public/vendor/slim/slim/Slim/DeferredCallable.php:57
  23. Api\\V8\\Middleware\\ParamsMiddleware->__invoke() /www/public/Api/V8/Factory/ParamsMiddlewareFactory.php:37
  24. Slim\\Route->__invoke() /www/public/Api/V8/Middleware/ParamsMiddleware.php:67
  25. Api\\V8\\Controller\\InvocationStrategy\\SuiteInvocationStrategy->__invoke() /www/public/vendor/slim/slim/Slim/Route.php:281
  26. Api\\V8\\Controller\\ModuleController->getModuleRecords() /www/public/Api/V8/Controller/InvocationStrategy/SuiteInvocationStrategy.php:28
  27. Api\\V8\\Service\\ModuleService->getRecords() /www/public/Api/V8/Controller/ModuleController.php:58
  28. Api\\V8\\BeanDecorator\\BeanManager->countRecords() /www/public/Api/V8/Service/ModuleService.php:121

SuiteCRM Error log:

[FATAL] Mysqli_query failed.
[FATAL]  Query Failed: SELECT COUNT(*) AS cnt FROM accounts  WHERE (accounts_cstm.test_c = 'Test' AND accounts.phone_office = '') AND accounts.deleted = '0': MySQL error 1054: Unknown column 'accounts_cstm.test_c' in 'where clause'

Possible Fix

MR: https://github.com/salesagility/SuiteCRM/pull/10320

Steps to Reproduce

Add custom field to any module (I will use Account for example), for example test_c of any types
do get Accounts API request GET:{{crm_hostname}}/Api/V8/module/Accounts?filter[test_c][eq]=Test
Check Apache error log and SuiteCRM log

Context

Your Environment

SuiteCRM Version used: 7.12.x - 7.14.x
Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Chrome 120.x
Environment name and version (e.g. MySQL, PHP 7): PHP 7.4 - 8
Operating System and version (e.g Ubuntu 16.04): Ubuntu 20.04

elipp commented 8 months ago

(side note: SuiteCRM 7.14.x doesn't officially support PHP 7.4)

https://docs.suitecrm.com/admin/compatibility-matrix/

re8260 commented 8 months ago

Thanks @elipp I updated the details, bug not related to PHP\CRM version

salesagility / SuiteCRM