인프런 커뮤니티 질문&답변

routes/subs.ts 의 topSubs 쿼리빌더에서 sql 오류가 자꾸 발생합니다.

수고 많으십니다

수업 잘 듣고 있습니다

다름이 아니라

const topSubs = async (req: Request, res: Response) => {
  try {
    const imageUrlExp = `COALESCE(s."imageUrn",'https://www.gravatar.com/avatar?d=mp&f=y')`;
    const subs = await AppDataSource.createQueryBuilder()
      .select(
        `s.title, s.name, ${imageUrlExp} as "imageUrl", count(p.id) as "postCount"`
      )
      .from(Sub, "s")
      .leftJoin(Post, "p", `s.name = p."subName`)
      .groupBy('s.title, s.name, "imageUrl"')
      .orderBy(`"postCount"`, "DESC")
      .limit(5)
      .execute();
  } catch (error) {
    console.log(error);
    return res.status(500).json({ error: "문제가 발생하였습니다" });
  }
};

위 소스를 실행하기 위해

http://localhost:4000/api/subs/sub/topSubs

를 실행하면

{"error":"문제가 발생하였습니다"}

같은 에러가 발생하고

세부로그를 보면

QueryFailedError: 구문 오류, "s" 부근
    at PostgresQueryRunner.query (D:\webwork\nextwork\raddit-clone-test-01\server\src\driver\postgres\PostgresQueryRunner.ts:299:19)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SelectQueryBuilder.execute (D:\webwork\nextwork\raddit-clone-test-01\server\src\query-builder\QueryBuilder.ts:523:20) {
  query: `SELECT "s"."title", "s"."name", COALESCE(s."imageUrn",'https://www.gravatar.com/avatar?d=mp&f=y') as "imageUrl", count("p"."id") as "postCount" FROM "subs" "s" LEFT JOIN "posts" "p" ON "s"."name" = p."subName GROUP BY "s"."title", "s"."name", "imageUrl" ORDER BY "postCount" DESC LIMIT 5`,
  parameters: [],
  driverError: error: 구문 오류, "s" 부근
      at Parser.parseErrorMessage (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\parser.ts:369:69)
      at Parser.handlePacket (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\parser.ts:188:21)
      at Parser.parse (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\parser.ts:103:30)
      at Socket.<anonymous> (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\index.ts:7:48)
      at Socket.emit (node:events:513:28)
      at Socket.emit (node:domain:489:12)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Socket.Readable.push (node:internal/streams/readable:234:10)    
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {   
    length: 90,
    severity: '오류',
    code: '42601',
    detail: undefined,
    hint: undefined,
    position: '220',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'scan.l',
    line: '1192',
    routine: 'scanner_yyerror'
  },
  length: 90,
  severity: '오류',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '220',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1192',
  routine: 'scanner_yyerror'
}
GET /api/subs/sub/topSubs 500 35.980 ms - 43
QueryFailedError: 구문 오류, "s" 부근
    at PostgresQueryRunner.query (D:\webwork\nextwork\raddit-clone-test-01\server\src\driver\postgres\PostgresQueryRunner.ts:299:19)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async SelectQueryBuilder.execute (D:\webwork\nextwork\raddit-clone-test-01\server\src\query-builder\QueryBuilder.ts:523:20) {
  query: `SELECT "s"."title", "s"."name", COALESCE(s."imageUrn",'https://www.gravatar.com/avatar?d=mp&f=y') as "imageUrl", count("p"."id") as "postCount" FROM "subs" "s" LEFT JOIN "posts" "p" ON "s"."name" = p."subName GROUP BY "s"."title", "s"."name", "imageUrl" ORDER BY "postCount" DESC LIMIT 5`,
  parameters: [],
  driverError: error: 구문 오류, "s" 부근
      at Parser.parseErrorMessage (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\parser.ts:369:69)
      at Parser.handlePacket (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\parser.ts:188:21)
      at Parser.parse (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\parser.ts:103:30)
      at Socket.<anonymous> (D:\webwork\nextwork\raddit-clone-test-01\server\node_modules\pg-protocol\src\index.ts:7:48)
      at Socket.emit (node:events:513:28)
      at Socket.emit (node:domain:489:12)
      at addChunk (node:internal/streams/readable:324:12)
      at readableAddChunk (node:internal/streams/readable:297:9)
      at Socket.Readable.push (node:internal/streams/readable:234:10)    
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {   
    length: 90,
    severity: '오류',
    code: '42601',
    detail: undefined,
    hint: undefined,
    position: '220',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'scan.l',
    line: '1192',
    routine: 'scanner_yyerror'
  },
  length: 90,
  severity: '오류',
  code: '42601',
  detail: undefined,
  hint: undefined,
  position: '220',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'scan.l',
  line: '1192',
  routine: 'scanner_yyerror'
}
GET /api/subs/sub/topSubs 500 6.997 ms - 43

와 같습니다.

아무리 봐도 에러 원인을 찾지 못해 이렇게 글을 올립니다

해결방법을 알려주셨으면 합니다.

db는 postgresql 15.1 입니다

감사합니다