Filtering, Sorting & Pagination

Prisma Fundamentals

Chapter 6 · Filtering, Sorting & Pagination

Chapter 5's queries used simple conditions such as { published: false }. Real applications need more: posts with "prisma" in the title, published this month, newest first, twenty to a page. This chapter covers the filtering, sorting and paging options, and one trap — case sensitivity — that behaves differently on every database.

Filter Operators

Writing { published: true } is shorthand for { published: { equals: true } }. Instead of a plain value, a field can take an object of operators:

Operator	Example	Matches
`equals`, `not`	`{ title: { not: "Draft" } }`	Equal / not equal
`in`, `notIn`	`{ id: { in: [1, 2, 3] } }`	Any value in the list / none of them
`lt`, `lte`, `gt`, `gte`	`{ viewCount: { gte: 100 } }`	Less than, less or equal, greater than, greater or equal
`contains`	`{ title: { contains: "prisma" } }`	Text containing the value
`startsWith`, `endsWith`	`{ slug: { startsWith: "how-to" } }`	Text beginning / ending with the value

// Popular posts published since the start of September
const popular = await prisma.post.findMany({
  where: {
    published: true,
    viewCount: { gte: 100 },
    publishedAt: { gte: new Date("2026-09-01") },
  },
});

Several conditions at the top level of where are combined with AND: a post must match all of them.

Matching NULL

// Posts with no content yet
await prisma.post.findMany({ where: { content: null } });

// Posts that do have content
await prisma.post.findMany({ where: { content: { not: null } } });

undefined means "ignore this condition"

null and undefined are very different in a where. { content: null } finds rows where content is empty; { content: undefined } is ignored completely. So if a search function builds where: { title: searchTerm } and searchTerm is undefined, the filter vanishes and you get every post. Check inputs before using them.

Combining Conditions: AND, OR and NOT

// Published posts mentioning "prisma" in the title OR the content
const results = await prisma.post.findMany({
  where: {
    published: true,
    OR: [
      { title:   { contains: "prisma" } },
      { content: { contains: "prisma" } },
    ],
    NOT: { slug: { startsWith: "test-" } },
  },
});

OR takes a list, and a row matches if any item matches. AND takes a list too, and is useful when you need the same field twice. NOT excludes rows that match. They can be nested as deeply as you need.

Case Sensitivity: Different on Every Database

Does contains: "prisma" match a title containing "Prisma"? It depends on the database.

Database	Default text matching	Case-insensitive option
PostgreSQL	Case-sensitive	Add `mode: "insensitive"`
MongoDB	Case-sensitive	Add `mode: "insensitive"`
MySQL	Usually case-insensitive (depends on the column's collation)	Already insensitive by default
SQL Server	Usually case-insensitive	Already insensitive by default
SQLite	Not case-insensitive for columns Prisma creates	Edit the migration to add `COLLATE NOCASE` to the column

// PostgreSQL: match "Prisma", "PRISMA" and "prisma"
await prisma.post.findMany({
  where: { title: { contains: "prisma", mode: "insensitive" } },
});

A search that works in development can fail in production

If you develop on SQLite and deploy on PostgreSQL (or the reverse), the same search can return different results. Develop against the same kind of database you run in production, and test searches with mixed-case text.

Sorting

// Newest first
await prisma.post.findMany({ orderBy: { createdAt: "desc" } });

// Several fields: most viewed first, then alphabetical among equal counts
await prisma.post.findMany({
  orderBy: [{ viewCount: "desc" }, { title: "asc" }],
});

// Choose where empty values go
await prisma.post.findMany({
  orderBy: { publishedAt: { sort: "desc", nulls: "last" } },
});

Always sort when paging

Without orderBy, the database may return rows in any order it likes, and that order can change between queries. Paging without a fixed sort can show the same post twice or skip one entirely.

Pagination

Offset pagination: take and skip

const pageSize = 20;
const page = 3;   // pages numbered from 1

const posts = await prisma.post.findMany({
  where:   { published: true },
  orderBy: { createdAt: "desc" },
  skip:    (page - 1) * pageSize,
  take:    pageSize,
});
const total = await prisma.post.count({ where: { published: true } });

Simple, and it lets users jump to any page number. But the database still has to count past every skipped row, so very deep pages get slower, and if posts are added while someone is paging, items shift between pages.

Cursor pagination

// First page
const firstPage = await prisma.post.findMany({
  take: 20,
  orderBy: { id: "asc" },
});
const lastId = firstPage[firstPage.length - 1]?.id;

// Next page: start at the last post seen, and skip that post itself
const nextPage = await prisma.post.findMany({
  take: 20,
  skip: 1,
  cursor: { id: lastId },
  orderBy: { id: "asc" },
});

	Offset (`skip`/`take`)	Cursor
Jump to page 7	Yes	No — only next/previous
Speed on deep pages	Gets slower	Stays fast
New rows while paging	Items can shift or repeat	Stable
Good for	Admin tables, numbered pages	Infinite scroll, feeds, large tables

Removing Duplicates: distinct

// One row per distinct role
const roles = await prisma.user.findMany({
  distinct: ["role"],
  select: { role: true },
});

Hands-On Exercises

Exercise 1

Write a query for published posts that either have at least 50 views or were published in the last 7 days, excluding any whose slug starts with draft-, sorted by views (most first) then title.

📄 View solution

Exercise 2

Write a searchPosts(term?: string, page = 1) function that returns 10 published posts per page, newest first, filtered by title if a term is given, along with the total number of pages. Make sure a missing term can't accidentally return drafts.

📄 View solution

Exercise 3

On your SQLite project, create posts titled "Prisma Tips" and "prisma tricks", then search with contains: "prisma". Explain the result, and what you'd change to make the search case-insensitive on SQLite and on PostgreSQL.

📄 View solution

Chapter 6 Quick Reference

Operators: equals, not, in, notIn, lt/lte/gt/gte, contains, startsWith, endsWith
Top-level conditions are ANDed; combine with AND, OR (lists) and NOT
null matches empty values; undefined removes the condition entirely
Case sensitivity varies: mode: "insensitive" on PostgreSQL and MongoDB; MySQL and SQL Server are usually insensitive; SQLite needs COLLATE NOCASE
orderBy takes one object or a list; { sort, nulls: "first" | "last" } controls empty values
Offset paging: skip + take (plus count for totals); cursor paging: cursor, skip: 1, take
Always orderBy when paging; distinct removes duplicate rows