查询构建器
数据库查询构建器用于构造 SELECT、UPDATE 和 DELETE 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_address
和 country
列。
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
是使用页码应用 offset
和 limit
的便捷方法。 它总共接受两个参数。
- 第一个参数是页码 不是偏移量 。
- 第二个参数是要获取的行数,默认为 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
方法允许你在 PostgreSQL、Oracle、SQLite3 和 MSSQL 数据库中使用 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
withMaterialized
和 withNotMaterialized
方法允许你在 PostgreSQL 和 SQLite3 数据库中使用 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
方法在 PostgreSQL、Oracle、SQLite3 和 MSSQL 数据库中创建递归 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' })
返回值是受影响的行数。 但是,当使用 PostgreSQL
、Oracle
或 MSSQL
时,也可以指定返回列。
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 PostgreSQL 和 LOCK 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
方法返回一个具有 meta
和 data
属性的对象。 该方法的输出适用于 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)
)
本译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。