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 provider. MySQL and MariaDB 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 (Preview)

In 2.16.0 and later, you can sort by properties of a relation. To enable this feature, add orderByRelation to previewFeatures in your schema:

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

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 (Preview)

In 2.19.0 and later, you can sort by the count of related records. To enable this feature, add orderByRelation to previewFeatures in your schema:

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

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.

Sorting FAQs