Prisma Filtering and Sorting — Advanced Query Conditions and Ordering

DodaTech Updated 2026-06-28 7 min read

In this tutorial, you will learn about Prisma Filtering and Sorting. We cover key concepts, practical examples, and best practices to help you master this topic.

Prisma filtering and sorting enables precise data retrieval using where conditions with comparison operators, string matching, date ranges, combined filters with AND/OR/NOT, and sorting by single or multiple fields.

What You'll Learn

By the end of this lesson you will use filter operators for strings, numbers, and dates, combine conditions with AND/OR/NOT, sort by multiple fields, filter on related models, and use case-insensitive search.

Why It Matters

Real-world applications rarely need all records. Filtering and sorting are essential for search, reporting, dashboards, and API endpoints that serve paginated, ordered results.

Real-World Use

DodaZIP's file listing API uses complex filters: users can filter files by name (case-insensitive search), date range, size range, and status, with sorting by date or name. All filters are combined with AND.

Filter Operators

Prisma provides operators for all data types.

// String filters
const users = await prisma.user.findMany({
  where: {
    name: { contains: 'john' },       // LIKE '%john%'
    name: { startsWith: 'A' },         // LIKE 'A%'
    name: { endsWith: 'son' },         // LIKE '%son'
    name: { mode: 'insensitive' },     // Case-insensitive
    email: { in: ['a@b.com', 'c@d.com'] },
  },
});

// Numeric filters
const files = await prisma.file.findMany({
  where: {
    sizeBytes: { gt: 1000000 },       // Greater than
    sizeBytes: { gte: 1000 },          // Greater than or equal
    sizeBytes: { lt: 10000000 },       // Less than
    sizeBytes: { lte: 5000 },          // Less than or equal
    sizeBytes: { not: 0 },             // Not equal
    sizeBytes: { in: [100, 200, 300] },
  },
});

// Date filters
const posts = await prisma.post.findMany({
  where: {
    createdAt: {
      gt: new Date('2026-01-01'),
      lt: new Date('2026-12-31'),
    },
  },
});

# filter_operators.py
# Available filter operators

def filter_operators():
    operators = {
        "equals": "Exact match",
        "not": "Not equal",
        "in": "In list of values",
        "notIn": "Not in list",
        "lt": "Less than",
        "lte": "Less than or equal",
        "gt": "Greater than",
        "gte": "Greater than or equal",
        "contains": "String contains substring",
        "startsWith": "String starts with",
        "endsWith": "String ends with",
    }
    
    print("Filter Operators:")
    print(f"  {'Operator':15s} | {'Description'}")
    print("  " + "-" * 40)
    for op, desc in operators.items():
        print(f"  {op:15s} | {desc}")

filter_operators()

Combining Conditions

Use AND, OR, and NOT for complex filters.

// AND (all conditions must match)
const files = await prisma.file.findMany({
  where: {
    AND: [
      { status: 'PROCESSING' },
      { sizeBytes: { gt: 1000000 } },
      { createdAt: { gt: new Date('2026-01-01') } },
    ],
  },
});

// OR (any condition can match)
const users = await prisma.user.findMany({
  where: {
    OR: [
      { role: 'ADMIN' },
      { status: 'VIP' },
    ],
  },
});

// NOT (exclude matching records)
const files = await prisma.file.findMany({
  where: {
    NOT: { status: 'DELETED' },
  },
});

// Mixed combinations
const results = await prisma.post.findMany({
  where: {
    published: true,
    OR: [
      { title: { contains: 'Prisma' } },
      { content: { contains: 'Prisma' } },
    ],
    NOT: { authorId: null },
  },
});

# combining.py
# Condition combination patterns

def combine_conditions():
    print("Condition Combination Patterns:")
    print()
    print("AND: All conditions must be true")
    print("  where: { AND: [cond1, cond2] }")
    print("  where: { field1: val1, field2: val2 } (implicit AND)")
    print()
    print("OR: Any condition can be true")
    print("  where: { OR: [cond1, cond2] }")
    print()
    print("NOT: Exclude matching records")
    print("  where: { NOT: condition }")
    print()
    print("Nested combinations:")
    print("  where: {")
    print("    published: true,")
    print("    OR: [{ field: val1 }, { field: val2 }],")
    print("    NOT: { field: val3 }")
    print("  }")

combine_conditions()

Sorting

Order results by one or multiple fields.

// Single field sort
const users = await prisma.user.findMany({
  orderBy: { createdAt: 'desc' },
});

// Multiple field sort
const files = await prisma.file.findMany({
  orderBy: [
    { status: 'asc' },
    { createdAt: 'desc' },
  ],
});

// Sort by relation field
const posts = await prisma.post.findMany({
  orderBy: {
    author: { name: 'asc' },
  },
});

// Sort with pagination
const users = await prisma.user.findMany({
  orderBy: { name: 'asc' },
  take: 10,
  skip: 20,
});

# sorting.py
# Sorting patterns

def sorting_patterns():
    print("Sorting Patterns:")
    print()
    print("Single field:")
    print("  orderBy: { field: 'asc' }")
    print("  orderBy: { field: 'desc' }")
    print()
    print("Multiple fields:")
    print("  orderBy: [")
    print("    { status: 'asc' },")
    print("    { createdAt: 'desc' }")
    print("  ]")
    print()
    print("By relation field:")
    print("  orderBy: {")
    print("    author: { name: 'asc' }")
    print("  }")
    print()
    print("Null handling:")
    print("  - Nulls are sorted last by default")
    print("  - Use sort: 'nullsFirst' or 'nullsLast'")

sorting_patterns()

Filtering on Relations

Filter records based on related model conditions.

// Filter users who have posts
const usersWithPosts = await prisma.user.findMany({
  where: {
    posts: { some: { published: true } },
  },
});

// Filter users who have no posts
const usersWithoutPosts = await prisma.user.findMany({
  where: {
    posts: { none: {} },
  },
});

// Filter where every post matches
const usersWithAllPublished = await prisma.user.findMany({
  where: {
    posts: { every: { published: true } },
  },
});

// Filter by relation field value
const users = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        title: { contains: 'Prisma' },
      },
    },
  },
  include: { posts: { where: { published: true } } },
});

# relation_filters.py
# Filtering on relations

def relation_filters():
    print("Relation Filter Operators:")
    print()
    print("some: At least one related record matches")
    print("  posts: { some: { published: true } }")
    print()
    print("none: No related record matches")
    print("  posts: { none: { status: 'DELETED' } }")
    print()
    print("every: All related records match")
    print("  posts: { every: { published: true } }")
    print()
    print("is: Exact match for one-to-one")
    print("  profile: { is: { bio: { contains: 'developer' } } }")

relation_filters()

Common Mistakes

Forgetting mode: 'insensitive' for case-insensitive search: By default, PostgreSQL string matching is case-sensitive. Use mode: 'insensitive' for case-insensitive search.
Mixing AND/OR incorrectly: Nested AND/OR combinations can produce unexpected results. Use explicit AND/OR arrays for complex conditions.
Not using relation filters efficiently: Using some on large relations can be slow. Add indexes on foreign key columns.
Ordering by unindexed fields: Sorting on unindexed columns is slow for large datasets. Add indexes for frequently sorted fields.
Using contains without an index: Full text search (contains) is slow without an index. Consider full-text search indexes for large text fields.

Practice Questions

How do you filter records where a string contains a substring? Use where: { field: { contains: 'substring' } }.
How do you combine multiple conditions with OR? Use where: { OR: [condition1, condition2] }.
How do you sort by multiple fields? Use orderBy: [{ field1: 'asc' }, { field2: 'desc' }].
How do you find users who have at least one published post? Use where: { posts: { some: { published: true } } }.
Challenge: Create a search API endpoint that supports filtering by name (case-insensitive), date range, status, and sorting by any field with ascending/descending order.

FAQ

Can I use contains with case-insensitive mode on all databases?

PostgreSQL supports mode: 'insensitive'. MySQL is case-insensitive by default. SQLite requires raw queries.

How do I filter by null values?

Use where: { field: null } or where: { field: { not: null } }.

Can I filter by a field in a related model?

Yes. Use relation filter operators: some, none, every, is.

How do I get distinct values?

Use prisma.modelName.findMany({ distinct: ['fieldName'] }).

Can I use raw SQL in where conditions?

Yes. Use prisma.$queryRaw for complex filters that Prisma does not support.

Mini Project

Create a file search service that supports filtering by name, size range, date range, status, and sorting by any field with both ascending and descending order.

def file_search_service():
    print("File Search Service:")
    print()
    print("listFiles({")
    print("  name: string?,           # contains, case-insensitive")
    print("  minSize: number?,         # size >= minSize")
    print("  maxSize: number?,         # size <= maxSize")
    print("  status: string?,          # exact match")
    print("  fromDate: Date?,          # created >= fromDate")
    print("  toDate: Date?,            # created <= toDate")
    print("  sortBy: string?,          # field name")
    print("  sortOrder: 'asc'|'desc', # default 'asc'")
    print("  page: number,             # page number")
    print("  pageSize: number          # items per page")
    print("})")

file_search_service()

What's Next

Next: Prisma Pagination for paginated queries.

← Previous Prisma CRUD Operations — Create, Read, Update, and Delete Data Next → Prisma Pagination — Offset and Cursor-Based Pagination

Built by the developers of DodaTech

Doda Browser, DodaZIP & Durga Antivirus Pro

Home Browse Prisma