我有一个查询
select count(id), status, "createdAt"::date from "ProjectLog" where "projectId" = (select id from "Project" where slug = ${id}) and "createdAt" > current_date - interval '${interval} day' group by "createdAt"::date, status;
我也试过用户Prisma.sql
在引号内传递值,但它一直抛出错误,它期望1个参数,但fount 2。
我在棱镜2.20.1
中没有这个问题这个问题只发生在3.3.0
版本
Query: select count(id) as count, status, "createdAt"::date from "ProjectLog" where "projectId" = (select id from "Project" where slug = $1) and "createdAt" > current_date - interval '$2 day' and key notnull group by "createdAt"::date, status
Param: ["main","30"]
PrismaClientKnownRequestError:
Invalid `prisma.queryRaw()` invocation:
Your raw query had an incorrect number of parameters. Expected: `1`, actual: `2`.
code: 'P1016',
clientVersion: '3.3.0',
meta: { expected: 1, actual: 2 }
}
有什么建议吗?我不确定这是不是一个bug。
在prism文档中没有找到一个优雅的解决方案,我甚至找不到一个,但是有一种方法可以工作…你可以试试这个${'variable'}
尝试使用:
const interval = num + ' days';
${interval}::TEXT::INTERVAL
代替interval '${interval} day'
这是我的工作!
另一种选择,如果interval
的源是安全的(例如,在您的代码中其他地方定义,不是来自用户输入的),则对整个字符串利用Prisma.raw
,通常用单引号将其括起来:
const interval = 3;
const request = prisma.$queryRaw`
select count(id),
status,
"createdAt"::date
from "ProjectLog"
where "projectId" = (select id
from "Project"
where slug = ${id})
and "createdAt" > current_date - interval '${Prisma.raw(`${interval} day`}'
group by "createdAt"::date,
status`;
注意$queryRaw
和Prisma.raw
的组合。放置在其中的值没有参数化,而是包含在查询文本本身中,因此,如果您从不安全的来源获得interval
,则此代码容易受到SQL注入攻击。