Filtering Query Results
1. Using Equals Filter
| Form | Example |
|---|
| Shorthand | { status: "active" } |
| Explicit | { status: { equals: "active" } } |
2. Using NOT Filter
await prisma.user.findMany({ where: { NOT: { role: "ADMIN" } } });
| Form | Use |
|---|
{ field: { not: x } } | Single negation |
{ NOT: {...} } | Negate compound clause |
3. Using IN Operator
await prisma.user.findMany({ where: { role: { in: ["ADMIN","EDITOR"] } } });
4. Using NOT IN Operator
| Form | Detail |
|---|
{ field: { notIn: [...] } } | NOT IN (...) |
5. Comparing Values
| Operator | SQL |
|---|
gt | > |
gte | >= |
lt | < |
lte | <= |
6. Filtering String Contains
await prisma.user.findMany({
where: { email: { contains: "@example.com", mode: "insensitive" } }
});
| Operator | Behavior |
|---|
contains | Substring match (LIKE %x%) |
startsWith | Prefix |
endsWith | Suffix |
7. Using String Filters
| Filter | Use |
|---|
equals | Exact |
search | Full-text search (PG/MySQL) |
mode | "default" | "insensitive" |
8. Using Case-Insensitive Filtering
await prisma.user.findMany({
where: { name: { equals: "alice", mode: "insensitive" } }
});
| DB | Support |
|---|
| PG | ILIKE |
| MySQL | Collation-driven (utf8mb4_ci) |
| SQLite | Limited |
9. Combining Filters
await prisma.post.findMany({
where: {
AND: [
{ published: true },
{ OR: [{ tags: { some: { name: "react" } } }, { title: { contains: "TS" } }] }
]
}
});
| Operator | Use |
|---|
AND | All conditions |
OR | Any condition |
NOT | Negate clause |
10. Using Nested Relation Filters
| Filter | Use |
|---|
is / isNot | To-one relation |
some / every / none | To-many relation |
| Deep nesting | Supported for any depth |