Hi, is there a way to search across multiple fields but get the best result? For example, I have columns

firstName

,

lastName

,

street

,

houseNumber

, and if I only provide a name

John Doe

it should return all John Doe's. But if I search for

John Doe Mainroad 15

it should only return the John Doe on Mainroad 15. I currently have a

OR: []

clause with

contains

/`search` conditions, but because of the

OR

it also returns all entires with house number 15. But with

AND

it would not return any result, so I can't use that either.

Hey Julian 👋 For these kinds of search-specific use cases, I think ElasticSearch or any other search-specific tools would be a good choice. They allow you to define the relevance of the search terms, typo-tolerance etc right out of the box

Ah okay, thanks! ElasticSearch is a separate database and service, right? Or is there an easy way to integrate with my current Prisma setup? What I have done now is do a raw query with

to_tsvector

and

to_tsquery

, and do a

findMany

with

where: { id: { in: [list of IDs from raw query] } }

to include relationships and take/skip logic.

Elasticsearch is indeed a separate database service! Your approach of using raw query looks good but for the extreme use cases that you mentioned a dedicated search tool is more preferable.