Prisma Filtering and Sorting

Filter and sort Prisma queries with where, AND/OR/NOT, orderBy, and cursor/offset pagination

When to Use

• Building dynamic search or filter queries against Prisma models
• Combining multiple filter conditions with boolean logic
• Sorting results by one or more fields
• Implementing pagination with cursor-based or offset strategies

Instructions

1. Basic equality filter — pass field values directly in

   where

   :

const users = await prisma.user.findMany({
  where: { role: 'ADMIN', isActive: true },
});

Multiple top-level fields are implicitly AND-ed.

2. Comparison operators — use

   equals

   ,

   not

   ,

   gt

   ,

   gte

   ,

   lt

   ,

   lte

   ,

   in

   ,

   notIn

   :

const recentPosts = await prisma.post.findMany({
  where: {
    createdAt: { gte: new Date('2024-01-01') },
    viewCount: { gt: 100 },
  },
});

3. String filters — use

   contains

   ,

   startsWith

   ,

   endsWith

   . Add

   mode: 'insensitive'

   for case-insensitive matching (PostgreSQL only):

const results = await prisma.user.findMany({
  where: { name: { contains: 'john', mode: 'insensitive' } },
});

4. Boolean combinators — use

   AND

   ,

   OR

   ,

   NOT

   for complex logic:

const filtered = await prisma.post.findMany({
  where: {
    OR: [{ title: { contains: search } }, { content: { contains: search } }],
    AND: { published: true },
    NOT: { authorId: blockedUserId },
  },
});

5. Filter on relations — use

   some

   ,

   every

   ,

   none

   for to-many relations and

   is

   ,

   isNot

   for to-one:

const usersWithPublishedPosts = await prisma.user.findMany({
  where: { posts: { some: { published: true } } },
});

6. Sort with

   orderBy

   — pass one or multiple sort fields:

const sorted = await prisma.post.findMany({
  orderBy: [{ pinned: 'desc' }, { createdAt: 'desc' }],
});

7. Sort on relations — order by a related field count or value:

const byPostCount = await prisma.user.findMany({
  orderBy: { posts: { _count: 'desc' } },
});

8. Offset pagination — use

   skip

   and

   take

   :

const page = await prisma.post.findMany({
  skip: (pageNumber - 1) * pageSize,
  take: pageSize,
  orderBy: { createdAt: 'desc' },
});

9. Cursor pagination — use

   cursor

   with

   skip: 1

   to exclude the cursor record:

const nextPage = await prisma.post.findMany({
  cursor: { id: lastPostId },
  skip: 1,
  take: 20,
  orderBy: { createdAt: 'desc' },
});

10. Aggregations — use

    aggregate

    ,

    groupBy

    , and

    count

    :

const stats = await prisma.post.aggregate({
  _avg: { viewCount: true },
  _max: { viewCount: true },
  where: { published: true },
});

Details

Prisma's filtering and sorting are fully type-safe. The available filter operators depend on the field type —

contains

only appears on

String

fields,

gt

/

lt

only on numeric and

DateTime

fields.

Offset vs cursor pagination: Offset (

skip

/

take

) is simpler to implement but degrades at high offsets because the database still scans skipped rows. Cursor pagination (

cursor

/

take

) uses an indexed value (usually

id

or

createdAt

) and performs consistently regardless of position. Use cursor pagination for infinite scroll or large datasets.

Dynamic filters: Build

where

objects conditionally to avoid unnecessary filters:

const where: Prisma.PostWhereInput = { published: true };
if (search) where.title = { contains: search, mode: 'insensitive' };
if (authorId) where.authorId = authorId;

const posts = await prisma.post.findMany({ where });

null

filtering: Use

equals: null

to find null values and

not: null

to exclude them. With

strictNullChecks

, Prisma distinguishes between "field is null" and "field is not provided in the filter."

Aggregation limitations:

• groupBy

  requires all non-aggregated fields in the

  by

  array
• You cannot aggregate on relations — use raw queries for complex aggregations like joining and grouping across tables

• count

  returns

  number

  , not

  BigInt

  , even on tables with

  BigInt

  IDs

Performance notes:

• Filtering on non-indexed fields triggers full table scans. Always check that filtered fields have database indexes

• mode: 'insensitive'

  on PostgreSQL uses

  ILIKE

  which cannot use standard B-tree indexes — create a GIN/GiST trigram index or a functional index with

  lower()

  for production workloads

Source

https://prisma.io/docs/orm/prisma-client/queries/filtering-and-sorting

Process

1. Read the instructions and examples in this document.
2. Apply the patterns to your implementation, adapting to your specific context.
3. Verify your implementation against the details and edge cases listed above.

Harness Integration

• Type: knowledge — this skill is a reference document, not a procedural workflow.
• No tools or state — consumed as context by other skills and agents.

Success Criteria

• The patterns described in this document are applied correctly in the implementation.
• Edge cases and anti-patterns listed in this document are avoided.