Prisma•4mo ago

Passing an array into TypedSQL query

Hi, thank you for the TypedSQL feature I am trying to use now, but getting an error when passing and array of integers. Can I ask for pointing out what am I doing wrong? This is my query in .sql file:

-- @param {Int} $1:jobLevelId
-- @param {Int} $2:userId
-- @param {Int} $3:strengthThreshold
-- @param {Int} $4:feedbacksCountThreshold
-- @param {Int[]} $5:higherOrEqualLevelIds

SELECT 
    u.id AS "userId",
    u."jobLevelId",
    cf."competencyId",
    AVG(cf.value) AS "averageValue",
    COUNT(cf.id) AS "totalCount"
FROM
    "CompetencyMentorshipApplication" cma
JOIN
    "User" u ON u.id = cma."userId"
JOIN
    "CompetencyFeedback" cf ON cf."competencyId" = cma."competencyId"
JOIN
    "Feedback" f ON cf."feedbackId" = f."id"
JOIN
    "_ReceivedByUsers" rbu ON f."id" = rbu."A" AND rbu."B" = u.id
LEFT JOIN
    "Level" l ON u."jobLevelId" = l.id
WHERE
    cma."userId" != $2
    AND ($1 IS NULL OR u."jobLevelId" = $1)
    AND (ARRAY_LENGTH($5, 1) IS NULL OR u."jobLevelId" = ANY($5::integer[]))
    AND (ARRAY_LENGTH($5, 1) IS NULL OR cf."levelId" = ANY($5::integer[]))
GROUP BY
    u.id, u."jobLevelId", u."lastName", cf."competencyId", l."order"
HAVING
    AVG(cf.value) >= $3
    AND COUNT(cf.id) >= $4;

-- @param {Int} $1:jobLevelId
-- @param {Int} $2:userId
-- @param {Int} $3:strengthThreshold
-- @param {Int} $4:feedbacksCountThreshold
-- @param {Int[]} $5:higherOrEqualLevelIds

SELECT 
    u.id AS "userId",
    u."jobLevelId",
    cf."competencyId",
    AVG(cf.value) AS "averageValue",
    COUNT(cf.id) AS "totalCount"
FROM
    "CompetencyMentorshipApplication" cma
JOIN
    "User" u ON u.id = cma."userId"
JOIN
    "CompetencyFeedback" cf ON cf."competencyId" = cma."competencyId"
JOIN
    "Feedback" f ON cf."feedbackId" = f."id"
JOIN
    "_ReceivedByUsers" rbu ON f."id" = rbu."A" AND rbu."B" = u.id
LEFT JOIN
    "Level" l ON u."jobLevelId" = l.id
WHERE
    cma."userId" != $2
    AND ($1 IS NULL OR u."jobLevelId" = $1)
    AND (ARRAY_LENGTH($5, 1) IS NULL OR u."jobLevelId" = ANY($5::integer[]))
    AND (ARRAY_LENGTH($5, 1) IS NULL OR cf."levelId" = ANY($5::integer[]))
GROUP BY
    u.id, u."jobLevelId", u."lastName", cf."competencyId", l."order"
HAVING
    AVG(cf.value) >= $3
    AND COUNT(cf.id) >= $4;

And this an error I am getting when running prisma generate --sql:

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Error: Errors while reading sql files:

In prisma/sql/listCompetencyMentors.sql:
Error: ERROR: could not determine polymorphic type because input has type unknown

Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Error: Errors while reading sql files:

In prisma/sql/listCompetencyMentors.sql:
Error: ERROR: could not determine polymorphic type because input has type unknown

Thank you very much. Ondrej

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Passing an array into TypedSQL query