Filtering and sorting

Filter conditions and operators

Refer to the Prisma Client reference documentation for a full list of operators , such as startsWith and contains.

const result = await prisma.user.findMany({
  where: {
    OR: [
      {
        email: {
          endsWith: 'prisma.io',
        },
      },
      { email: { endsWith: 'gmail.com' } },
    ],
    NOT: {
      email: {
        endsWith: 'hotmail.com',
      },
    },
  },
  select: {
    email: true,
  },
})

Filter on relations

Prisma Client supports filtering on related records. For example, in the following schema, a user can have many blog posts:

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  email String  @unique
  posts Post[] // User can have many posts
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  published Boolean @default(true)
  author    User    @relation(fields: [authorId], references: [id])
  authorId  Int
}

The one-to-many relation between User and Post allows you to query users based on their posts - for example, the following query returns all users where at least one post (some) has more than 10 views:

const result = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        views: {
          gt: 10,
        },
      },
    },
  },
})

You can also query posts based on the properties of the author. For example, the following query returns all posts where the author's email contains "prisma.io":

const res = await prisma.post.findMany({
  where: {
    author: {
      email: {
        contains: 'prisma.io',
      },
    },
  },
})

Filter on scalar lists / arrays

Scalar lists (for example, String[]) have a special set of filter conditions - for example, the following query returns all posts where the tags array contains databases:

const posts = await client.post.findMany({
  where: {
    tags: {
      has: 'databases',
    },
  },
})

Case-insensitive filtering

Case-insensitive filtering is available as a feature for the PostgreSQL and MongoDB providers. MySQL, MariaDB and Microsoft SQL Server are case-insensitive by default, and do not require a Prisma Client feature to make case-insensitive filtering possible.

To use case-insensitive filtering, add the mode property to a particular filter and specify insensitive:

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
      mode: 'insensitive', // Default value: default
    },
    name: {
      equals: 'Archibald', // Default mode
    },
  },
})

See also: Case sensitivity

Sort by relation

You can also sort by properties of a relation. For example, the following query sorts all posts by the author's email address:

const posts = await prisma.post.findMany({
  orderBy: {
    author: {
      email: 'asc',
    },
  },
})

Sort by relation aggregate value

In 2.19.0 and later, you can sort by the count of related records.

For example, the following query sorts users by the number of related posts:

const getActiveUsers = await prisma.user.findMany({
  take: 10,
  orderBy: {
    posts: {
      _count: 'desc',
    },
  },
})

Note: It is not currently possible to return the count of a relation.

Sort by relevance (PostgreSQL)

In 3.5.0 and later, when using PostgresSQL you can sort records by relevance to the query using the _relevance keyword. This uses the relevance ranking functions from PostgreSQL's full text search feature, which are explained further in the PostgreSQL documentation.

Enable order by relevance with the fullTextSearch preview feature:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}

Ordering by relevance can be used either separately from or together with the search filter: _relevance is used to order the list, while search filters the unordered list. For example, the following query uses _relevance to order the list of users by relevance to the search term 'developer' in their bio, and search to filter the list:

const getUsersByRelevance = await prisma.user.findMany({
  take: 10,
  orderBy: {
    _relevance: {
      fields: ['bio'],
      search: 'developer',
      sort: 'asc',
    },
  },
})

Sort with null records first or last

In version 4.1.0 and later, you can sort the results so that records with null fields appear either first or last.

To use this feature, in the generator block of your schema.prisma file, enable the orderByNulls preview feature:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["orderByNulls"]
}

Example: If updatedAt is an optional field, then the following query sorts posts by updatedAt, with null records at the end:

const posts = await prisma.post.findMany({
  orderBy: {
    updatedAt: { sort: 'asc', nulls: 'last' },
  },
})

Sorting FAQs