使用Prisma自定义SQL传递数字数组至IN参数报错求解决方案

你遇到的错误是因为直接将数组传入$queryRaw的模板字符串时，Prisma会把数组解析为PostgreSQL的bigint[]类型，而你的player_id是integer类型，两者无法直接匹配。以下是两种可行的解决方法：

方法一：使用Prisma.join生成参数列表 #

通过Prisma.join将数组元素转换为SQL参数列表，自动处理类型匹配：

import { Prisma } from '@prisma/client';

const numberOfTryscorers: number = home_tryscorer_ids.length;

const games: GameType[] = await this.db
  .$queryRaw`
    SELECT g.*, COUNT(DISTINCT p."player_id")
    FROM "public"."NrlPlayerToGame" p
    JOIN "public"."NrlGame" g ON p."game_id" = g."id"
    WHERE p."player_id" IN (${Prisma.join(home_tryscorer_ids)}) AND "tries" >= 1
    GROUP BY g."id", g."date", g."home_team_id", g."away_team_id"
    HAVING COUNT(DISTINCT p."player_id") = ${numberOfTryscorers}
  `;

方法二：使用ANY操作符配合类型转换 #

将传入的数组强制转为integer[]类型，与player_id的类型匹配后用ANY替代IN：

const numberOfTryscorers: number = home_tryscorer_ids.length;

const games: GameType[] = await this.db
  .$queryRaw`
    SELECT g.*, COUNT(DISTINCT p."player_id")
    FROM "public"."NrlPlayerToGame" p
    JOIN "public"."NrlGame" g ON p."game_id" = g."id"
    WHERE p."player_id" = ANY(${home_tryscorer_ids}::integer[]) AND "tries" >= 1
    GROUP BY g."id", g."date", g."home_team_id", g."away_team_id"
    HAVING COUNT(DISTINCT p."player_id") = ${numberOfTryscorers}
  `;

为什么之前的方法无效？ #

• 直接传数组：Prisma会将序列化为bigint[]类型，与integer类型的player_id无法匹配，触发类型错误。
• 转逗号分隔字符串：会被SQL解析为单个字符串参数（如IN ('1,2,7')），实际是匹配player_id等于字符串"1,2,7"，而非三个数字值。

内容的提问来源于stack exchange，提问作者unicorn_surprise