Skip to main content

过滤和排序

Prisma 客户端支持 filtering 和 where 查询选项，以及 sorting 和 orderBy 查询选项。

¥Prisma Client supports filtering with the where query option, and sorting with the orderBy query option.

过滤​

¥Filtering

Prisma Client 允许你过滤模型字段 包括相关型号 的任意组合上的记录，并支持各种 过滤条件。

¥Prisma Client allows you to filter records on any combination of model fields, including related models, and supports a variety of filter conditions.

warning

某些过滤条件使用 SQL 运算符 LIKE 和 ILIKE，这可能会导致查询出现意外行为。更多信息请参阅 我们的过滤常见问题解答。

¥Some filter conditions use the SQL operators LIKE and ILIKE which may cause unexpected behavior in your queries. Please refer to our filtering FAQs for more information.

以下查询：

¥The following query:

  • 返回所有 User 记录：

    ¥Returns all User records with:

    • 以 prisma.io 结尾的电子邮件地址和

      ¥an email address that ends with prisma.io and

    • 至少一篇已发布的帖子（关系查询）

      ¥at least one published post (a relation query)

  • 返回所有 User 字段

    ¥Returns all User fields

  • 包括所有相关的 Post 记录，其中 published 等于 true

    ¥Includes all related Post records where published equals true

const result = await prisma.user.findMany({
  where: {
    email: {
      endsWith: 'prisma.io',
    },
    posts: {
      some: {
        published: true,
      },
    },
  },
  include: {
    posts: {
      where: {
        published: true,
      },
    },
  },
})
Show CLI results

过滤条件和运算符​

¥Filter conditions and operators

请参阅 Prisma Client 的 运算符的完整列表 参考文档，例如 startsWith 和 contains。

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

组合运算符​

¥Combining operators

你可以使用运算符（例如 NOT 和 OR ）按条件组合进行过滤。以下查询返回 email 以 gmail.com 或 company.com 结尾的所有用户，但不包括以 admin.company.com 结尾的任何电子邮件

¥You can use operators (such as NOT and OR ) to filter by a combination of conditions. The following query returns all users whose email ends with gmail.com or company.com, but excludes any emails ending with admin.company.com

const result = await prisma.user.findMany({
  where: {
    OR: [
      {
        email: {
          endsWith: 'gmail.com',
        },
      },
      { email: { endsWith: 'company.com' } },
    ],
    NOT: {
      email: {
        endsWith: 'admin.company.com',
      },
    },
  },
  select: {
    email: true,
  },
})
Show CLI results

过滤空字段​

¥Filter on null fields

以下查询返回 content 字段为 null 的所有帖子：

¥The following query returns all posts whose content field is null:

const posts = await prisma.post.findMany({
  where: {
    content: null,
  },
})

过滤非空字段​

¥Filter for non-null fields

以下查询返回 content 字段不是 null 的所有帖子：

¥The following query returns all posts whose content field is not null:

const posts = await prisma.post.findMany({
  where: {
    content: { not: null },
  },
})

过滤关系​

¥Filter on relations

Prisma 客户端支持 过滤相关记录。例如，在以下架构中，用户可以拥有许多博客文章：

¥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
}

User 和 Post 之间的一对多关系允许你根据用户的帖子查询用户 - 例如，以下查询返回至少一篇帖子 (some) 的浏览量超过 10 次的所有用户：

¥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,
        },
      },
    },
  },
})

你还可以根据作者的属性查询帖子。例如，以下查询返回作者的 email 包含 "prisma.io" 的所有帖子：

¥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

标量列表（例如 String[]）有一组特殊的 过滤条件 - 例如，以下查询返回 tags 数组包含 databases 的所有帖子：

¥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

不区分大小写的过滤 作为 PostgreSQL 和 MongoDB 提供商的一项功能提供。MySQL、MariaDB 和 Microsoft SQL Server 默认情况下不区分大小写，并且不需要 Prisma 客户端功能即可实现不区分大小写的过滤。

¥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.

要使用不区分大小写的过滤，请将 mode 属性添加到特定过滤器并指定 insensitive：

¥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

过滤常见问题解答​

¥Filtering FAQs

数据库级别的过滤如何工作？​

¥How does filtering work at the database level?

对于 MySQL 和 PostgreSQL，Prisma 客户端利用 LIKE（和 ILIKE）运算符来搜索给定模式。这些运算符具有使用 LIKE 特有符号的内置模式匹配。模式匹配符号包括 % 表示零个或多个字符（类似于其他正则表达式实现中的 *）和 _ 表示一个字符（类似于 .）

¥For MySQL and PostgreSQL, Prisma Client utilizes the LIKE (and ILIKE) operator to search for a given pattern. The operators have built-in pattern matching using symbols unique to LIKE. The pattern-matching symbols include % for zero or more characters (similar to * in other regex implementations) and _ for one character (similar to .)

要匹配字面量字符 % 或 _，请确保对这些字符进行转义。例如：

¥To match the literal characters, % or _, make sure you escape those characters. For example:

const users = await prisma.user.findMany({
  where: {
    name: {
      startsWith: '_benny',
    },
  },
})

上述查询将匹配名称以字符开头并后跟 benny（例如 7benny 或 &benny）的任何用户。如果你想查找名称以字面量字符串 _benny 开头的任何用户，你可以这样做：

¥The above query will match any user whose name starts with a character followed by benny such as 7benny or &benny. If you instead wanted to find any user whose name starts with the literal string _benny, you could do:

const users = await prisma.user.findMany({
  where: {
    name: {
      startsWith: '\\_benny', // note that the `_` character is escaped, preceding `\` with `\` when included in a string
    },
  },
})

排序​

¥Sorting

使用 orderBy 按特定字段或字段集对记录列表或嵌套记录列表进行排序。例如，以下查询返回按 role 和 name 排序的所有 User 记录，以及按 title 排序的每个用户的帖子：

¥Use orderBy to sort a list of records or a nested list of records by a particular field or set of fields. For example, the following query returns all User records sorted by role and name, and each user's posts sorted by title:

const usersWithPosts = await prisma.user.findMany({
  orderBy: [
    {
      role: 'desc',
    },
    {
      name: 'desc',
    },
  ],
  include: {
    posts: {
      orderBy: {
        title: 'desc',
      },
      select: {
        title: true,
      },
    },
  },
})
Show CLI results

注意：你还可以 对嵌套记录列表进行排序 按 ID 检索单个记录。

¥Note: You can also sort lists of nested records to retrieve a single record by ID.

按关系排序​

¥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

在 2.19.0 及更高版本中，你可以按相关记录的数量排序。

¥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.

按相关性排序（PostgreSQL 和 MySQL）​

¥Sort by relevance (PostgreSQL and MySQL)

在 PostgreSQL 的 3.5.0+ 和 MySQL 的 3.8.0+ 中，你可以使用 _relevance 关键字按与查询的相关性对记录进行排序。这使用了全文搜索功能中的相关性排名函数。

¥In 3.5.0+ for PostgreSQL and 3.8.0+ for MySQL, you can sort records by relevance to the query using the _relevance keyword. This uses the relevance ranking functions from full text search features.

此功能在 PostgreSQL 文档 和 MySQL 文档 中进一步解释。

¥This feature is further explain in the PostgreSQL documentation and the MySQL documentation.

对于 PostgreSQL，你需要使用 fullTextSearchPostgres 预览功能 启用按相关性排序：

¥For PostgreSQL, you need to enable order by relevance with the fullTextSearchPostgres preview feature:

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

按相关性排序可以单独使用 search 过滤器，也可以与 search 过滤器一起使用：_relevance 用于对列表进行排序，而 search 则对无序列表进行过滤。

¥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.

例如，以下查询使用 _relevance 按 bio 字段中的术语 developer 进行过滤，然后按相关性降序对结果进行排序：

¥For example, the following query uses _relevance to filter by the term developer in the bio field, and then sorts the result by relevance in a descending manner:

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

注意

在 Prisma ORM 5.16.0 之前，启用 fullTextSearch 预览功能会将 <Model>OrderByWithRelationInput TypeScript 类型重命名为 <Model>OrderByWithRelationAndSearchRelevanceInput。如果你正在使用预览功能，则需要更新类型导入。

¥Prior to Prisma ORM 5.16.0, enabling the fullTextSearch preview feature would rename the <Model>OrderByWithRelationInput TypeScript types to <Model>OrderByWithRelationAndSearchRelevanceInput. If you are using the Preview feature, you will need to update your type imports.

首先或最后以空记录排序​

¥Sort with null records first or last

信息

注意：

¥Notes:

  • 此功能通常在 4.16.0 及更高版本中提供。要在版本 4.1.0 至 4.15.0 中使用此功能，需要启用 预览功能 orderByNulls。

    ¥This feature is generally available in version 4.16.0 and later. To use this feature in versions 4.1.0 to 4.15.0 the Preview feature orderByNulls will need to be enabled.

  • 此功能不适用于 MongoDB。

    ¥This feature is not available for MongoDB.

  • 你只能按可选 scalar 字段上的空值进行排序。如果你尝试按必填字段或 relation 字段上的空值进行排序，Prisma 客户端会抛出 P2009 错误。

    ¥You can only sort by nulls on optional scalar fields. If you try to sort by nulls on a required or relation field, Prisma Client throws a P2009 error.

你可以对结果进行排序，以便具有 null 字段的记录出现在最前面或最后。

¥You can sort the results so that records with null fields appear either first or last.

如果 name 是可选字段，则使用 last 的以下查询按 name 对用户进行排序，末尾有 null 记录：

¥If name is an optional field, then the following query using last sorts users by name, with null records at the end:

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

如果你希望具有 null 值的记录出现在返回数组的开头，请使用 first：

¥If you want the records with null values to appear at the beginning of the returned array, use first:

const users = await prisma.user.findMany({
  orderBy: {
    updatedAt: { sort: 'asc', nulls: 'first' },
  },
})

请注意，first 也是默认值，因此如果省略 null 选项，null 值将首先出现在返回的数组中。

¥Note that first also is the default value, so if you omit the null option, null values will appear first in the returned array.

排序常见问题解答​

¥Sorting FAQs

我可以执行不区分大小写的排序吗？​

¥Can I perform case-insensitive sorting?

跟随 GitHub 上的问题#841。

¥Follow issue #841 on GitHub.