Exercise 3: An Optional-Filter Search Endpoint — Possible Solution
====================================================================

A note on the tag filter: Post and Tag have an implicit many-to-many
relation, which Prisma stores in a hidden join table named "_PostToTag"
with columns "A" (Post id) and "B" (Tag id). Check the name in your own
migration SQL — if you ever switch to an explicit join model, the
table and column names change.

import { Prisma } from "./generated/prisma/client";

const SORTS = {
  newest:  '"createdAt" DESC',
  popular: '"viewCount" DESC',
} as const;

app.get("/search", async (req, res) => {
  const q    = typeof req.query.q === "string" ? req.query.q.trim() : "";
  const tag  = typeof req.query.tag === "string" ? req.query.tag.trim().toLowerCase() : "";
  const sort = req.query.sort === "popular" ? "popular" : "newest";

  const titleFilter = q
    ? Prisma.sql`AND p."title" ILIKE ${"%" + q + "%"}`
    : Prisma.empty;

  const tagFilter = tag
    ? Prisma.sql`AND EXISTS (
        SELECT 1 FROM "_PostToTag" pt
        JOIN "Tag" t ON t."id" = pt."B"
        WHERE pt."A" = p."id" AND t."name" = ${tag}
      )`
    : Prisma.empty;

  const rows = await prisma.$queryRaw<{ title: string; slug: string; viewCount: number }[]>`
    SELECT p."title", p."slug", p."viewCount"
    FROM "Post" p
    WHERE p."published" = true ${titleFilter} ${tagFilter}
    ORDER BY ${Prisma.raw(SORTS[sort])}
    LIMIT 20
  `;
  res.json(rows);
});

  $ curl "localhost:3000/search?q=prisma&tag=orm&sort=popular"
  $ curl "localhost:3000/search"                    # all filters optional
  $ curl "localhost:3000/search?sort=DROP%20TABLE"  # falls back to newest

WHY THIS WORKS AS AN ANSWER
------------------------------
Each optional filter is either a parameterised Prisma.sql fragment or
Prisma.empty, so the query text is assembled safely and user input is
only ever passed as values. The sort column can't be a parameter, so
the user's choice is mapped onto one of two fixed strings before
Prisma.raw sees it — an unknown value just falls back to "newest".
EXISTS avoids duplicate rows that a plain JOIN on tags could produce.
(In practice, Prisma Client could do all of this too, with
where: { tags: { some: { name: tag } } } — the point of the exercise
is to practise the raw-SQL tools for when you genuinely need them.)