Implementing Search and Filtering

app.get("/products", (req, res) => {
  const q = (req.query.search || "").trim();
  // ... build query
});

SELECT * FROM products WHERE name ILIKE '%' || $1 || '%' LIMIT 50;

const items = await prisma.product.findMany({
  where: { name: { contains: q, mode: "insensitive" } },
  take: 50
});

const where = {
  AND: [
    req.query.category && { category: req.query.category },
    req.query.minPrice && { price: { gte: Number(req.query.minPrice) } },
    req.query.maxPrice && { price: { lte: Number(req.query.maxPrice) } }
  ].filter(Boolean)
};

const { from, to } = req.query;
const where = {
  createdAt: {
    ...(from && { gte: new Date(from) }),
    ...(to   && { lte: new Date(to) })
  }
};

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products USING GIN (name gin_trgm_ops);

SELECT *, similarity(name, $1) AS score
FROM products
WHERE name % $1
ORDER BY score DESC
LIMIT 20;

function buildProductFilter(q) {
  const where = {};
  if (q.search)   where.OR = [{ name: { contains: q.search } }, { sku: q.search }];
  if (q.category) where.category = q.category;
  if (q.inStock === "true") where.stock = { gt: 0 };
  if (q.minPrice || q.maxPrice) {
    where.price = {};
    if (q.minPrice) where.price.gte = Number(q.minPrice);
    if (q.maxPrice) where.price.lte = Number(q.maxPrice);
  }
  return where;
}

const filterSchema = z.object({
  search:   z.string().max(100).optional(),
  category: z.enum(["books","music","games"]).optional(),
  minPrice: z.coerce.number().min(0).optional(),
  maxPrice: z.coerce.number().min(0).optional(),
  inStock:  z.enum(["true","false"]).optional()
}).refine(d => !d.minPrice || !d.maxPrice || d.minPrice <= d.maxPrice,
          { message: "minPrice must be ≤ maxPrice" });

ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX articles_search_idx ON articles USING GIN (search);

SELECT *, ts_rank(search, q) AS rank
FROM articles, plainto_tsquery('english', $1) q
WHERE search @@ q
ORDER BY rank DESC LIMIT 20;

function sanitizeSearch(s) {
  return String(s)
    .slice(0, 100)                  // length cap
    .replace(/[%_\\]/g, "\\
function sanitizeSearch(s) {
  return String(s)
    .slice(0, 100)                  // length cap
    .replace(/[%_\\]/g, "\\$&");   // escape SQL LIKE wildcards
}
#x26;");   // escape SQL LIKE wildcards
}