Issue

메시지 통계에서 알림톡 발송량 조회시 "QueryFailedError: Error: Incorrect syntax near ')'

Problem

/v1/messages/dailyTop GET API에서 퀴리 빌드시 where 조건 messageType IN ()이 비어있어 syntax 에러가 발생했다.

✏️ traceid: 14f5340d-1add-437f-910c-08eb4419f9b6

cloudwatch에서 traceid로 확인한 에러 로그이다.

{
    "level": "info",
    "traceId": "14f5340d-1add-437f-910c-08eb4419f9b6",
    "method": "GET",
    "url": "블라",
    "authorization": "블라",
    "message": "[request] {{}}",
    "body": {},
    "hospitalId": "블라",
    "role": "블라",
    "crmUser": "블라",
    "instanceId": "i-블라",
    "rss": "907.56 MB",
    "heapTotal": "586.49 MB",
    "heapUsed": "509.29 MB",
    "external": "32.40 MB",
    "arrayBuffers": "13.22 MB",
    "timestamp": "2024-07-02T06:38:18.198Z",
    "ms": "+79ms"
}

{
    "level": "error",
    "hospitalId": "21752",
    "statusCode": 500,
    "authorization": "블라",
    "url": "블라",
    "traceId": "블라",
    "request": {},
    "instanceId": "블라",
    "message": "[response] QueryFailedError: Error: Incorrect syntax near ')'.\n    at /var/app/current/node_modules/.pnpm/@alm1983+typeorm@0.3.20-11_ioredis@5.4.1_mssql@9.1.1_reflect-metadata@0.2.2_ts-node@10.9.0/node_modules/@alm1983/typeorm/driver/sqlserver/SqlServerQueryRunner.js:205:30\n    at /var/app/current/node_modules/.pnpm/mssql@9.1.1/node_modules/mssql/lib/base/request.js:440:25\n    at Request.userCallback (/var/app/current/node_modules/.pnpm/mssql@9.1.1/node_modules/mssql/lib/tedious/request.js:492:15)\n    at Request.callback (/var/app/current/node_modules/.pnpm/tedious@15.1.3/node_modules/tedious/lib/request.js:205:14)\n    at Parser.onEndOfMessage (/var/app/current/node_modules/.pnpm/tedious@15.1.3/node_modules/tedious/lib/connection.js:2823:22)\n    at Object.onceWrapper (node:events:632:28)\n    at Parser.emit (node:events:518:28)\n    at Readable.<anonymous> (/var/app/current/node_modules/.pnpm/tedious@15.1.3/node_modules/tedious/lib/token/token-stream-parser.js:32:12)\n    at Readable.emit (node:events:518:28)\n    at endReadableNT (node:internal/streams/readable:1696:12)",
    "stack": "QueryFailedError: Error: Incorrect syntax near ')'.\n    at /var/app/current/node_modules/.pnpm/@alm1983+typeorm@0.3.20-11_ioredis@5.4.1_mssql@9.1.1_reflect-metadata@0.2.2_ts-node@10.9.0/node_modules/@alm1983/typeorm/driver/sqlserver/SqlServerQueryRunner.js:205:30\n    at /var/app/current/node_modules/.pnpm/mssql@9.1.1/node_modules/mssql/lib/base/request.js:440:25\n    at Request.userCallback (/var/app/current/node_modules/.pnpm/mssql@9.1.1/node_modules/mssql/lib/tedious/request.js:492:15)\n    at Request.callback (/var/app/current/node_modules/.pnpm/tedious@15.1.3/node_modules/tedious/lib/request.js:205:14)\n    at Parser.onEndOfMessage (/var/app/current/node_modules/.pnpm/tedious@15.1.3/node_modules/tedious/lib/connection.js:2823:22)\n    at Object.onceWrapper (node:events:632:28)\n    at Parser.emit (node:events:518:28)\n    at Readable.<anonymous> (/var/app/current/node_modules/.pnpm/tedious@15.1.3/node_modules/tedious/lib/token/token-stream-parser.js:32:12)\n    at Readable.emit (node:events:518:28)\n    at endReadableNT (node:internal/streams/readable:1696:12)",
    "rss": "907.56 MB",
    "heapTotal": "586.49 MB",
    "heapUsed": "510.54 MB",
    "external": "32.43 MB",
    "arrayBuffers": "13.25 MB",
    "timestamp": "2024-07-02T06:38:18.241Z",
    "ms": "+43ms"
}

Solution

type 파라미터가 MESSAGE, FRIEND_TALK, ALL, ADVERTISE일 때는 정상 작동하고 INFO_TALK일 때만 에러 발생하는 것으로 확인했다. 쿼리에서 messageType in (…messageTypes)을 where 조건으로 설정하지만 type이 INFO_TALK일 때 ( )가 비어있어 syntax 에러가 발생한 것이다.

queryBuilder.from((oqb) => {
      oqb.from((qb) => {
        qb.from('rawMessages', 'u');
        qb.andWhere('userId = :userId', {
          userId: hospitalId,
        });
        qb.andWhere(
          new Brackets((wb) => {
            if (categoryType.includes(MessageStatisticsCategoryType.ADVERTISE))
              wb.orWhere('isAdvertise = 1');
            **wb.orWhere('messageType in (:...messageTypes)', {**
              messageTypes: types,
            });
            return wb;
          }),
        );
        ...

따라서 type 파라미터를 messageType으로 매핑해주는 메소드를 확인했다.

private static getRelatedMessageType(
    types: MessageStatisticsCategoryType[],
  ): MessageType[] {
    return _.chain(types)
      .flatMap((type) => {
        if (type === MessageStatisticsCategoryType.MESSAGE)
          return [MessageType.SMS, MessageType.LMS, MessageType.MMS];
        if (type === MessageStatisticsCategoryType.FRIEND_TALK)
          return [
            MessageType.TEXT_FRIEND_TALK,
            MessageType.IMAGE_FRIEND_TALK,
            MessageType.WIDE_FRIEND_TALK,
          ];
        if (type === MessageStatisticsCategoryType.ALL)
          return Object.values(MessageType);
        if (type === MessageStatisticsCategoryType.ADVERTISE)
          return Object.values(MessageType);
        return [];
      })
      .uniq()
      .value();
  }

역시나 type이 MessageStatisticsCategoryType.INFO_TALK일 때 MessageType.INFO_TALK로 매핑하는 부분이 빠져있다는 것을 확인했다.

if (type === MessageStatisticsCategoryType.INFO_TALK)
          return [MessageType.INFO_TALK];

따라서 위와 같이 추가했다.

성공!