Filtering & Dynamic WHERE Clauses¶

Clients want "show me TODO tasks due this week tagged urgent." You build that where from query parameters — safely.

Parse Query Params with a Schema¶

8 lines

1const Query = z.object({
2  status: z.enum(["TODO", "IN_PROGRESS", "DONE"]).optional(),
3  tag: z.string().optional(),
4  dueBefore: z.coerce.date().optional(),
5});
6const { status, tag, dueBefore } = Query.parse(
7  Object.fromEntries(req.nextUrl.searchParams),
8);

Validate filters just like a body — they're untrusted input too.

Build the WHERE Conditionally¶

8 lines

1const where: Prisma.TaskWhereInput = {
2  ownerId: session.userId,             // always scope to the caller
3  ...(status && { status }),
4  ...(dueBefore && { dueDate: { lte: dueBefore } }),
5  ...(tag && { tags: { some: { name: tag } } }),
6};
7
8const tasks = await prisma.task.findMany({ where, take: 20 });

Each filter is added only when present. The base scope (ownerId) is never optional.

Combining Conditions: AND / OR¶

7 lines

1where: {
2  ownerId,
3  OR: [
4    { title: { contains: q, mode: "insensitive" } },
5    { description: { contains: q, mode: "insensitive" } },
6  ],
7}

Filter on the Database, Not in App Code¶

6 lines

1// ❌ fetch everything, filter in JS — slow, memory-heavy
2const all = await prisma.task.findMany();
3const todo = all.filter((t) => t.status === "TODO");
4
5// ✅ let the database do it
6const todo = await prisma.task.findMany({ where: { status: "TODO" } });

Push every filter, sort, and limit into the query. The database has indexes; your Array.filter does not.