前言

查询构建器是一个用于构建查询以供稍后执行的类。例如，如果你需要多个 where 进行查询，你可以在 QueryBuilder 类中将它们链接在一起。然后修改该类，直到你要执行查询。模型在后台使用查询构建器来进行所有这些调用。许多模型方法实际上返回一个 QueryBuilder 的实例，因此你可以继续将复杂的查询链接在一起。

直接使用查询构建器类允许你在不需要使用模型的情况下进行数据库调用。

获取 QueryBuilder 类

要获取查询构建器类，你可以简单地导入查询构建器。导入后，你需要传递存储在 config.database 文件中的 connection_details 字典：

from masoniteorm.query import QueryBuilder

builder = QueryBuilder().table("users")

你还可以使用 on 方法即时切换或指定连接：

from masoniteorm.query import QueryBuilder

builder = QueryBuilder().on('staging').table("users")

from_("users") 也是 table("users") 方法的有效别名。随意使用任何你觉得更有表现力的东西。

然后，你可以开始进行任意数量的数据库调用。

Models 模型

如果你想使用模型，你应该参考 Models 文档。这是直接使用查询生成器的模型的示例。

默认情况下，查询构建器将根据结果集返回字典或列表。以下是仅使用查询生成器的结果示例：

# 不使用 models
user = QueryBuilder().table("users").first()
# == {"id": 1, "name": "Joe" ...}

# 使用 models
from masoniteorm.models import Model

class User(Model):
    pass

user = QueryBuilder(model=User).table("users").first()
# == <app.models.User>

获取记录

选择

builder.table('users').select('username').get()
# SELECT `users`.`username` FROM `users`

你还可以选择表和列：

builder.table('users').select('profiles.name').get()
# SELECT `profiles`.`name` FROM `users`

你还可以选择表格和星号 (*)。这在进行连接时很有用：

builder.table('users').select('profiles.*').get()
# SELECT `profiles`.* FROM `users`

最后，你还可以通过将 as 添加到列选择来为该列提供别名：

builder.table('users').select('profiles.username as name').get()
# SELECT `profiles`.`username` AS name FROM `users`

First

你可以轻松获得第一条记录：

builder.table('users').first()
# SELECT * from `users` LIMIT 1

All

你还可以简单地从表中获取所有记录：

builder.table('users').all()
# SELECT * from `users`

GET 方法

一旦你开始链接方法，你应该调用 get() 方法而不是 all() 方法来执行查询。

例如，这是正确的调用方式：

builder.table('users').select('username').get()

这是错误的调用方式：

builder.table('users').select('username').all()

Wheres

你还可以指定以下任何一种 where 语句：

最简单的一种是 where equals 语句。这是一个查询 username 等于 Joe AND age 等于 18 的位置：

builder.table('users').where('username', 'Joe').where('age', 18).get()

你还可以使用字典来构建 where 方法：

builder.table('users').where({"username": "Joe", "age": 18}).get()

你还可以指定不同的比较运算符：

builder.table('users').where('age', '=', 18).get()
builder.table('users').where('age', '>', 18).get()
builder.table('users').where('age', '<', 18).get()
builder.table('users').where('age', '>=', 18).get()
builder.table('users').where('age', '<=', 18).get()
builder.table('users').where('age', 'regexp', r"[0-9]").get()
builder.table('users').where('age', 'not regexp', r"[0-9]").get()

Where Null

另一个常见的 where 子句是检查值在哪里是 NULL：

builder.table('users').where_null('admin').get()

这将获取 admin 列为 NULL 的所有记录。

或者反过来：

builder.table('users').where_not_null('admin').get()

这将选择 admin 为 NOT NULL 的所有列。

Where In

为了获取某个列表中的所有记录，我们可以传入一个列表：

builder.table('users').where_in('age', [18,21,25]).get()

这将获取年龄为 18、21 或 25 的所有记录。

Where Like

你可以执行 WHERE LIKE 或 WHERE NOT LIKE 查询：

builder.table('users').where_like('name', "Jo%").get()
builder.table('users').where_not_like('name', "Jo%").get()

Where 子查询

你可以通过将可调用对象传递给 where 方法来轻松进行子查询：

builder.table("users").where(lambda q: q.where("active", 1).where_null("activated_at")).get()
# SELECT * FROM "users" WHERE ("users"."active" = '1' AND "users"."activated_at" IS NULL)

你还可以对 where_in 语句进行子查询：

builder.table("users").where_in("id", lambda q: q.select("profile_id").table("profiles")).get()
# SELECT * FROM "users" WHERE "id" IN (SELECT "profiles"."profile_id" FROM "profiles")

Select 子查询

你可以在 select 子句中进行子查询。这需要 2 个参数。第一个是子查询的别名，第二个是带有查询构建器的可调用对象。

builder.table("stores").add_select("sales", lambda query: (
    query.count("*").from_("sales").where_column("sales.store_id", "stores.id")
)).order_by("sales", "desc")

这将在查询的 select 部分添加一个子查询。然后，你可以按此别名排序或执行 wheres。

以下是查询销售额超过 1000 的所有商店的示例：

builder.table("stores").add_select("sales", lambda query: (
    query.count("*").from_("sales").where_column("sales.store_id", "stores.id")
)).where("sales", ">", "1000")

条件查询

有时你需要指定条件语句并根据条件值运行查询。

例如，你可能有如下代码：

def show(self, request: Request):
    age = request.input('age')
    article = Article.where('active', 1)
    if age >= 21:
        article.where('age_restricted', 1)

你可以使用 when 方法代替编写上面的代码。此方法接受一个条件作为第一个参数和一个可调用对象作为第二个参数。上面的代码如下所示：

def show(self, request: Request):
    age = request.input('age')
    article = Article.where('active', 1).when(age >= 21, lambda q: q.where('age_restricted', 1))

如果第一个参数中传递的条件不是真的，那么第二个参数将被忽略。

Limits 和 Offsets

同时使用 limit 和/或 offset 查询也非常简单。

下面是一个 limit 查询示例：

builder.table('users').limit(10).get()

下面是一个 offset 查询示例：

builder.table('users').offset(10).get()

这是同时使用两者的示例：

builder.table('users').limit(10).offset(10).get()

Between 语句

你可能需要获取列值介于 2 个值之间的所有记录：

builder.table('users').where_between('age', 18, 21).get()

Group By 语句

你可能希望按特定列分组：

builder.table('users').group_by('active').get()

你还可以通过以下方式指定多列分组：

builder.table('users').group_by('active, name, is_admin').get()

原生 Group By 语句

你还可以使用原生分组：

builder.table('users').group_by_raw('COUNT(*)').get()

Having 语句

Having 子句子句通常用于 group by 。例如，返回所有按 salary 分组且 salary 大于 0 的用户：

builder.table('users').sum('salary').group_by('salary').having('salary').get()

你也可以指定相同的查询，但 salary 大于 50,000

builder.table('users').sum('salary').group_by('salary').having('salary', 50000).get()

Join 语句

创建连接查询非常简单。

builder.join('other_table', 'column1', '=', 'column2')

这将在后台为你构建一个 JoinClause 。

高级 Join 语句

高级连接适用于你需要编译不仅仅是连接两个远距离列的连接子句的用例。高级连接是你需要附加 On 或 where语句 的地方。目前有两种方法可以执行高级 WHERE 子句。

第一种方法是你可以从头开始创建自己的 JoinClause，并构建自己的子句：

from masoniteorm.expressions import JoinClause

clause = (
    JoinClause('other_table as ot')
    .on('column1', '=', 'column2')
    .on('column3', '=', 'column4')
    .where('column3', '>', 4)
)

builder.join(clause)

第二种方法是直接将「 lambda 」传递给 join 方法，它将返回一个可以构建的 JoinClause 类。这种方式更简洁一点：

builder.join('other_table as ot', lambda join: (
    (
        join.on('column1', '=', 'column2')
        .on('column3', '=', 'column4')
        .where('column3', '>', 4)
    )
))

Left Join 语句

builder.table('users').left_join('table1', 'table2.id', '=', 'table1.table_id')

和右连接：

Right Join 语句

builder.table('users').right_join('table1', 'table2.id', '=', 'table1.table_id')

递增

有时你只需要增加一个列，而不需要提取任何额外的信息。很多递增逻辑被隐藏起来了：

builder.table('users').increment('status')

递减也是类似的：

递减

builder.table('users').decrement('status')

还可以提供第二个参数来指定列应该增加或减少的数量。

builder.table('users').increment('status', 10)
builder.table('users').decrement('status', 10)

分页

有时你会想要对结果集进行分页。有两种方法可以对记录进行分页。

第一个是「长度感知」分页。这意味着在分页上会有额外的结果，比如总记录。这将执行 2 个查询。获取记录的初始查询和获取总数的 COUNT 查询。对于大型或复杂的结果集，这可能不是最佳选择，因为需要进行 2 次查询。

builder.table("users").where("active", 1).paginate(number_of_results, page)

你也可以做「简单分页」。这不会返回查询总数，也不会进行第二次 COUNT 查询。

builder.table("users").where("active", 1).simple_paginate(number_of_results, page)

聚合

你可以使用多种聚合方法来聚合列：

求和

salary = builder.table('users').sum('salary').first().salary

请注意，聚合的别名是列的名称。

平均值

salary = builder.table('users').avg('salary').first().salary

请注意，聚合的别名是列的名称。

统计

salary = builder.table('users').count('salary').first().salary

你还可以统计全部：

salary = builder.table('users').count('salary').first().salary

最大值

salary = builder.table('users').max('salary').first().salary

最小值

salary = builder.table('users').min('salary').first().salary

别名

你还可以为聚合表达式指定别名。你可以通过在聚合表达式中添加 as {alias} 来做到这一点：

builder.table('users').sum('salary as payments').get()
#== SELECT SUM(`users`.`salary`) as payments FROM `users`

Order By 语句

你可以通过以下方式排序：

builder.order_by("column")

默认为升序，但你可以更改排序方向：

builder.order_by("column", "desc")

你也可以指定一个以逗号分隔的列列表，按所有3列进行排序：

builder.order_by("name, email, active")

你还可以单独指定每个列的排序方向：

builder.order_by("name, email desc, active")

这将会对 name 和 active 按升序排序，因为这是默认设置，但会对 email 按降序进行排序。

这 2 段代码是相同的：

builder.order_by("name, active").order_by("name", "desc")
builder.order_by("name, email desc, active")

原生 Order By 语句

你也可以进行原生排序. 这会将你的原生查询直接传递给查询：

builder.order_by_raw("name asc")

创建记录

你可以通过将字典传递给 create 方法来创建记录。这将执行一个 INSERT 查询：

builder.create({"name": "Joe", "active": 1})

批量创建

你还可以通过传递字典列表来批量创建记录：

builder.bulk_create([
    {"name": "Joe", "active": 1},
    {"name": "John", "active": 0},
    {"name": "Bill", "active": 1},
])

原生查询

如果一些查询更容易写成原生查询，你可以轻松地为 selects 和 wheres 执行此操作：

builder.table('users').select_raw("COUNT(`username`) as username").where_raw("`username` = 'Joe'").get()

你还可以使用 statement 方法指定完全原生的查询。这将直接执行查询并返回结果，而不是构建查询：

builder.statement("select count(*) from users where active = 1")

你还可以传递查询绑定：

builder.statement("select count(*) from users where active = '?'", [1])

你还可以使用 Raw 表达式类来指定原生表达式。这可以与更新查询一起使用：

from masoniteorm.expressions import Raw

builder.update({
    "name": Raw('"alias"')
})
# == UPDATE "users" SET "name" = "alias"

分块

如果你需要遍历大量结果，请考虑分块。块只会将指定数量的记录拉入生成器：

for users in builder.table('users').chunk(100):
    for user in users:
        user #== <User object>

获取 SQL

如果你想找出执行命令时将运行的 SQL。你可以使用 to_sql() 。此方法返回没有绑定的完整查询。发送到数据库的实际查询是「qmark 查询」（见下文）。to_sql() 方法主要用于调试目的，不应直接发送到数据库，因为结果没有查询绑定，并且会受到 SQL 注入攻击。 仅将此方法用于调试目的。

builder.table('users').count('salary').where('age', 18).to_sql()
#== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '18'

获取 Qmark

Qmark 本质上只是一个普通的 SQL 语句，只是查询被替换为带引号的问号 ('?')。本应在问号位置的值存储在一个元组中，并与 qmark 查询一起发送以帮助进行 sql 注入。 qmark 查询是使用连接类发送的实际查询。

builder.table('users').count('salary').where('age', 18).to_qmark()
#== SELECT COUNT(`users`.`salary`) AS salary FROM `users` WHERE `users`.`age` = '?'

注意：qmark 查询将重置查询构建器并从构建器类中删除 aggregates 和 wheres 等内容。正因为如此，在to_qmark 之后写 get() 会导致不正确的查询（因为最终查询中会缺少 wheres 和 aggregates 之类的内容）。如果需要调试查询，请使用 to_sql() 方法，该方法没有这种重置行为。

更新

更新记录

你可以更新许多记录。

builder.where('active', 0).update({
    'active': 1
})
# UPDATE `users` SET `users`.`active` = 1 where `users`.`active` = 0

删除

删除记录

你也可以删除许多记录。例如，删除所有 active 为 0 的记录。

builder.where('active', 0).delete()

截断

你也可以直接从查询生成器中截断：

builder.truncate('users')

你也可以暂时禁用和重新启用外键以避免外键检查。

builder.truncate('users', foreign_keys=True)

可用方法

聚合

连接

Where 子句

悲观锁

查询构建器包含几个函数，可帮助你对 SELECT 语句执行「悲观锁」。

要使用「共享锁」运行 SELECT 语句，你可以在查询中使用 shared_lock 方法：

builder.where('votes', '>', 100).shared_lock().get()

要在 SELECT 语句上使用「更新锁」，你可以在查询上使用 lock_for_update 方法：

builder.where('votes', '>', 100).lock_for_update().get()

原生查询

修饰符

DML

测试

低级方法

这些是可能有用的较低级别的方法：

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

原文地址：https://learnku.com/docs/masonite/4.0/or...

译文地址：https://learnku.com/docs/masonite/4.0/or...