Full-text-search not working when the query is multiple strings

[m-abdelwahab]

Bug description

Full-text-search not working when the query is multiple words, works fine when it's a single word being passed into my Prisma query:

const results = await prisma.post.findMany({
      where: {
        body: {
          search: `post`, // works
        },
      },
    });

const results = await prisma.post.findMany({
      where: {
        body: {
          search: `second post`, // throws an error
        },
      },
    });

I get the following error:

PrismaClientUnknownRequestError3 [PrismaClientUnknownRequestError]: 
Invalid `prisma.post.findMany()` invocation:


  Error occurred during query execution:
ConnectorError(ConnectorError { user_facing_error: None, kind: QueryError(Error { kind: Db, cause: Some(DbError { severity: "ERROR", parsed_severity: Some(Error), code: SqlState("42601"), message: "syntax error in tsquery: \"second post\"", detail: None, hint: None, position: None, where_: None, schema: None, table: None, column: None, datatype: None, constraint: None, file: Some("tsquery.c"), line: Some(726), routine: Some("makepol") }) }) })
    at cb (/Users/m-abdelwahab/Desktop/full-text-search-demo/node_modules/@prisma/client/runtime/index.js:36067:17)
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at async handler (webpack-internal:///./pages/api/search.ts:11:21)
    at async Object.apiResolver (/Users/m-abdelwahab/Desktop/full-text-search-demo/node_modules/next/dist/server/api-utils.js:101:9)
    at async DevServer.handleApiRequest (/Users/m-abdelwahab/Desktop/full-text-search-demo/node_modules/next/dist/server/next-server.js:760:9)
    at async Object.fn (/Users/m-abdelwahab/Desktop/full-text-search-demo/node_modules/next/dist/server/next-server.js:651:37)
    at async Router.execute (/Users/m-abdelwahab/Desktop/full-text-search-demo/node_modules/next/dist/server/router.js:205:32)
    at async DevServer.run (/Users/m-abdelwahab/Desktop/full-text-search-demo/node_modules/next/dist/server/next-server.js:825:29)
    at async DevServer.handleRequest (/Users/m-abdelwahab/Desktop/full-text-search-demo/node_modules/next/dist/server/next-server.js:292:20) {
  clientVersion: '2.30.0'
}

Reproduction: https://github.com/m-abdelwahab/prisma-full-text-search

How to reproduce

Reproduction: https://github.com/m-abdelwahab/prisma-full-text-search

Clone repo, install dependencies, run seed script, go to http://localhost:3000 and try some queries

Maybe I'm doing something wrong that I'm not aware of

Expected behavior

Should work normally and return results that contain my query.

Prisma information

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String @unique
  body   String
  status Status
}

enum Status {
  Draft
  Live
}

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Node.js version: v14.17.4

Prisma Version

prisma                : 2.30.0
@prisma/client        : 2.30.0
Current platform      : darwin
Query Engine (Binary) : query-engine 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine      : migration-engine-cli 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine  : introspection-core 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary         : prisma-fmt 60b19f4a1de4fe95741da371b4c44a92f4d1adcb (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash  : 60b19f4a1de4fe95741da371b4c44a92f4d1adcb
Studio                : 0.422.0
Preview Features      : fullTextSearch

[Weakky]

The API was designed this way to give full flexibility to users. Here's a proposal to make it more user-friendly:

type StringFilter {
  search: String | SearchFilter # The String shorthand would resolve into mode: NATURAL
}

type SearchFilter {
  query: String!
  mode: SearchFilterMode
}

enum SearchFilterMode {
  NATURAL,
  BOOLEAN
}

By default, we'd use plainto_tsquery to enable multi-word queries with spaces. If needed, users could use the more "advanced" mode.

eg:

prisma.x.findMany(where: { field: { search: "hello world" }})

prisma.x.findMany(where: { field: { search: { mode: "BOOLEAN", query: "hello & world" } }})

[matthewmueller]

The workaround today is to pre-process your queries, for example:

const results = await prisma.post.findMany({
  where: {
    body: {
      search: "second post".split(" ").join(" & "),
    },
  },
});

[m3hari]

My workaround is to replace whitespace with underscore, it works for tabs, newline, and consecutive white spaces.

{
  search:query.replace(/[\s\n\t]/g, '_')
}

[Crymzix]

I am facing an issue with full text search where I am getting zero results when I have multiple terms in a string. For example, I have a row where the value of the name column is S.S Perforated Sheet 4'x8'-3.0mm-Hole-4.0mm X Space-6.0mm-MAT 201 Grade. I want to be able to search this string 3.0mm 4.0mm 6.0mm and have the query return results. Currently, I am getting zero results when I pass this string in. My pre-processing function is like so:

const preprocessSearchTerms = (searchTerm: string) => {
	const tsquerySpecialChars = /[()|&:*!]/g
	return searchTerm
                .trim()
		.replace(tsquerySpecialChars, ' ')
		.split(/\s+/)
		.join(' & ')
}

Calling the above function gives me a string formatted as 3.0mm & 4.0mm & 6.0mm in which I pass into the search query. Why does this not return any results? Am I missing something here? Would I need to do something more with the string to get the results I want?

[revmischa]

I would be happy to take some arbitrary user-supplied search query and run it through a prisma "escape tsquery" function that deals with whitespace and escapes special chars like & for me

[nemanjam]

Why is & working as or with Postgres? Doc is pretty clear about this, but in reality code returns or results?

I see clearly it's working as or, I debugged my code.

Edit: I see it's ok, it searches cat and dog separately and not cat dog with a space. Can I search phrase with a space literally?

I see _ matches space literally but where is that documented in docs? I can't find anything about underscore.

[floelhoeffel]

Hello everyone 👋

We are starting design work to improve Prisma's support for Full Text Search (FTS)!

If you would like to help, please tell us about your needs via a short FTS user research survey.

Thank you!

[younes-alouani]

I sent you my feedback. I also have a use case where i need to search depending on the user language.

For exemple having a model "blog" with a field "title". We create two indexes : one for french and the other for spanish.

We read the user language preferences from the request cookie then search blogs using a french or spanish index accordingly.

[janpio]

This issue is technically not about the search language, so better open a new issue and fully describe your use case. As you already found out, we are working on this right now - but an explicit issue about this does not hurt.

[RockLloque]

If you want to search only for words in the exact order I recommend the postgres proximity operator.

const results = await prisma.post.findMany({
  where: {
    body: {
      search: "second post".split(" ").join(" <-> "),
    },
  },
});

[Madd0g]

this is my solution - only join words with <-> when the user doesn't do it.

// join with <-> if there's a space between two words
const fixed = query.replace(/(\w)\s+(\w)/g, '$1 <-> $2');

so the user can use operators, but if they don't - it still works. any suggestions to make it better?