Create operator for PostgreSQL trigrams (fuzzy string matching)

## Problem For string comparisons, there are filter operators like `equals`, `startsWith`, `endsWith` and `contains`. Although these are already very useful, it would be nice to take full advantage of the `pg_trgm` extension. To my knowledge there is currently no way besides a raw SQL query to fuzzy match a string. **Update**: I just realized that there are some issues going on about Full-Text-Search in prisma. So please just consider this as an API suggestion since ppl seem to be aware of the problem already (#7960) ## Suggested solution A suggested solution would be to create a new operator that can be used like the known string operators: ``` javascript const searchString = "Meier" const result = await prisma.user.findMany({ where: { lastName: { similar: searchString, threshold: 0.45 } } }); // Would return last names "Meier", "Meyer", "Maier", ... ``` ## Alternatives As an alternative, one could use a raw SQL query: ``` javascript const searchString = "Meier"; const result = await prisma.$queryRaw(`SELECT * FROM User WHERE SIMILARITY(lastName, '${searchString}') > 0.45;`) ``` ## Material fuzzy string matching https://www.postgresql.org/docs/13/pgtrgm.html https://www.freecodecamp.org/news/fuzzy-string-matching-with-postgresql/

Posted by mxcd on Jul 1, 2021, 8:56 PM

Tags

• github (Other)

Related issues

• struct's lifetime requirements can be bypassed via covariant field assignment
• Proposal to Reorganize the Moderation Team and Review Moderation Policies
• Progressive FPS degradation appears to be related to dota2.com — blocking the domain prevents the performance loss
• proposal: container/...: generic collection types
• Better Custom Endpoint BYOK
• A concrete policy for backward compatibility until Godot 5.0