SQL query too big with reactions

[nickvergessen]

{
  "reqId": "OMAUCLFyse0NuXFA4tem",
  "level": 3,
  "time": "2022-11-04T10:54:33+00:00",
  "remoteAddr": "…",
  "user": "…",
  "app": "core",
  "method": "GET",
  "url": "/ocs/v2.php/apps/spreed/api/v1/reaction/…/1500436",
  "message": "More than 1000 expressions in a list are not allowed on Oracle.",
  "userAgent": "Mozilla/5.0 (X11; Linux x86_64; rv:106.0) Gecko/20100101 Firefox/106.0",
  "version": "25.0.1.1",
  "exception": {
    "Exception": "Doctrine\\DBAL\\Query\\QueryException",
    "Message": "More than 1000 expressions in a list are not allowed on Oracle.",
    "Code": 0,
    "Trace": [
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
        "line": 294,
        "function": "execute",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->",
        "args": []
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/Comments/Manager.php",
        "line": 1119,
        "function": "executeQuery",
        "class": "OC\\DB\\QueryBuilder\\QueryBuilder",
        "type": "->",
        "args": []
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/Comments/Manager.php",
        "line": 1042,
        "function": "getCommentsById",
        "class": "OC\\Comments\\Manager",
        "type": "->",
        "args": [
          [
            1500437,
            1500438,
            1500439,
            1500440,
            1500441,
            "And 1472 more entries, set log level to debug to see all entries"
          ]
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/apps/spreed/lib/Chat/ReactionManager.php",
        "line": 155,
        "function": "retrieveAllReactions",
        "class": "OC\\Comments\\Manager",
        "type": "->",
        "args": [
          1500436
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/apps/spreed/lib/Controller/ReactionController.php",
        "line": 124,
        "function": "retrieveReactionMessages",
        "class": "OCA\\Talk\\Chat\\ReactionManager",
        "type": "->",
        "args": [
          {
            "__class__": "OCA\\Talk\\Room"
          },
          {
            "__class__": "OCA\\Talk\\Participant"
          },
          1500436,
          null
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
        "line": 225,
        "function": "getReactions",
        "class": "OCA\\Talk\\Controller\\ReactionController",
        "type": "->",
        "args": [
          1500436,
          null
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/AppFramework/Http/Dispatcher.php",
        "line": 133,
        "function": "executeController",
        "class": "OC\\AppFramework\\Http\\Dispatcher",
        "type": "->",
        "args": [
          {
            "__class__": "OCA\\Talk\\Controller\\ReactionController"
          },
          "getReactions"
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/AppFramework/App.php",
        "line": 172,
        "function": "dispatch",
        "class": "OC\\AppFramework\\Http\\Dispatcher",
        "type": "->",
        "args": [
          {
            "__class__": "OCA\\Talk\\Controller\\ReactionController"
          },
          "getReactions"
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/Route/Router.php",
        "line": 298,
        "function": "main",
        "class": "OC\\AppFramework\\App",
        "type": "::",
        "args": [
          "OCA\\Talk\\Controller\\ReactionController",
          "getReactions",
          {
            "__class__": "OC\\AppFramework\\DependencyInjection\\DIContainer"
          },
          [
            "v1",
            "8135184652",
            "1500436",
            "ocs.spreed.Reaction.getReactions"
          ]
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/ocs/v1.php",
        "line": 63,
        "function": "match",
        "class": "OC\\Route\\Router",
        "type": "->",
        "args": [
          "/ocsapp/apps/spreed/api/v1/reaction/8135184652/1500436"
        ]
      },
      {
        "file": "/var/www/cloud.nextcloud.com/nextcloud/ocs/v2.php",
        "line": 23,
        "args": [
          "/var/www/cloud.nextcloud.com/nextcloud/ocs/v1.php"
        ],
        "function": "require_once"
      }
    ],
    "File": "/var/www/cloud.nextcloud.com/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php",
    "Line": 264,
    "message": "More than 1000 expressions in a list are not allowed on Oracle.",
    "query": "SELECT * FROM `*PREFIX*comments` WHERE `id` IN (:dcValue1) ORDER BY `creation_timestamp` DESC, `id` DESC",
    "exception": {},
    "CustomMessage": "More than 1000 expressions in a list are not allowed on Oracle."
  }
}

[vitormattos]

What this code do:

Fetch all reactions of a message.
The reaction is stored as a comment and to fetch who did the reaction, is necessary to do a query on comments table with comments.id equals to all ids of reactions to get the actors of all reactions.

The problem occur when we have more than 1000 reactions with Oracle database because Oracle can't do a query with more than 1000 parameters.

The method that fetch the comments by id is on de server repository, than, maybe the solution of this issue will be at server, not here in Talk repository.

Possible solutions that I think to solve this problem:

• Split the comments ids by groups of 1000 and do separated parameterized queries.
• use a raw where value like ->where('id in (1,2,3)') and don't use parameters like ->where($query->expr()->in('id', $query->createNamedParameter($commentIds, IQueryBuilder::PARAM_STR_ARRAY))).

@nickvergessen did you have any other suggestion or preference by any of the above approaches?

[nickvergessen]

Split the comments ids by groups of 1000 and do separated parameterized queries.

Yes, similarly to this case:
https://github.com/nextcloud/server/blob/c6c512a19d13dbe7fe0bdd7330f53c3beb351686/apps/dav/lib/CalDAV/CalDavBackend.php#L1153

[nickvergessen]

But don't use 1000 as chunk size, but maybe something like 500

[vitormattos]

We have a problem: the sort with multiple values using date and after this the ID of comment that now we do by SQL.

[vitormattos]

Maybe isn't a problem if the chunks already was sorted by id.