prisma
prisma copied to clipboard
prisma
Add accent insensitive query mode
Problem
Currently there is a possibility to search a database case insensitive with QueryMode.insensitive. I would like to additionally search the database accent insensitive.
Suggested solution
Extend the QueryMode.insensitive to be also accent insensitive or add the QueryMode.accentInsensitive with the possibility to combine the mode with QueryMode.insensitive (it would probably be good to rename QueryMode.insensitive to QueryMode.caseInsensitive)
Hey @friebetill, I can understand the use case. This is unfortunately a very complex subject and our current implementation is best-effort and depends on a lot of factors.
Do you know how you would do this in SQL? If we have the SQL and potentially database collation settings, we can have a closer look.
Thanks for your answer! So far I haven't implemented anything, but I will probably follow this Stack Overflow answer and use the extension unaccent to solve it. I hope that this extension can be installed directly in Postgres and then used with the Prisma client via $queryRaw.
Of course, the approach does not work for the other databases and the first search result, e.g. for MySQL is not promising.
@friebetill If you've had any success with installing the module and using the function in a $rawQuery (and still have access to it, because it's been a while since you posted), please share here because I'm racking my brains to find out how to implement this :)
Unfortunately, I didn't have and still don't have the time to investigate it further.
It will be very helpful to get "unaccent" from postgreSQL working without having to use query raw
Yes same thing, the whole search and fulltextsearch features of prisma become kinda useless at the moment you have non ASCII characters contents. When you have multi language contents (japanese, chinese, ...) It become impossible to do a decent db search with default prisma tools and casing.
It's frustrating to rely on raw query just to wrap the field you search or filter with lower(unaccent(title)).
To be able to use "unnacent" with a complex/typesafe "where", I'm using 2 queries:
- The first using "queryRawUnsafe" that returning IDs
- The second using these IDs to compose a native Prisma query.
Hope it helps someone :) Does anyone have a better approach?
Accent insensitive search is very important for languages like Portuguese and others.
When trying to search "João" people may use "joao" and this should be handled by prisma to avoid doing a lot of raw queries to make that work!
Hey @friebetill, I can understand the use case. This is unfortunately a very complex subject and our current implementation is best-effort and depends on a lot of factors.
Do you know how you would do this in SQL? If we have the SQL and potentially database collation settings, we can have a closer look.
Hello there, I'm also in need of searching without having to think about accent.
Unfortunatly, I didn't have the chance yet to deep dive into the code of Prisma. So maybe what I am about to say is retarded :)
If it's complicated to handle it for all languages, maybe we could settle for a solution where Prisma provides a hook called when the unaccent parameter is set? The objective of this function would be to locally modify the query and apply PSQL functions like unaccent ?
For example ("lastname = 'BRËS'") => ("unaccent(lastname)= unaccent('BRËS')")
It would prevent the use of raw query for the time being, and once the feature is fully operational, the hook can be ignored.
Ahhhh, remembering how I love modern ORMs... "Prisma is a next-generation Node.js and TypeScript ORM that unlocks a new level of developer experience"
