Select 查询构建器

本指南涵盖 SELECT 查询构建器。你将学会如何：

选择列，包括别名、子查询和原始片段
使用完整的 where 子句系列和 JSON 助手过滤行
连接、分组、聚合和排序结果
组合集合操作和通用表表达式
在事务内部获取行级锁
执行、检查和调试查询
使用 if、unless、match 和感知方言的助手构建条件查询

概述

Select 查询构建器是 Lucid 用于构建 SELECT 语句的流式接口。执行时它返回普通的 JavaScript 对象。对于带类型的模型结果，请使用模型查询构建器。对于插入操作，请使用 insert 查询构建器。对于更新和删除操作，请使用更新和删除查询。

通过 db 服务获取构建器实例。

import db from '@adonisjs/lucid/services/db'

const query = db.query()              // 未选择表的构建器
const usersQuery = db.from('users')   // 快捷方式：已选择表的构建器

这两种形式都返回相同的 DatabaseQueryBuilder 实例。db.from(table) 是你最常使用的快捷方式。

选择列

默认情况下，构建器选择所有列（SELECT *）。调用 select 将结果限制为特定的列，或者使用别名、子查询和原始表达式。

select

将列名作为单独的参数或数组传入。

app/services/users_service.ts

import db from '@adonisjs/lucid/services/db'

const users = await db
  .from('users')
  .select('id', 'username', 'email')

使用 as 语法或传入带有别名键的对象来重命名结果中的列。

db.from('users').select('id', 'email as userEmail')

db.from('users').select({ id: 'id', userEmail: 'email' })

将子查询作为列传入，以便在查询时计算值。以下示例选择每个用户最近的登录 IP 地址作为派生列。

const users = await db
  .from('users')
  .select(
    db
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id')
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip')
  )

使用 db.raw 将任意 SQL 片段作为列。

db
  .from('users')
  .select(
    db.raw(`(select count(*) from posts where posts.user_id = users.id) as posts_count`)
  )

from

选择查询操作的表。

db.from('users')

from 方法也接受子查询或回调来查询派生表。

db
  .from((subquery) => {
    subquery
      .from('user_exams')
      .sum('marks as total')
      .groupBy('user_id')
      .as('totals')
  })
  .avg('totals.total')

as

在查询上设置别名，通常在将查询作为子查询传递给 select、from 或其他查询构建器方法时使用。

db
  .from('users')
  .select('id', 'username')
  .as('active_users')

Where 子句

Where 子句过滤查询返回的行。Lucid 公开了一个接受多种格式的 where 方法，加上一系列更具体的助手方法（whereLike、whereIn、whereNull、whereExists、whereBetween、whereRaw、JSON 助手）。

where

最灵活的过滤方法。它接受带有可选运算符和值的列名、相等检查对象，或构建分组条件的回调。

// 相等
db.from('users').where('email', 'virk@adonisjs.com')

// 运算符
db.from('users').where('age', '>', 18)

// 对象形式
db.from('users').where({ status: 'active', role: 'admin' })

// 分组条件
db.from('users').where((query) => {
  query.where('role', 'admin').orWhere('is_owner', true)
})

当比较无法内联表达时，传入子查询或原始表达式作为值。

db
  .from('user_groups')
  .where(
    'user_id',
    db.raw(`(select user_id from users where users.user_id = ?)`, [1])
  )

where 方法具有用于组合和反转子句的变体：

方法	描述
`andWhere`	`where` 的别名
`orWhere`	添加 `OR WHERE` 子句
`whereNot`	添加 `WHERE NOT` 子句
`orWhereNot`	添加 `OR WHERE NOT` 子句
`andWhereNot`	`whereNot` 的别名

whereColumn

直接比较两列，而不是将列与值进行比较。在子查询和连接中很有用。

db.from('users').whereColumn('updated_at', '>', 'created_at')

db
  .from('users')
  .select(
    db
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id')
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip')
  )

whereColumn 共享与 where 相同的变体集：andWhereColumn、orWhereColumn、whereNotColumn、orWhereNotColumn、andWhereNotColumn。

whereLike 和 whereILike

whereLike 运行区分大小写的 LIKE 比较。whereILike 运行不区分大小写的比较，并为每种方言使用适当的运算符（PostgreSQL 上是 ILIKE，SQL Server 上是 LIKE，其他方言上是 LOWER(...)）。

db.from('posts').whereLike('title', '%Adonis 101%')
db.from('posts').whereILike('title', '%adonis 101%')

whereIn

将列与值列表进行匹配。

db.from('users').whereIn('id', [1, 2, 3])

传入列名数组和值元组列表，以同时过滤多列。

db
  .from('users')
  .whereIn(['id', 'email'], [[1, 'virk@adonisjs.com']])

// SQL: select * from "users" where ("id", "email") in ((?, ?))

从子查询计算值，用于"此行的 id 在另一个查询的结果中"的模式。

db
  .from('users')
  .whereIn(
    'id',
    db.from('user_logins').select('user_id').where('logged_in_at', '>', '2026-01-01')
  )

变体：andWhereIn、orWhereIn、whereNotIn、orWhereNotIn、andWhereNotIn。

whereNull 和 whereNotNull

过滤列为（或不为）NULL 的行。

db.from('users').whereNull('deleted_at')
db.from('users').whereNotNull('email_verified_at')

变体：andWhereNull、orWhereNull、andWhereNotNull、orWhereNotNull。

whereBetween 和 whereNotBetween

过滤列的值落在（或不落在）包含边界范围内的行。

db.from('orders').whereBetween('total', [100, 500])
db.from('orders').whereNotBetween('created_at', ['2026-01-01', '2026-02-01'])

变体：andWhereBetween、orWhereBetween、andWhereNotBetween、orWhereNotBetween。

whereExists

过滤子查询返回至少一行的行。用于"具有关联记录的行"模式。

db
  .from('users')
  .whereExists((query) => {
    query
      .from('orders')
      .whereColumn('orders.user_id', 'users.id')
      .where('orders.status', 'paid')
  })

变体：andWhereExists、orWhereExists、whereNotExists、orWhereNotExists、andWhereNotExists。

wrapExisting

将到目前为止添加的每个 where 子句包装到它自己的组中，以便后续的 where 子句与包装后的组组合，而不是与其各个条件组合。当你构建了基础查询并希望应用不应混入现有逻辑的附加条件时很有用。

const baseQuery = db
  .from('posts')
  .where('status', 'published')
  .orWhere('status', 'scheduled')

baseQuery
  .wrapExisting()
  .where('author_id', currentUser.id)

// SQL: select * from "posts" where ("status" = ? or "status" = ?) and "author_id" = ?

如果没有 wrapExisting，新的 where('author_id', ...) 将仅与最近的 orWhere 进行 AND 运算，从而产生不同的结果。

whereRaw

在 where 子句中嵌入原始 SQL 片段。始终将用户输入作为绑定传入，切勿插值到 SQL 字符串中。

db
  .from('users')
  .whereRaw('LOWER(email) = ?', [request.input('email').toLowerCase()])

变体：andWhereRaw、orWhereRaw、whereNotRaw、orWhereNotRaw、andWhereNotRaw。

whereJsonPath

通过比较 JSON 列内 JSON 路径处的值来过滤行。第三个参数是运算符，第四个参数是比较值；传入三个参数以将运算符默认为 =。

db
  .from('users')
  .whereJsonPath('preferences', '$.theme', 'dark')

db
  .from('orders')
  .whereJsonPath('payload', '$.totals.grand', '>', 1000)

变体：andWhereJsonPath、orWhereJsonPath。

whereJson

过滤 JSON 列在结构上匹配某个值的行。传入列必须等于的对象。

db
  .from('users')
  .whereJson('address', { city: 'Bangalore', pincode: '560001' })

变体：andWhereJson、orWhereJson、whereNotJson、orWhereNotJson、andWhereNotJson。

whereJsonSuperset

过滤 JSON 列包含给定对象的所有键和值的行。该列可以具有测试对象中没有的额外字段。

db
  .from('users')
  .whereJsonSuperset('preferences', { theme: 'dark' })

变体：andWhereJsonSuperset、orWhereJsonSuperset、whereNotJsonSuperset、orWhereNotJsonSuperset、andWhereNotJsonSuperset。

whereJsonSubset

过滤 JSON 列完全包含在给定对象内的行。该列的键必须是测试对象的子集。

db
  .from('users')
  .whereJsonSubset('flags', { premium: true, beta: true, internal: false })

变体：andWhereJsonSubset、orWhereJsonSubset、whereNotJsonSubset、orWhereNotJsonSubset、andWhereNotJsonSubset。

JSON 助手在 PostgreSQL 和 MySQL 上受支持。在 SQLite 上，比较回退到文本 JSON_EXTRACT 风格的模拟，这适用于简单情况，但不应依赖它进行复杂的 JSON 查询。

连接表

连接组合来自多个表的行。查询构建器公开了标准的 SQL 连接类型，加上一个原始逃生舱。

join

join 方法默认添加 INNER JOIN。传入连接的表名，然后传入列到列的比较，或传入用于多条件连接的回调。

// 简单的 ON 子句
db
  .from('posts')
  .join('users', 'posts.user_id', 'users.id')

// 带有显式运算符
db
  .from('posts')
  .join('users', 'posts.user_id', '=', 'users.id')

// 复合条件的回调形式
db
  .from('posts')
  .join('users', (clause) => {
    clause
      .on('posts.user_id', 'users.id')
      .andOn('users.is_active', '=', db.raw('?', [true]))
  })

变体方法产生其他连接类型：

方法	描述
`innerJoin`	与 `join` 相同，但显式
`leftJoin` / `leftOuterJoin`	`LEFT JOIN` / `LEFT OUTER JOIN`
`rightJoin` / `rightOuterJoin`	`RIGHT JOIN` / `RIGHT OUTER JOIN`
`fullOuterJoin`	`FULL OUTER JOIN`
`crossJoin`	`CROSS JOIN`

joinRaw

当标准助手无法表达你的需求时，使用原始 SQL 片段作为整个连接子句。

db
  .from('posts')
  .joinRaw('NATURAL FULL JOIN posts_meta')

使用 on* 作为连接条件

在连接回调内部，on 子句支持一系列用于非相等条件的助手。这些方法镜像了 where 子句系列，但在连接子句对象上调用。

db
  .from('posts')
  .leftJoin('reactions', (clause) => {
    clause
      .on('reactions.post_id', 'posts.id')
      .onIn('reactions.kind', ['like', 'love', 'wow'])
      .onNotNull('reactions.deleted_at')
  })

db
  .from('users')
  .innerJoin('subscriptions', (clause) => {
    clause
      .on('subscriptions.user_id', 'users.id')
      .onBetween('subscriptions.created_at', ['2026-01-01', '2026-12-31'])
  })

完整集合：

方法	描述
`onIn(column, values)`	`column IN (...)`
`onNotIn(column, values)`	`column NOT IN (...)`
`onNull(column)`	`column IS NULL`
`onNotNull(column)`	`column IS NOT NULL`
`onExists(callback)`	`EXISTS (...)`
`onNotExists(callback)`	`NOT EXISTS (...)`
`onBetween(column, [min, max])`	`column BETWEEN min AND max`
`onNotBetween(column, [min, max])`	`column NOT BETWEEN min AND max`

分组和聚合

groupBy

按一列或多列对行进行分组。像 count、sum 和 avg 这样的聚合函数按组应用。

db
  .from('orders')
  .select('user_id')
  .count('* as order_count')
  .groupBy('user_id')

groupByRaw

按原始 SQL 表达式进行分组。

db
  .from('orders')
  .groupByRaw('DATE_TRUNC(\'month\', created_at)')

having

过滤分组后的结果。当过滤应用于聚合时，使用 having 代替 where。

db
  .from('orders')
  .select('user_id')
  .count('* as order_count')
  .groupBy('user_id')
  .having('order_count', '>', 5)

变体：andHaving、orHaving、havingIn、havingNotIn、havingNull、havingNotNull、havingBetween、havingNotBetween、havingExists、havingNotExists。

havingRaw

使用原始 SQL 片段作为 having 子句。

db
  .from('orders')
  .select('user_id')
  .sum('total as total_spend')
  .groupBy('user_id')
  .havingRaw('SUM(total) > ?', [10000])

聚合方法

聚合方法将结果缩减为每组单行，或者在没有设置 groupBy 时为总单行。将别名作为列字符串的一部分传入以控制输出键。

const result = await db.from('orders').count('* as total')
console.log(result[0].total)

聚合方法的完整集合：

方法	描述
`count(column)`	非空行的数量
`countDistinct(column)`	不同非空值的数量
`min(column)`	最小值
`max(column)`	最大值
`sum(column)`	值的总和
`sumDistinct(column)`	不同值的总和
`avg(column)`	值的平均值
`avgDistinct(column)`	不同值的平均值

distinct

将 DISTINCT 修饰符应用于 SELECT。

db.from('users').distinct('email')

不传入参数以将 DISTINCT 应用于所有选定的列。

distinctOn

应用 PostgreSQL 特有的 DISTINCT ON 修饰符，根据查询的 ORDER BY，保留列出列的每个不同值的第一行。仅在 PostgreSQL 上可用。

db
  .from('events')
  .distinctOn('user_id')
  .orderBy('user_id')
  .orderBy('occurred_at', 'desc')

排序和限制

orderBy

按列对结果排序。传入 'asc' 或 'desc'（默认为 'asc'）。

db.from('posts').orderBy('created_at', 'desc')

传入对象数组以按多列排序。

db
  .from('posts')
  .orderBy([
    { column: 'is_pinned', order: 'desc' },
    { column: 'created_at', order: 'desc' },
  ])

orderByRaw

按原始 SQL 表达式排序。

db
  .from('posts')
  .orderByRaw('LENGTH(title) DESC')

offset 和 limit

跳过和限制行。

db.from('posts').offset(40).limit(20)

forPage

结合了 offset 和 limit 的便捷方法，用于基于偏移的分页。forPage(page, perPage) 等同于 .offset((page - 1) * perPage).limit(perPage)。

db.from('posts').forPage(3, 20)

有关包括 paginate 方法和 SimplePaginator 参考在内的完整分页工作流，请参见分页指南。

集合操作

union

组合返回相同列的两个或多个查询的结果。

db
  .from('users')
  .select('email')
  .where('subscribed_to_newsletter', true)
  .union((query) => {
    query.from('contacts').select('email').where('opted_in', true)
  })

默认情况下 union 删除重复行。传入 true 作为第二个参数或使用 unionAll 保留重复项。

db
  .from('users')
  .select('email')
  .unionAll((query) => {
    query.from('contacts').select('email')
  })

intersect 和 except

仅返回同时出现在两个查询中的行（intersect）或出现在第一个但不出现在第二个中的行（except）。在 PostgreSQL、MSSQL 和 SQLite 上受支持。

db
  .from('active_users')
  .select('id')
  .intersect((query) => {
    query.from('paying_users').select('id')
  })

通用表表达式

CTE 允许你为子查询命名并在主查询中引用它。有助于提高可读性和递归查询。

with

定义 CTE 别名并将其用作常规表。

db
  .with('active_users', (query) => {
    query
      .from('users')
      .select('*')
      .where('is_active', true)
  })
  .from('active_users')
  .where('role', 'admin')

withMaterialized 和 withNotMaterialized

PostgreSQL 特有的提示，强制规划器物化（或拒绝物化）CTE 结果。

db
  .withMaterialized('aliased_table', (query) => {
    query.from('users').select('*')
  })
  .select('*')
  .from('aliased_table')

withRecursive

构建用于树和图遍历的递归 CTE。在 PostgreSQL、MySQL 8+、SQLite 3.8+、MSSQL 和 Oracle 上受支持。

下面的示例计算从给定根节点派生的每个账户的金额总和，沿着父/子层次结构向下遍历。

db
  .query()
  .withRecursive('tree', (query) => {
    query
      .from('accounts')
      .select('amount', 'id')
      .where('id', 1)
      .union((subquery) => {
        subquery
          .from('accounts as a')
          .select('a.amount', 'a.id')
          .innerJoin('tree', 'tree.id', 'a.parent_id')
      })
  })
  .sum('amount as total')
  .from('tree')

第三个参数限制 CTE 的列列表。

db
  .query()
  .withRecursive('tree', (query) => {
    // ...
  }, ['amount', 'id'])
  .sum('amount as total')
  .from('tree')

锁

锁在数据库级别序列化对选定行的并发访问。它们必须在事务内部使用；如果没有事务，锁会被获取并立即释放。

锁必须通过事务客户端本身（trx）发出，而不是通过 db，这样锁和事务属于同一个连接。

forUpdate

在选定的行上获取写锁。尝试读取或修改相同行的其他事务将被阻塞，直到你的事务提交或回滚。

await db.transaction(async (trx) => {
  const wallet = await trx
    .from('wallets')
    .where('user_id', userId)
    .forUpdate()
    .first()

  // 在事务内修改行是安全的
})

forShare

获取共享锁。其他事务可以读取但不能修改行。PostgreSQL 发出 FOR SHARE；MySQL 发出 LOCK IN SHARE MODE。

await db.transaction(async (trx) => {
  await trx
    .from('users')
    .where('id', 1)
    .forShare()
    .first()
})

skipLocked

跳过另一个事务已锁定的行，而不是等待。在 PostgreSQL 9.5+ 和 MySQL 8.0+ 上可用。

await db.transaction(async (trx) => {
  const job = await trx
    .from('jobs')
    .where('status', 'pending')
    .forUpdate()
    .skipLocked()
    .first()
})

noWait

当任何选定的行被锁定时立即失败，而不是阻塞。在 PostgreSQL 9.5+ 和 MySQL 8.0+ 上可用。

await db.transaction(async (trx) => {
  await trx
    .from('jobs')
    .where('id', 1)
    .forUpdate()
    .noWait()
    .first()
})

执行查询

查询构建器是一个 Promise。你可以直接 await 它，迭代结果，或使用下面专门的执行助手。

const users = await db.from('users').where('is_active', true)

first

返回第一个匹配的行，或者在没有行匹配时返回 null。

const user = await db.from('users').where('email', email).first()

firstOrFail

返回第一个匹配的行，或者在没有行匹配时抛出 RowNotFoundException。对于当记录不存在时应响应 404 的控制器很方便。

const user = await db.from('users').where('id', params.id).firstOrFail()

在事务内部

要在事务内运行查询，直接在事务客户端上构建它。trx 对象暴露与 db 服务相同的 .from、.query 和其他入口点。参见事务指南了解完整模式。

await db.transaction(async (trx) => {
  const users = await trx
    .from('users')
    .where('is_active', true)
})

条件构建器

这些方法允许你根据应用状态添加查询约束，而无需在整个链中散布 if 语句。

if

仅当条件为真值时应用回调。回调接收查询构建器。

const search = request.input('search')

const users = await db
  .from('users')
  .if(search, (query) => {
    query.whereILike('email', `%${search}%`)
  })

传入回退回调作为第三个参数用于 false 分支。

db
  .from('posts')
  .if(includeDrafts, (query) => query, (query) => query.where('status', 'published'))

unless

if 的反面：仅当条件为假值时应用回调。

db
  .from('posts')
  .unless(currentUser.isAdmin, (query) => {
    query.where('status', 'published')
  })

match

应用第一个其守卫为真值的回调，或者当没有匹配时应用可选的默认回调。当有多个互斥条件起作用时阅读起来很清晰。

db
  .from('posts')
  .match(
    [request.input('status') === 'draft', (query) => query.where('status', 'draft')],
    [request.input('status') === 'published', (query) => query.where('status', 'published')],
    (query) => query.whereNot('status', 'archived')
  )

ifDialect 和 unlessDialect

仅当连接的方言匹配（或不匹配）给定名称之一时应用回调。有助于编写方言特定的 SQL 片段而无需手动检查。

db
  .from('users')
  .ifDialect('postgres', (query) => {
    query.select(db.raw(`to_char(created_at, 'YYYY-MM-DD') as created_date`))
  })
  .unlessDialect(['sqlite3', 'better-sqlite3'], (query) => {
    query.forUpdate()
  })

接受的方言名称：postgres、mysql、mysql2、mssql、sqlite3、better-sqlite3、libsql、oracledb、redshift。

检查和调试

toSQL

返回一个 { sql, bindings } 对象，描述查询将执行的 SQL，而不运行它。有助于记录日志、调试或与 db.rawQuery 组合。

const { sql, bindings } = db.from('users').where('is_active', true).toSQL()

toQuery

将查询作为带有替换绑定的单个插值字符串返回。方便临时检查。转发到日志时优先使用 toSQL + 绑定以避免引号问题。

const sql = db.from('users').where('is_active', true).toQuery()

debug

仅为该查询启用调试输出。当至少附加了一个监听器时，查询会在 db:query 事件上发出。

db.from('users').debug(true)

有关完整的调试工作流，包括美化打印和 asyncStackTraces，请参见调试指南。

timeout

如果查询运行时间超过给定的毫秒数则中止。在 PostgreSQL 和 MySQL 上传入 { cancel: true } 以取消底层查询。

db.from('users').timeout(5000)
db.from('users').timeout(5000, { cancel: true })

clone

返回构建器的副本，可以对其进行修改而不会影响原始构建器。当一个基础查询为多个变体提供数据时很有用。

const baseQuery = db.from('posts').where('is_published', true)

const recent = await baseQuery.clone().orderBy('created_at', 'desc').limit(10)
const popular = await baseQuery.clone().orderBy('views', 'desc').limit(10)

reporterData

将任意元数据附加到 db:query 事件载荷，监听器可以访问这些数据。用于使用请求 ID、用户 ID 或功能标志标记查询以便可观测。

const posts = await db
  .from('posts')
  .where('is_published', true)
  .reporterData({ userId: auth.user.id, source: 'feed' })

start/events.ts

import emitter from '@adonisjs/core/services/emitter'

emitter.on('db:query', (query) => {
  console.log(query.userId, query.source)
})

有关完整的 db:query 事件载荷，请参见调试指南。

Schema 和逃生舱

withSchema

为查询设置非默认 schema（PostgreSQL、MSSQL）。

db.from('users').withSchema('analytics')

knexQuery

返回底层的 Knex 查询构建器。当你需要 Lucid 未暴露的 Knex 方法（例如 rowNumber 或其他分析助手）时使用此方法。

const rankings = await db
  .knexQuery()
  .from('scores')
  .select('user_id', 'points')
  .rowNumber('rank', { column: 'points', order: 'desc' })

结果由 Knex 塑造而非 Lucid，因此你将失去 Lucid 的带类型结果。有关更广泛的讨论，请参见数据库服务指南中的降级到 Knex。