https://www.prisma.io/ logo
Join Slack
Powered by
Hi all, I am trying to execute a rawQuery that con...
# orm-help
r
Hi all, I am trying to execute a rawQuery that contains parameters. As soon as I submit this query, I get the following error message. I want to develop a global search with the rawQuery, for this I use tsvectoren in a postgres database. Has anyone had the same problem and can help me?
Copy code
const value = 'test';
const results = await prismaClient.$queryRaw`
    SELECT * FROM "contact"
    WHERE "textSearch" @@ to_tsquery('english', '${query}')
    ORDER BY ts_rank("textSearch", to_tsquery('english', '${query}')) DESC
    LIMIT 10;
  ;`;
Copy code
PrismaClientKnownRequestError: 
Invalid `prisma.queryRaw()` invocation:


  Your raw query had an incorrect number of parameters. Expected: `0`, actual: `2`.
    {
  code: 'P1016',
  clientVersion: '3.0.2',
  meta: { expected: 0, actual: 2 }
}
👀 1
r
Hi @Robin Irmer 👋 Can you try this
Copy code
const value = 'test';
const results = await prismaClient.$queryRaw`
    SELECT * FROM "contact"
    WHERE "textSearch" @@ to_tsquery('english', ${query})
    ORDER BY ts_rank("textSearch", to_tsquery('english', ${query})) DESC
    LIMIT 10`;
r
Hi @Raphael Etim, thanks for your reply. Unfortunately, the query doesn’t work for me. Do you know any other solution?
But I get another error message:
Copy code
PrismaClientKnownRequestError: 
Invalid `prisma.queryRaw()` invocation:


  Raw query failed. Code: `N/A`. Message: `error deserializing column 8: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `tsvector`` {
  code: 'P2010',
  clientVersion: '3.0.2',
  meta: {
    code: 'N/A',
    message: 'error deserializing column 8: cannot convert between the Rust type `core::option::Option<alloc::string::String>` and the Postgres type `tsvector`'
  }
}
When I run the sql query on my postgres database, this works
Copy code
SELECT * FROM "contact"
    WHERE "textSearch" @@ to_tsquery('english', 'Robin')
    ORDER BY ts_rank("textSearch", to_tsquery('english', 'Robin')) DESC
    LIMIT 10
r
From the error message, it looks like prisma doesn't have support for the Postgres type 
tsvector
There is an open issue about unsupported types here. You can add your specific usecase there.
5 Views
Open in Slack
PreviousNext
Powered by