Last active
May 3, 2024 17:39
-
-
Save Cauen/72a03ecbbe5b06ab70bb3b24fe6648ab to your computer and use it in GitHub Desktop.
Prisma Accent Case + Complex Query
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Raw Query | |
const getUnnacentPersonIdsByNameOrNickname = async (text?: string | null) => { | |
if (!text) return [] | |
// Search accent case with View | |
const unnacentLower = (str: string) => | |
str | |
.normalize('NFD') | |
.replace(/[\u0300-\u036f]/g, '') | |
.toLocaleLowerCase() | |
const nameWhere = `unaccent(lower(name)) ilike '%${unnacentLower( | |
text, | |
)}%'` | |
const nickname = `unaccent(lower(nickname)) ilike '%${unnacentLower( | |
text, | |
)}%'` | |
const where = [ | |
...(nameWhere ? [nameWhere] : []), | |
...(nickname ? [nickname] : []), | |
].join(' OR ') | |
const founds: Pick<Lawsuit, 'id'>[] = await prisma.$queryRawUnsafe( | |
`SELECT id FROM "Person" WHERE ${where};`, | |
) | |
const ids = founds.map((el) => el.id) | |
return ids | |
} | |
// Append IDs in complex "where.OR" | |
const getCustomOrPersonFilter = async (textSearch?: string | null): Promise<Prisma.Enumerable<Prisma.PersonWhereInput>> => { | |
if (!textSearch) return [] | |
const unnacentPersonIds = await getUnnacentPersonIdsByNameOrNickname(textSearch) | |
return [ | |
{ | |
CPF: { | |
contains: textSearch, | |
mode: "insensitive" | |
} | |
}, | |
{ | |
CNPJ: { | |
contains: textSearch, | |
mode: "insensitive" | |
} | |
}, | |
{ | |
PersonTags: { | |
some: { | |
name: { | |
contains: textSearch, | |
mode: "insensitive" | |
} | |
} | |
} | |
}, | |
{ id: { in: unnacentPersonIds } } | |
] | |
} | |
// Usage | |
const personList = await prisma.person.findMany({ | |
where: { | |
OR: await getCustomOrPersonFilter(args.search) | |
} | |
}) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment