Queries, filtering and pagination

A product list needs filtering, sorting, searching and paging, and all of it must stay fast as the catalogue grows. This lesson builds a complete, working query layer.

The query operators

The ones you will actually use:

where: {
  isActive: true,                              // equals
  name: { contains: "atta", mode: "insensitive" },
  slug: { startsWith: "aashirvaad" },
  pricePaise: { gte: 10000, lte: 50000 },      // between
  stock: { gt: 0 },
  categoryId: { in: ["a", "b"] },
  brand: { not: null },
}

Operator	Means
equals	exact — the default when you pass a value
not	not equal
in / notIn	in a list
lt lte gt gte	comparisons
contains	substring
startsWith / endsWith	prefix / suffix
mode: "insensitive"	case-insensitive (PostgreSQL)

mode: "insensitive" is required for search that works. Without it, searching "Atta" misses "atta", and users do not capitalise.

Combining:

where: {
  AND: [{ isActive: true }, { stock: { gt: 0 } }],
  OR: [{ name: { contains: q } }, { brand: { contains: q } }],
  NOT: { categoryId: "excluded" },
}

Top-level fields are already ANDed, so AND is only needed when you build conditions dynamically.

Building a filter safely

// apps/api/src/products/products.service.ts
import { Prisma } from "@prisma/client";

type ProductQuery = {
  category?: string;
  q?: string;
  minPaise?: number;
  maxPaise?: number;
  inStock?: boolean;
  sort?: "price_asc" | "price_desc" | "name" | "newest";
  page?: number;
  limit?: number;
};

@Injectable()
export class ProductsService {
  constructor(private readonly prisma: PrismaService) {}

  private buildWhere(query: ProductQuery): Prisma.ProductWhereInput {
    const where: Prisma.ProductWhereInput = { isActive: true };

    if (query.category) {
      where.category = { slug: query.category };
    }

    if (query.q?.trim()) {
      const q = query.q.trim();
      where.OR = [
        { name: { contains: q, mode: "insensitive" } },
        { brand: { contains: q, mode: "insensitive" } },
        { description: { contains: q, mode: "insensitive" } },
      ];
    }

    const priceFilter: Prisma.IntFilter = {};
    if (query.minPaise !== undefined) priceFilter.gte = query.minPaise;
    if (query.maxPaise !== undefined) priceFilter.lte = query.maxPaise;

    const variantWhere: Prisma.VariantWhereInput = { isActive: true };
    if (Object.keys(priceFilter).length > 0) variantWhere.pricePaise = priceFilter;
    if (query.inStock) variantWhere.stock = { gt: 0 };

    where.variants = { some: variantWhere };

    return where;
  }
}

Three things worth noting.

Prisma.ProductWhereInput is a generated type. Building the object with it means a typo in a field name fails to compile — the whole point of the ORM.

isActive: true is set first, before anything else. The soft-delete filter is not optional, so it is not conditional.

Price filters the variant, not the product, because price lives on the variant. some means "has at least one variant matching", which is the correct question for "show me products under ₹500".

Sorting

private buildOrderBy(sort?: string): Prisma.ProductOrderByWithRelationInput {
  switch (sort) {
    case "name":
      return { name: "asc" };
    case "newest":
      return { createdAt: "desc" };
    default:
      return { name: "asc" };
  }
}

Always have a default, and make it deterministic. Without an orderBy, PostgreSQL returns rows in no guaranteed order — and it can differ between calls, which makes pagination return duplicates and skip rows.

Never interpolate a sort field from user input:

orderBy: { [req.query.sort as string]: "asc" }      // do not

That lets a caller sort by any column, including ones you do not expose. Map from a known set, as above.

Sorting by a relation's field — cheapest variant price — is not directly expressible here. Options: denormalise a minPricePaise onto the product and keep it updated, or use a raw query. Denormalising is usually right for a sort you need on every listing.

Pagination

Offset pagination, which is what you want for a shop:

async findAll(query: ProductQuery) {
  const page = Math.max(1, query.page ?? 1);
  const limit = Math.min(100, Math.max(1, query.limit ?? 20));
  const where = this.buildWhere(query);

  const [items, total] = await this.prisma.$transaction([
    this.prisma.product.findMany({
      where,
      orderBy: this.buildOrderBy(query.sort),
      skip: (page - 1) * limit,
      take: limit,
      select: {
        id: true,
        slug: true,
        name: true,
        brand: true,
        imageUrl: true,
        variants: {
          where: { isActive: true },
          orderBy: { pricePaise: "asc" },
          take: 1,
          select: { id: true, label: true, pricePaise: true, mrpPaise: true, stock: true },
        },
      },
    }),
    this.prisma.product.count({ where }),
  ]);

  return {
    items,
    page,
    limit,
    total,
    totalPages: Math.ceil(total / limit),
    hasNext: page * limit < total,
  };
}

Points that matter:

Clamp the limit. Math.min(100, ...) stops ?limit=1000000 from asking your database for the entire catalogue. Without it, that is a trivial way to make your API fall over.

$transaction for the two queries so the count matches the page — otherwise a product added between them gives an inconsistent total.

The same where for both. Building it twice is how they drift apart.

take: 1 on variants fetches only the cheapest, which is what a card shows.

Return the metadata, not just items. The front end needs totalPages to render pagination and hasNext to decide whether to show a Next button.

Why offset and not cursor

Cursor pagination is faster on very large tables, because skip: 200000 makes the database count through 200,000 rows.

It also cannot jump to page 5, and shops need numbered pages for users and for crawlers. With a catalogue of a few thousand products, offset is fine and simpler.

Use a cursor for an infinite-scroll feed or an export:

this.prisma.product.findMany({
  take: 20,
  skip: cursor ? 1 : 0,
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { id: "asc" },
});

Text search

contains is fine at this scale. It cannot do ranking, stemming or typo tolerance — "tomatos" finds nothing.

PostgreSQL full-text search is the next step:

where: { name: { search: "atta & select" } }

It needs previewFeatures = ["fullTextSearchPostgres"] and a GIN index to be fast.

Be honest about the ceiling. For real search — typo tolerance, relevance ranking, faceting — you want a search engine. That is out of scope here, and knowing where the boundary is matters more than crossing it.

Aggregation

const stats = await this.prisma.variant.aggregate({
  where: { isActive: true },
  _count: true,
  _min: { pricePaise: true },
  _max: { pricePaise: true },
  _avg: { pricePaise: true },
});

const byCategory = await this.prisma.product.groupBy({
  by: ["categoryId"],
  where: { isActive: true },
  _count: { _all: true },
});

aggregate for one summary, groupBy for per-group counts — a category filter showing "Staples (24)".

Performance

Index what you filter and sort on. From the modelling lesson.

Watch the query count. N+1 is the usual culprit.

EXPLAIN ANALYZE when something is slow:

const plan = await this.prisma.$queryRaw`
  EXPLAIN ANALYZE SELECT * FROM products WHERE "categoryId" = ${id}
`;

Seq Scan on a large table means no usable index.

Select fewer columns. A product list does not need descriptions.

Do not paginate in application code:

const all = await this.prisma.product.findMany();
return all.slice(0, 20);                              // fetched everything

That loads the whole table into memory. skip and take push it to the database.

Check your work

Why mode: "insensitive": users do not capitalise, so a case-sensitive search misses most matches.

Why price filters the variant: price lives on the variant, so "under ₹500" means "has a variant under ₹500" — some.

Why a default orderBy matters: without one the order is not guaranteed, so pages can repeat and skip rows.

Why not interpolate a sort field from input: it lets a caller sort by any column, including unexposed ones. Map from a known set.

Why clamp the limit: ?limit=1000000 would otherwise ask for the whole catalogue.

Why $transaction around findMany and count: so the total matches the page even if a row is inserted between them.

Offset versus cursor: offset supports numbered pages and is fine up to thousands of rows; cursor is faster on very large tables but cannot jump to a page.

Why .slice() after findMany is wrong: the whole table is fetched into memory first.

Practice

1. Build findAll with filtering, sorting and pagination. Call it with curl.
2. Search for "ATTA" in capitals and confirm it matches. Remove mode: "insensitive" and watch it fail.
3. Filter by price range and confirm it matches on the variant.
4. Request ?limit=1000000 and confirm the clamp holds.
5. Request page 2 and confirm no product appears on both pages.
6. Remove the orderBy and page through a large list looking for duplicates.
7. Return the pagination metadata and render page numbers in the front end.
8. Replace skip/take with .slice() and compare the query log and timing.
9. Add groupBy to count products per category and show the counts in the filter.
10. Run EXPLAIN ANALYZE on your category query. Drop the index, run it again, and compare.

Next: transactions, and the places where they are not optional.