PrismaP
Prisma•13mo ago
thehornta

ERROR: could not determine data type of parameter $5

Hi, I'm trying out the TypedSQL feature, but I'm receiving an error when trying to generate prisma with --sql. Can anyone spot what I'm doing incorrectly?
-- @param {Int} $1:userId
-- @param {Int} $2:parentEntryId
-- @param {Int} $3:pageSize
-- @param {Int} $4:page
-- @param {String} $5:query
-- @param {String} $6:sort

WITH EntryWithFiles AS (
  SELECT
    e.id,
    e.name,
    e.created_at,
    e.user_id,
    e.parent_entry_id,
    CASE
      WHEN f.id IS NOT NULL THEN 'folder'
      ELSE 'file'
    END as "entryType",
    COALESCE(files.size, null) as size,
    COALESCE(files.status, null) as status,
    COALESCE(files.type, null) as file_type,
    COUNT(*) OVER() as total_count
  FROM entry e
  LEFT JOIN folder f ON f.id = e.id AND f.user_id = e.user_id
  LEFT JOIN LATERAL (
    SELECT f.size, f.status, f.type
    FROM file f
    WHERE f.id = e.id AND f.user_id = e.user_id
    LIMIT 1
  ) files ON true
  WHERE
    e.user_id = $1
    AND e.parent_entry_id = $2
    AND CASE
      WHEN $5 IS NOT NULL THEN
        e.name ILIKE CONCAT('%',$5,'%')
      ELSE true
    END
)
SELECT *
FROM EntryWithFiles
ORDER BY
  CASE WHEN $6 = 'newest' THEN created_at END DESC,
  CASE WHEN $6 = 'oldest' THEN created_at END ASC,
  -- For a-z, sort folders first, then by name
  CASE WHEN $6 = 'a-z' THEN
    CASE "entryType"
      WHEN 'folder' THEN 0
      ELSE 1
    END
  END ASC,
  CASE WHEN $6 = 'a-z' THEN name END ASC,
  -- For z-a, sort folders last, then by name
  CASE WHEN $6 = 'z-a' THEN
    CASE "entryType"
      WHEN 'folder' THEN 1
      ELSE 0
    END
  END ASC,
  CASE WHEN $6 = 'z-a' THEN name END DESC,
  CASE WHEN $6 = 'smallest' THEN size END ASC,
  CASE WHEN $6 = 'largest' THEN size END DESC
LIMIT $3
OFFSET ($4 - 1) * $3;
Was this page helpful?