MessageSearch: More than 1000 expressions in a list are not allowed on Oracle

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

When there are too many rooms, we are not able to execute the search query anymore. Stacktrace:

Doctrine\DBAL\Query\QueryException: More than 1000 expressions in a list are not allowed on Oracle.
#0 /opt/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(239): OC\DB\QueryBuilder\QueryBuilder::prepareForExecute
#1 /opt/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php(286): OC\DB\QueryBuilder\QueryBuilder::executeQuery
#2 /spreed/lib/Chat/CommentsManager.php(299): OCA\Talk\Chat\CommentsManager::searchForObjectsWithFilters
#3 /spreed/lib/Chat/ChatManager.php(1123): OCA\Talk\Chat\ChatManager::searchForObjectsWithFilters
#4 /spreed/lib/Search/MessageSearch.php(198): OCA\Talk\Search\MessageSearch::performSearch
#5 /spreed/lib/Search/MessageSearch.php(138): OCA\Talk\Search\MessageSearch::search
#6 /opt/nextcloud/lib/private/Search/SearchComposer.php(365): OC\Search\SearchComposer::search
#7 /opt/nextcloud/core/Controller/UnifiedSearchController.php(117): OC\Core\Controller\UnifiedSearchController::search
#8 /opt/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(204): OC\AppFramework\Http\Dispatcher::executeController
#9 /opt/nextcloud/lib/private/AppFramework/Http/Dispatcher.php(118): OC\AppFramework\Http\Dispatcher::dispatch
#10 /opt/nextcloud/lib/private/AppFramework/App.php(153): OC\AppFramework\App::main
#11 /opt/nextcloud/lib/private/Route/Router.php(321): OC\Route\Router::match
#12 /opt/nextcloud/ocs/v1.php(61): require_once
#13 /opt/nextcloud/ocs/v2.php(8)

Roomlist created at https://github.com/nextcloud/spreed/blob/b04ec3d38fccfe9392a3119d44178ecb7395fa35/lib/Search/MessageSearch.php#L145-L170

and passed at https://github.com/nextcloud/spreed/blob/b04ec3d38fccfe9392a3119d44178ecb7395fa35/lib/Search/MessageSearch.php#L200

Not totally sure if this should be handled in talk or server.

Start with lib/Search/MessageSearch.php around lines 145-170 and 200, then trace the room list into lib/Chat/CommentsManager.php around line 299 and the server QueryBuilder stack. Determine where the more-than-1000-expression failure should be handled; done means message search works for more than 1000 rooms on Oracle.