查询构建器

未匹配的标注

数据库查询构建器用于构造 SELECTUPDATEDELETE SQL 查询。 要插入新行,你必须使用 插入查询构建器 并使用 [原始查询构建器](docs.adonisjs. com/reference/database/raw-query-builder) 用于运行原始 SQL 查询。

你可以使用以下方法之一获取数据库查询构建器的实例。

import Database from '@ioc:Adonis/Lucid/Database'

Database.query()

// 选择表也会返回查询构建器实例
Database.from('users')

方法/属性

以下是查询构建器实例上可用的方法/属性列表。

select

select 方法允许从数据库表中选择列。你可以传递列数组或将它们作为多个参数传递。

Database
  .from('users')
  .select('id', 'username', 'email')

列别名

你可以使用 as 表达式或传递键值对对象为列定义别名。

Database
  .from('users')
  .select('id', 'email as userEmail')
Database
  .from('users')
  .select({
    id: 'id',

    // 键是别名
    userEmail: 'email'
  })

使用子查询

此外,你可以使用子查询和原始查询在运行时生成列,例如,从 user_logins 表中选择用户的最后登录 IP 地址。

Database
  .from('users')
  .select(
    Database
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id')
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip') // 👈 这很重要
  )

使用原始查询

与子查询类似,你也可以传递原始查询的实例。

Database
  .from('users')
  .select(
    Database
      .raw(`
        (select ip_address from user_logins where users.id = user_logins.user_id limit 1) as last_login_ip
      `)
  )

from

from 方法用于为查询定义数据库表。

Database.from('users')

查询构建器还允许通过传递子查询或闭包(其作用类似于子查询)来使用派生表。

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

where

where 方法用于定义 SQL 查询中的 where 子句。 查询构建器接受广泛的参数类型,让你充分利用 SQL 的强大功能。

下面的示例接受列名作为第一个参数,将其值作为第二个参数。

Database
  .from('users')
  .where('username', 'virk')

你还可以定义 SQL 运算符,如下所示。

Database
  .from('users')
  .where('created_at', '>', '2020-09-09')
// 使用 luxon 生成日期
Database
  .from('users')
  .where('created_at', '>', DateTime.local().toSQLDate())
// 使用 like 运算符
Database
  .from('posts')
  .where('title', 'like', '%Adonis 101%')

where 组

你可以通过将回调传递给 where 方法来创建 where 组。 例如:

Database
  .from('users')
  .where((query) => {
    query
      .where('username', 'virk')
      .whereNull('deleted_at')
  })
  .orWhere((query) => {
    query
      .where('email', 'virk@adonisjs.com')
      .whereNull('deleted_at')
  })

生成的 SQL

SELECT * FROM "users"
  WHERE (
    "username" = ? AND "deleted_at" IS NULL
  )
  or (
    "email" = ? AND "deleted_at" IS NULL
  )

使用子查询

where 方法值也可以是子查询。

Database
  .from('user_groups')
  .where(
    'user_id',
    Database
      .from('users')
      .select('user_id')
      .where('users.user_id', 1)
  )

使用原始查询

同样,你也可以定义原始查询。

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

where 方法变体

以下是 where 方法变体的列表并共享相同的 API。

方法 描述
andWhere where 方法别名
orWhere 添加 or where 子句
whereNot 添加 where not 子句
orWhereNot 添加 or where not 子句
andWhereNot whereNot 别名

whereColumn

whereColumn 方法允许你将列定义为 where 子句的值。该方法通常对查询和连接很有帮助。例如:

Database
  .from('users')
  .whereColumn('updated_at', '>', 'created_at')
Database
  .from('users')
  .select(
    Database
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id') // 👈
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip')
  )

whereColumn 方法变体

以下是 whereColumn 方法变体的列表并共享相同的 API。

方法 描述
andWhereColumn whereColumn 方法别名
orWhereColumn 添加 or where 子句
whereNotColumn 添加 where not 子句
orWhereNotColumn 添加 or where not 子句
andWhereNotColumn whereNotColumn 别名

whereLike

在具有给定值的给定列上添加带有区分大小写的子字符串比较的 where 子句。

Database
  .from('posts')
  .whereLike('title', '%Adonis 101%')

whereILike

在具有给定值的给定列上添加不区分大小写的子字符串比较的 where 子句。该方法为每种方言生成略有不同的值,以实现不区分大小写的比较。

Database
  .from('posts')
  .whereILike('title', '%Adonis 101%')

whereIn

whereIn 方法用于定义 wherein SQL 子句。该方法接受列名作为第一个参数,一个值数组作为第二个参数。

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

也可以为多个列定义值。 例如:

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

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

使用子查询

你还可以使用子查询计算 whereIn 值。

Database
  .from('users')
  .whereIn(
    'id',
    Database
      .from('user_logins')
      .select('user_id')
      .where('created_at', '<', '2020-09-09')
  )

针对多列

Database
  .from('users')
  .whereIn(
    ['id', 'email'],
    Database
      .from('accounts')
      .select('user_id', 'email')
  )

whereIn 方法也接受回调作为第二个参数。回调接收一个子查询的实例,你可以使用它作为运行时计算值。

Database
  .from('users')
  .whereIn(
    'id',
    (query) => query.from('user_logins').select('user_id')
  )

whereIn 方法变体

以下是 whereIn 方法变体的列表并共享相同的 API。

方法 描述
andWhereIn whereIn 别名
orWhereIn 增加 or where in 子句
whereNotIn 增加 where not in 子句
orWhereNotIn 增加 or where not in 子句
andWhereNotIn whereNotIn 别名

whereNull

whereNull 方法将 where null 子句添加到查询中。

Database
  .from('users')
  .whereNull('deleted_at')

whereNull 方法变体

以下是 whereNull 方法变体的列表并共享相同的 API。

方法 描述
andWhereNull whereNull 别名
orWhereNull 增加 or where null 子句
whereNotNull 增加 where not null 子句
orWhereNotNull 增加 or where not null 子句
andWhereNotNull whereNotNull 别名

whereExists

whereExists 方法允许通过检查子查询是否存在结果来添加 where 约束。 例如:选择所有至少登录过一次的用户。

Database
  .from('users')
  .whereExists((query) => {
    query
      .from('user_logins')
      .whereColumn('users.id', 'user_logins.user_id')
      .limit(1)
  })

你还可以传入子查询或原始查询作为第一个参数。

Database
  .from('users')
  .whereExists(
    Database
      .from('user_logins')
      .whereColumn('users.id', 'user_logins.user_id')
      .limit(1)
  )
Database
  .from('users')
  .whereExists(
    Database.raw(
      'select * from user_logins where users.id = user_logins.user_id'
    )
  )

whereExists 方法变体

以下是 whereExists 方法变体的列表并共享相同的 API。

方法 描述
andWhereExists whereExists 方法别名
orWhereExists 增加 or where exists 子句
whereNotExists 增加 where not exists 子句
orWhereNotExists 增加 or where not exists 子句
andWhereNotExists whereNotExists 方法别名

whereBetween

whereBetween 方法添加了 where between 子句。它接受列名作为第一个参数,一个值数组作为第二个参数。

Database
  .from('users')
  .whereBetween('age', [18, 60])

使用子查询

你还可以使用子查询从不同的数据库表中派生值。

Database
  .from('users')
  .whereBetween('age', [
    Database.from('participation_rules').select('min_age'),
    Database.from('participation_rules').select('max_age'),
  ])

使用原生查询

你还可以使用原始查询从另一个数据库表中派生值。

Database
  .from('users')
  .whereBetween('age', [
    Database.raw('(select min_age from participation_rules)'),
    Database.raw('(select max_age from participation_rules)'),
  ])

whereBetween 方法变体

以下是 whereBetween 方法变体的列表并共享相同的 API。

方法 描述
andWhereBetween whereBetween 方法别名
orWhereBetween 新增 or where between 子句
whereNotBetween 新增 where not between 子句
orWhereNotBetween 新增 or where not between 子句
andWhereNotBetween whereNotBetween 方法别名

whereRaw

你可以使用 whereRaw 方法来表达现有查询构建器方法未涵盖的条件。

始终确保绑定参数并且不要直接在原始查询中对用户输入进行编码。

❌ 直接编码用户值

Database
  .from('users')
  .whereRaw(`username = ${username}`)

✅ 使用绑定参数

Database
  .from('users')
  .whereRaw('username = ?', [username])

你还可以使用 ?? 动态定义列名。

Database
  .from('users')
  .whereRaw('?? = ?', ['users.username', username])

whereRaw 方法变体

以下是 whereRaw 方法变体的列表并共享相同的 API。

方式 描述
andWhereRaw whereRaw 方法别名
orWhereRaw 新增 or where raw 子句

whereJson

添加带有对象的 where 子句以匹配数据库中 JSON 列的值。

Database
  .from('users')
  .whereJson('address', { city: 'XYZ', pincode: '110001' })

也可以使用子查询计算列值。

Database
  .from('users')
  .whereJson(
    'address',
    Database
      .select('address')
      .from('user_address')
      .where('address.user_id', 1)
  )

whereJson 方法变体

以下是 whereJson 方法变体的列表并共享相同的 API。

方法 描述
orWhereJson 新增 or where 子句以匹配 JSON 列值
andWhereJson whereJson 别名
whereNotJson 新增 where not 不是 JSON 子句
orWhereNotJson 新增 or where not 不是 JSON 列的子句
andWhereNotJson whereNotJson 别名

whereJsonSuperset

添加一个子句,其中 JSON 列的值是已定义对象的超集。 在以下示例中,用户地址存储为 JSON,我们通过用户的密码找到。

Database
  .from('users')
  .whereJsonSuperset('address', { pincode: '110001' })

whereJsonSuperset 方法变体

以下是 whereJsonSuperset 方法变体的列表并共享相同的 API。

方法 描述
orWhereJsonSuperset 添加 or where 子句匹配 JSON 列
andWhereJsonSuperset whereJsonSuperset 别名
whereNotJsonSuperset 添加 where not 子句匹配非 JSON 列
orWhereNotJsonSuperset 添加 or where not 匹配非 JSON 列
andWhereNotJsonSuperset whereNotJsonSuperset 别名

whereJsonSubset

添加一个子句,其中 JSON 列的值是已定义对象的子集。 在以下示例中,用户地址存储为 JSON,我们通过用户的密码或城市名称来查找。

Database
  .from('users')
  .whereJsonSubset('address', { pincode: '110001', city: 'XYZ' })

whereJsonSubset 方法变体

以下是 whereJsonSubset 方法变体的列表并共享相同的 API。

方法 描述
orWhereJsonSubset 添加 or where 子句匹配 JSON 列
andWhereJsonSubset whereJsonSubset 别名
whereNotJsonSubset 添加 where not 子句匹配非 JSON 列
orWhereNotJsonSubset 添加 or where not 子句匹配非 JSON 列
andWhereNotJsonSubset whereNotJsonSubset 别名

join

join 方法允许指定两个表之间的 SQL 连接。 例如:通过加入 user_logins 表来选择 ip_addresscountry 列。

Database
  .from('users')
  .join('user_logins', 'users.id', '=', 'user_logins.user_id')
  .select('users.*')
  .select('user_logins.ip_address')
  .select('user_logins.country')

你可以将回调作为第二个参数传递来定义更多连接约束。

Database
  .from('users')
  .join('user_logins', (query) => {
    query
      .on('users.id', '=', 'user_logins.user_id')
      .andOnVal('user_logins.created_at', '>', '2020-10-09')
  })
  .select('users.*')
  .select('user_logins.ip_address')
  .select('user_logins.country')

要对连接约束进行分组,你可以将回调传递给 on 方法。

Database
  .from('users')
  .join('user_logins', (query) => {
    query
      .on((subquery) => {
        subquery
          .on('users.id', '=', 'user_logins.user_id')
          .andOnVal('user_logins.created_at', '>', '2020-10-09')
      })
      .orOn((subquery) => {
        subquery
          .on('users.id', '=', 'user_logins.account_id')
          .andOnVal('user_logins.created_at', '>', '2020-10-09')
      })
  })
  .select('users.*')
  .select('user_logins.ip_address')
  .select('user_logins.country')

输出 SQL

SELECT
  "users".*,
  "user_logins"."ip_address",
  "user_logins"."country"
FROM "users"
  INNER JOIN "user_logins" ON (
    "users"."id" = "user_logins"."user_id" AND "user_logins"."created_at" > ?
  )
  or (
    "users"."id" = "user_logins"."account_id" AND "user_logins"."created_at" > ?
  )

join 方法默认使用 inner join,你可以使用以下可用方法之一使用不同的连接。

  • leftJoin

  • leftOuterJoin

  • rightJoin

  • rightOuterJoin

  • fullOuterJoin

  • crossJoin


joinRaw

你可以使用 joinRaw 方法来表达查询构建器标准 API 未涵盖的条件。


On 方法

以下是可用于 join query 的可用 on 方法列表。

Database
  .from('users')
  .joinRaw('natural full join user_logins')

onIn

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onIn('user_logins.country', ['India', 'US', 'UK'])
  })

onNotIn

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotIn('user_logins.country', ['India', 'US', 'UK'])
  })

onNull

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNull('user_logins.ip_address')
  })

onNotNull

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotNull('user_logins.ip_address')
  })

onExists

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onExists((subquery) => {
      subquery
        .select('*')
        .from('accounts')
        .whereRaw('users.account_id = accounts.id')
    })
  })

onNotExists

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotExists((subquery) => {
      subquery
        .select('*')
        .from('accounts')
        .whereRaw('users.account_id = accounts.id')
    })
  })

onBetween

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onBetween('user_logins.login_date', ['2020-10-01', '2020-12-31'])
  })

onNotBetween

Database
  .from('users')
  .join('user_logins', (query) => {
    query.onNotBetween('user_logins.login_date', ['2020-10-01', '2020-12-31'])
  })

having

have 方法添加了 have 子句。 它接受列名作为第一个参数,然后是可选的运算符和值。

Database
  .from('exams')
  .select('user_id')
  .groupBy('user_id')
  .having('score', '>', 80)

havingRaw

大多数情况下,你会发现自己会使用 haveRaw 方法,因为这可以为 having 子句定义聚合。

Database
  .from('exams')
  .select('user_id')
  .groupBy('user_id')
  .havingRaw('SUM(score) > ?', [200])

having 方法变体

以下是所有可用的 having 方法 的列表。

方法 描述
havingIn 向查询中添加 have in 子句。 它接受一个值数组。
havingNotIn 在查询中添加 have not in 子句。 它接受一个值数组。
havingNull 向查询添加有 null 子句。
havingNotNull 向查询添加一个没有为空的子句。
havingExists 向查询中添加有存在子句。
havingNotExists 向查询中添加 have not exists 子句。
havingBetween 向查询中添加 have between 子句。 它接受一个值数组。
havingNotBetween 在查询中添加 have not between 子句,它接受一组值。

distinct

distinct 方法将 distinct 子句应用于 select 语句。 你可以将一个或多个列名定义为多个参数。

Database
  .from('users')
  .distinct('country')

Database
  .from('users')
  .distinct('country', 'locale')

你可以在不带任何参数的情况下调用 distinct 方法来消除重复行。

Database.from('users').distinct()

还有另一种 PostgreSQL-only 方法,distinctOn。 这是一篇解释 SELECT DISTINCT ON 的文章。

Database
  .from('logs')
  .distinctOn('url')
  .orderBy('created_at', 'DESC')

groupBy

groupBy 方法将 group by 子句应用于查询。

Database
  .from('logs')
  .select('url')
  .groupBy('url')

groupByRaw

groupByRaw 方法允许编写 SQL 查询来定义 group by 语句。

Database
  .from('sales')
  .select('year')
  .groupByRaw('year WITH ROLLUP')

orderBy

orderBy 方法将 order by 子句应用于查询。

Database
  .from('users')
  .orderBy('created_at', 'desc')

你可以通过多次调用 orderBy 方法按多列排序。

Database
  .from('users')
  .orderBy('username', 'asc')
  .orderBy('created_at', 'desc')

或者传递一个对象数组。

Database
  .from('users')
  .orderBy([
    {
      column: 'username',
      order: 'asc',
    },
    {
      column: 'created_at',
      order: 'desc',
    }
  ])

使用子查询

你还可以将子查询实例传递给 orderBy 方法——例如,按帖子收到的评论数排序。

const commentsCountQuery = Database
  .from('comments')
  .count('*')
  .whereColumn('posts.id', '=', 'comments.post_id')

Database
  .from('posts')
  .orderBy(commentsCountQuery, 'desc')

orderByRaw

使用 orderByRaw 方法从 SQL 字符串定义排序顺序。

const commentsCountQuery = Database
  .raw(
    'select count(*) from comments where posts.id = comments.post_id'
  )
  .wrap('(', ')')

Database
  .from('posts')
  .orderBy(commentsCountQuery, 'desc')

offset

将偏移量应用于 SQL 查询

Database.from('posts').offset(11)

limit

将限制应用于 SQL 查询

Database.from('posts').limit(20)

forPage

forPage 是使用页码应用 offsetlimit 的便捷方法。 它总共接受两个参数。

  • 第一个参数是页码 不是偏移量
  • 第二个参数是要获取的行数,默认为 20
Database
  .from('posts')
  .forPage(request.input('page', 1), 20)

count

count 方法允许你在 SQL 查询中使用 count 聚合

聚合值的键是特定于方言的,因此我们建议你始终为可预测的输出定义别名。

在 PostgreSQL 中,count 方法返回一个 bigint 数据类型的字符串表示。

const users = await Database
  .from('users')
  .count('* as total')

console.log(users[0].total)

你还可以按如下方式定义聚合:

const users = await Database
  .from('users')
  .count('*', 'total')

console.log(users[0].total)

你可以按如下方式计算多列:

const users = await Database
  .from('users')
  .count({
    'active': 'is_active',
    'total': '*',
  })

console.log(users[0].total)
console.log(users[0].active)

其他聚合方法

以下所有聚合方法的 API 与 count 方法相同。

方法 描述
countDistinct 只计算不同的行
min 使用 min 函数聚合值
max 使用 max 函数聚合值
sum 使用 sum 函数聚合值
sumDistinct 使用 sum 函数聚合不同行的值
avg 使用 avg 函数聚合值
avgDistinct 使用 avg 函数聚合不同行的值

union

union 方法允许你通过使用查询构建器的多个实例来构建联合查询,例如:

Database
  .from('users')
  .whereNull('last_name')
  .union((query) => {
    query.from('users').whereNull('first_name')
  })

/**
SELECT * FROM "users" WHERE "last_name" IS NULL
UNION
SELECT * FROM "users" WHERE "first_name" IS NULL
*/

你还可以通过将布尔标志作为第二个参数传递来包装联合查询。

Database
  .from('users')
  .whereNull('last_name')
  .union((query) => {
    query.from('users').whereNull('first_name')
  }, true) // 👈

/**
SELECT * FROM "users" WHERE "last_name" IS NULL
UNION
(SELECT * FROM "users" WHERE "first_name" IS NULL)
*/

你可以传递一个回调数组来定义多个联合查询。

Database
  .from('users')
  .whereNull('last_name')
  .union([
    (query) => {
      query.from('users').whereNull('first_name')
    },
    (query) => {
      query.from('users').whereNull('email')
    },
  ], true)

/**
SELECT * FROM "users" WHERE "last_name" IS NULL
UNION
(SELECT * FROM "users" WHERE "first_name" IS NULL)
UNION
(SELECT * FROM "users" WHERE "email" IS NULL)
*/

使用子查询

你还可以通过传递查询构建器的实例来定义联合查询。

Database
  .from('users')
  .whereNull('last_name')
  .union([
    Database.from('users').whereNull('first_name'),
    Database.from('users').whereNull('email')
  ], true)

以下方法与 union 方法具有相同的 API。

  • unionAll

  • intersect


with

with 方法允许你在 PostgreSQLOracleSQLite3MSSQL 数据库中使用 CTE(通用表表达式)。

Database
  .query()
  .with('aliased_table', (query) => {
    query.from('users').select('*')
  })
  .select('*')
  .from('aliased_table')

/**
WITH "aliased_table" AS (
  SELECT * FROM "users"
)
SELECT * FROM "aliased_table"
*/

该方法还接受一个可选的第三个参数,它是一个列名数组,指定的列名数必须与 CTE 查询的结果集中的列数匹配。

Database
  .query()
  .with('aliased_table', (query) => {
    query.from('users').select('id', 'email')
  }, ['id', 'email'])
  .select('*')
  .from('aliased_table')

/**
WITH "aliased_table" (id, email) AS (
  SELECT * FROM "users"
)
SELECT * FROM "aliased_table"
*/

withMaterialized/withNotMaterialized

withMaterializedwithNotMaterialized 方法允许你在 PostgreSQLSQLite3 数据库中使用 CTE(通用表表达式)作为物化视图。

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

/**
WITH "aliased_table" AS MATERIALIZED (
  SELECT * FROM "users"
)
SELECT * FROM "aliased_table"
*/

withRecursive

withRecursive 方法在 PostgreSQLOracleSQLite3MSSQL 数据库中创建递归 CTE(公用表表达式)。

在以下示例中,我们计算父帐户的所有子帐户的总和。 此外,我们假设以下表结构。

id name parent_id amount
1 Expenses NULL NULL
2 Car Expenses 1 100
3 Food Expenses 1 40
4 Earnings NULL NULL
5 Freelance work 4 100
6 Blog post payment 4 78
7 Car service 2 60
Database
  .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')

上面的例子并不是为了简化 SQL 的复杂性。 相反,它展示了查询构建器构建此类 SQL 查询的强大功能,而无需将它们编写为 SQL 字符串。

该方法还接受一个可选的第三个参数,它是一个列名数组。 指定的列名数必须与 CTE 查询的结果集中的列数匹配。

Database
  .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')
      })
  }, ['amount', 'id'])
  .sum('amount as total')
  .from('tree')

这是一篇解释 PostgreSQL 递归查询的文章


update

update 方法允许更新一个或多个数据库行。你可以使用查询构建器在执行更新时添加额外的约束。

const affectedRows = Database
  .from('users')
  .where('id', 1)
  .update({ email: 'virk@adonisjs.com' })

返回值是受影响的行数。 但是,当使用 PostgreSQLOracleMSSQL 时,也可以指定返回列。

const rows = Database
  .from('users')
  .where('id', 1)
  .update(
    { email: 'virk@adonisjs.com' },
    ['id', 'email'] // columns to return
  )

console.log(rows[0].id)
console.log(rows[0].email)

increment

increment 方法允许增加一列或多列的值。

Database
  .from('accounts')
  .where('id', 1)
  .increment('balance', 10)

/**
UPDATE "accounts"
SET
  "balance" = "balance" + 10
WHERE
  "id" = 1
*/

你还可以通过传递一个对象来增加多列。

Database
  .from('accounts')
  .where('id', 1)
  .increment({
    balance: 10,
    credit_limit: 5
  })

/**
UPDATE "accounts"
SET
  "balance" = "balance" + 10,
  "credit_limit" = "credit_limit" + 5
WHERE
  "id" = 1
*/

decrement

decrement 方法与 increment 方法相反。 但是,API 是相同的。

Database
  .from('accounts')
  .where('id', 1)
  .decrement('balance', 10)

delete

delete 方法发出一个 delete SQL 查询。你可以使用查询构建器在执行删除时添加额外的约束。

Database
  .from('users')
  .where('id', 1)
  .delete()

delete 方法还有一个名为 del 的别名。


useTransaction

useTransaction 方法指示查询构建器将查询包装在事务中。 数据库事务 指南涵盖了在应用程序中创建和使用事务的不同方法。

const trx = await Database.transaction()

Database
  .from('users')
  .useTransaction(trx) // 👈
  .where('id', 1)
  .update({ email: 'virk@adonisjs.com' })

await trx.commit()

forUpdate

forUpdate 方法在 PostgreSQL 和 MySQL 中的选定行上获取更新锁。

在使用 forUpdate 或类似的锁之前,请确保始终使用 useTransaction 方法提供事务对象。

const user = Database
  .from('users')
  .where('id', 1)
  .useTransaction(trx)
  .forUpdate() // 👈
  .first()

forShare

forShare 在选择语句期间添加了 FOR SHARE in PostgreSQLLOCK IN SHARE MODE for MySQL

const user = Database
  .from('users')
  .where('id', 1)
  .useTransaction(trx)
  .forShare() // 👈
  .first()

skipLocked

skipLocked 方法跳过被另一个事务锁定的行。 该方法仅受 MySQL 8.0+ 和 PostgreSQL 9.5+ 支持。

Database
  .from('users')
  .where('id', 1)
  .forUpdate()
  .skipLocked() // 👈
  .first()

/**
SELECT * FROM "users"
WHERE "id" = 1
FOR UPDATE SKIP LOCKED
*/

noWait

如果任何选定的行被另一个事务锁定,则 noWait 方法将失败。 该方法仅受 MySQL 8.0+ 和 PostgreSQL 9.5+ 支持。

Database
  .from('users')
  .where('id', 1)
  .forUpdate()
  .noWait() // 👈
  .first()

/**
SELECT * FROM "users"
WHERE "id" = 1
FOR UPDATE NOWAIT
*/

clone

clone 方法返回一个新的查询构建器对象,其中包含从原始查询应用的所有约束。

const query = Database.from('users').select('id', 'email')
const clonedQuery = query.clone().clearSelect()

await query // select "id", "email" from "users"
await clonedQuery // select * from "users"

debug

debug 方法允许在单个查询级别启用或禁用调试。 这是关于调试查询的完整指南

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

timeout

为查询定义超时。超时后引发异常。

timeout 的值始终以毫秒为单位。

Database
  .from('users')
  .timeout(2000)

你还可以在 MySQL 和 PostgreSQL 使用超时时取消查询。

Database
  .from('users')
  .timeout(2000, { cancel: true })

toSQL

toSQL 方法将查询 SQL 和绑定作为对象返回。

const output = Database
  .from('users')
  .where('id', 1)
  .toSQL()

console.log(output)

toSQL 对象还具有 toNative 方法来根据使用的数据库方言格式化 SQL 查询。

const output = Database
  .from('users')
  .where('id', 1)
  .toSQL()
  .toNative()

console.log(output)

toQuery

应用绑定参数后返回 SQL 查询。

const output = Database
  .from('users')
  .where('id', 1)
  .toQuery()

console.log(output)
// select * from "users" where "id" = 1

执行查询

查询构建器扩展了原生的 Promise 类。你可以使用 await 关键字或链接 then/catch 方法来执行查询。

Database
  .from('users')
  .then((users) => {
  })
  .catch((error) => {
  })

使用 async/await

const users = await Database.from('users')

此外,你可以通过显式调用 exec 方法来执行查询。

const users = await Database.from('users').exec()

first

选择查询总是返回一个对象数组,即使查询旨在获取单行。但是,使用 first 方法会给你第一行或 null(当没有行时)。

First 并不意味着表中的第一行,只意味着结果数组中的第一行,不管从数据库中获取它的顺序是什么。

const user = await Database
  .from('users')
  .where('id', 1)
  .first()

if (user) {
  console.log(user.id)
}

firstOrFail

firstOrFail 方法类似于 first 方法,不同的是,它在没有找到行时引发异常。

const user = await Database
  .from('users')
  .where('id', 1)
  .firstOrFail()

分页

查询构建器对基于偏移的分页具有一流的支持。它还通过在后台运行单独的查询来自动计算总行数。

const page = request.input('page', 1)
const limit = 20

const results = await Database
  .from('users')
  .paginate(page, limit)

paginate 方法返回 SimplePaginator 类的实例。 该类具有以下属性和方法。

firstPage

返回第一页的编号,它始终为 1。

results.firstPage

perPage

返回传递给 paginate 方法的限制值。

results.perPage

currentPage

返回当前页面的值。

results.currentPage

lastPage

通过考虑总行数返回最后一页的值。

results.lastPage

total

保存数据库中总行数的值。

results.total

hasPages

一个布尔值,用于知道是否有分页页面。你可以依靠此值来决定何时或何时不显示分页链接。

以下是 Edge 视图的示例。

@if(results.hasPages)

  {{-- Display pagination links --}}

@endif

hasMorePages

一个布尔值,用于知道当前页面之后是否还有更多页面。

results.hasMorePages

all()

返回 SQL 查询返回的行数组。

results.all()

getUrl

返回给定页码的 URL。

result.getUrl(1) // /?page=1

getNextPageUrl

返回下一页的 URL

// 假设当前页面为 2

result.getNextPageUrl() // /?page=3

getPreviousPageUrl

返回上一页的 URL

// 假设当前页是 2

result.getPreviousPageUrl() // /?page=1

getUrlsForRange

返回给定范围的 URL,当你想要呈现给定范围的链接时很有帮助。

以下是在 Edge 模板中使用 getUrlsForRange 的示例。

@each(
  link in results.getUrlsForRange(results.firstPage, results.lastPage)
)
  <a
    href="{{ link.url }}"
    class="{{ link.isActive ? 'active' : '' }}"
  >
    {{ link.page }}
  </a>
@endeach

toJSON

toJSON 方法返回一个具有 metadata 属性的对象。 该方法的输出适用于 JSON API 响应。

results.toJSON()

/**
{
  meta: {
    total: 200,
    per_page: 20,
    current_page: 1,
    first_page: 1,
    last_page: 20,
    ...
  },
  data: [
    {
    }
  ]
}
*/

baseUrl

分页器类生成的所有 URL 都使用 /(根) URL。 但是,你可以通过定义自定义基本 URL 来更改此设置。

results.baseUrl('/posts')

results.getUrl(2) // /posts?page=2

queryString

定义要附加到分页器类生成的 URL 的查询字符串。

results.queryString({ limit: 20, sort: 'top' })

results.getUrl(2) // /?page=2&limit=20&sort=top

有用的属性和方法

以下是在查询构建器之上构建某些内容时可能偶尔需要的属性和方法列表。

client

参考底层数据库查询客户端的实例。

const query = Database.query()
console.log(query.client)

knexQuery

对底层 KnexJS 查询实例的引用。

const query = Database.query()
console.log(query.knexQuery)

hasAggregates

一个布尔值,用于了解查询是否使用任何聚合方法。

const query = Database.from('posts').count('* as total')
console.log(query.hasAggregates) // true

hasGroupBy

一个布尔值,用于了解查询是否使用 group by 子句。

const query = Database.from('posts').groupBy('tenant_id')
console.log(query.hasGroupBy) // true

hasUnion

一个布尔值,用于了解查询是否使用联合。

const query = Database
  .from('users')
  .whereNull('last_name')
  .union((query) => {
    query.from('users').whereNull('first_name')
  })

console.log(query.hasUnion) // true

clearSelect

调用此方法清除选定的列。

const query = Database.query().select('id', 'title')
query.clone().clearSelect()

clearWhere

调用此方法清除 where 子句。

const query = Database.query().where('id', 1)
query.clone().clearWhere()

clearOrder

调用此方法按顺序清除。

const query = Database.query().orderBy('id', 'desc')
query.clone().clearOrder()

clearHaving

调用此方法清除 have 子句。

const query = Database.query().having('total', '>', 100)
query.clone().clearHaving()

clearLimit

调用此方法清除应用的限制。

const query = Database.query().limit(20)
query.clone().clearLimit()

clearOffset

调用此方法清除应用的偏移量。

const query = Database.query().offset(20)
query.clone().clearOffset()

reporterData

查询构建器发出 db:query 事件并使用框架分析器报告查询的执行时间。

使用 reporterData 方法,你可以将其他详细信息传递给事件和分析器。

const query = Database.from('users')

await query
  .reporterData({ userId: auth.user.id })
  .select('*')

db:query 事件中,你可以访问 userId 的值,如下所示。

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

withSchema

指定执行查询时要使用的 PostgreSQL 模式。

Database
  .from('users')
  .withSchema('public')
  .select('*')

as

指定给定查询的别名,在将查询构建器实例作为子查询传递时通常很有帮助。例如:

Database
  .from('users')
  .select(
    Database
      .from('user_logins')
      .select('ip_address')
      .whereColumn('users.id', 'user_logins.user_id')
      .orderBy('id', 'desc')
      .limit(1)
      .as('last_login_ip') // 👈 Query alias
  )

if

if 辅助器允许你有条件地向查询构建器添加约束。例如:

Database
  .from('users')
  .if(searchQuery, (query) => {
    query.where('first_name', 'like', `%${searchQuery}%`)
    query.where('last_name', 'like', `%${searchQuery}%`)
  })

你可以通过传递另一个回调作为第二个参数来定义 else 方法。

Database
  .from('users')
  .if(
    condition,
    (query) => {}, // if condition met
    (query) => {}, // otherwise execute this
  )

unless

unless 方法与 if 助手相反。

Database
  .from('projects')
  .unless(filters.status, () => {
    /**
     * Fetch projects with "active" status when
     * not status is defined in filters
     */
    query.where('status', 'active')
  })

你可以传递另一个回调,当 unless 语句不正确时会执行该回调。

Database
  .from('users')
  .unless(
    condition,
    (query) => {}, // if condition met
    (query) => {}, // otherwise execute this
  )

match

match 辅助器允许你定义条件块数组以匹配并执行相应的回调。

在以下示例中,查询构建器将遍历所有条件块并执行第一个匹配的块并丢弃另一个。 把它想象成 JavaScript 中的 switch 语句

Database
  .query()
  .match(
    [
      // Run this is user is a super user
      auth.isSuperUser, (query) => query.whereIn('status', ['published', 'draft'])
    ],
    [
      // Run this is user is loggedin
      auth.user, (query) => query.where('user_id', auth.user.id)
    ],
    // Otherwise run this
    (query) => query.where('status', 'published').where('is_public', true)
  )

本文章首发在 LearnKu.com 网站上。

本译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。

原文地址:https://learnku.com/docs/adonisjs-ref/ch...

译文地址:https://learnku.com/docs/adonisjs-ref/ch...

上一篇 下一篇
贡献者:1
讨论数量: 0
发起讨论 只看当前版本


暂无话题~