ORM 查询指南

本页是 ORM 查询指南 的一部分。

上一页: ORM 查询指南 | 下一页: 为继承映射编写 SELECT 语句

为 ORM 映射类编写 SELECT 语句

关于此文档

本节使用在 SQLAlchemy 统一教程 中首次展示的 ORM 映射，在 声明映射类 部分中展示。

查看本页的 ORM 设置.

SELECT 语句由 select() 函数生成，该函数返回一个 Select 对象。要返回的实体和/或 SQL 表达式（即“列”子句）按位置传递给函数。然后，使用其他方法来生成完整的语句，例如下面说明的 Select.where() 方法

>>> from sqlalchemy import select
>>> stmt = select(User).where(User.name == "spongebob")

给定一个已完成的 Select 对象，为了在 ORM 中执行它以获取返回的行，将该对象传递给 Session.execute()，然后返回一个 Result 对象

>>> result = session.execute(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.name = ?
[...] ('spongebob',)
>>> for user_obj in result.scalars():
...     print(f"{user_obj.name} {user_obj.fullname}")
spongebob Spongebob Squarepants

选择 ORM 实体和属性

select() 结构接受 ORM 实体，包括映射类以及表示映射列的类级属性，它们在构造时被转换为 ORM 注解 的 FromClause 和 ColumnElement 元素。

包含 ORM 注解实体的 Select 对象通常使用 Session 对象执行，而不是 Connection 对象，这样 ORM 相关功能才能生效，包括返回 ORM 映射对象的实例。当直接使用 Connection 时，结果行只包含列级数据。

选择 ORM 实体

下面我们从 User 实体中选择，生成一个 Select，它从映射的 Table 中选择，User 映射到该表

>>> result = session.execute(select(User).order_by(User.id))
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()

从 ORM 实体中选择时，实体本身作为具有单个元素的行返回，而不是一系列单个列；例如上面的示例，Result 返回 Row 对象，每行只有一个元素，该元素包含一个 User 对象

>>> result.all()
[(User(id=1, name='spongebob', fullname='Spongebob Squarepants'),),
 (User(id=2, name='sandy', fullname='Sandy Cheeks'),),
 (User(id=3, name='patrick', fullname='Patrick Star'),),
 (User(id=4, name='squidward', fullname='Squidward Tentacles'),),
 (User(id=5, name='ehkrabs', fullname='Eugene H. Krabs'),)]

当选择包含 ORM 实体的单个元素行的列表时，通常会跳过生成 Row 对象，而是直接接收 ORM 实体。最简单的方法是使用 Session.scalars() 方法执行，而不是 Session.execute() 方法，这样就会返回一个 ScalarResult 对象，该对象生成单个元素而不是行

>>> session.scalars(select(User).order_by(User.id)).all()
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account ORDER BY user_account.id
[...] ()
[User(id=1, name='spongebob', fullname='Spongebob Squarepants'),
 User(id=2, name='sandy', fullname='Sandy Cheeks'),
 User(id=3, name='patrick', fullname='Patrick Star'),
 User(id=4, name='squidward', fullname='Squidward Tentacles'),
 User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')]

调用 Session.scalars() 方法等同于调用 Session.execute() 以接收 Result 对象，然后调用 Result.scalars() 以接收 ScalarResult 对象。

同时选择多个 ORM 实体

select() 函数一次接受任意数量的 ORM 类和/或列表达式，包括可以请求多个 ORM 类。从多个 ORM 类中选择时，它们在每个结果行中根据其类名命名。在下面的示例中，针对 User 和 Address 的 SELECT 的结果行将以 User 和 Address 的名称引用它们

>>> stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
>>> for row in session.execute(stmt):
...     print(f"{row.User.name} {row.Address.email_address}")
SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] ()
spongebob [email protected]
sandy [email protected]
sandy [email protected]
patrick [email protected]
squidward [email protected]

如果我们想在行中为这些实体分配不同的名称，我们将使用 aliased() 结构，使用 aliased.name 参数为它们指定明确的名称

>>> from sqlalchemy.orm import aliased
>>> user_cls = aliased(User, name="user_cls")
>>> email_cls = aliased(Address, name="email")
>>> stmt = (
...     select(user_cls, email_cls)
...     .join(user_cls.addresses.of_type(email_cls))
...     .order_by(user_cls.id, email_cls.id)
... )
>>> row = session.execute(stmt).first()
SELECT user_cls.id, user_cls.name, user_cls.fullname,
email.id AS id_1, email.user_id, email.email_address
FROM user_account AS user_cls JOIN address AS email
ON user_cls.id = email.user_id ORDER BY user_cls.id, email.id
[...] ()
>>> print(f"{row.user_cls.name} {row.email.email_address}")
spongebob [email protected]

上面提到的别名形式将在 使用关系在别名目标之间进行连接 中进一步讨论。

现有的 Select 结构也可以使用 Select.add_columns() 方法将 ORM 类和/或列表达式添加到其列子句中。我们可以使用此形式生成与上面相同的语句

>>> stmt = (
...     select(User).join(User.addresses).add_columns(Address).order_by(User.id, Address.id)
... )
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname,
address.id AS id_1, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id

选择单个属性

映射类上的属性，例如 User.name 和 Address.email_address，可以像传递给 Column 或其他 SQL 表达式对象一样使用 select()。创建一个针对特定列的 select() 将返回 Row 对象，而不是像 User 或 Address 对象这样的实体。每个 Row 将有每个列单独表示

>>> result = session.execute(
...     select(User.name, Address.email_address)
...     .join(User.addresses)
...     .order_by(User.id, Address.id)
... )
SELECT user_account.name, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
ORDER BY user_account.id, address.id
[...] ()

上面的语句返回 Row 对象，其中包含 name 和 email_address 列，如下面的运行时演示所示

>>> for row in result:
...     print(f"{row.name}  {row.email_address}")
spongebob  [email protected]
sandy  [email protected]
sandy  [email protected]
patrick  [email protected]
squidward  [email protected]

使用捆绑包对选定属性进行分组

Bundle 结构是一个可扩展的 ORM 专用结构，它允许将一组列表达式分组到结果行中

>>> from sqlalchemy.orm import Bundle
>>> stmt = select(
...     Bundle("user", User.name, User.fullname),
...     Bundle("email", Address.email_address),
... ).join_from(User, Address)
>>> for row in session.execute(stmt):
...     print(f"{row.user.name} {row.user.fullname} {row.email.email_address}")
SELECT user_account.name, user_account.fullname, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
[...] ()
spongebob Spongebob Squarepants [email protected]
sandy Sandy Cheeks [email protected]
sandy Sandy Cheeks [email protected]
patrick Patrick Star [email protected]
squidward Squidward Tentacles [email protected]

Bundle 可能对创建轻量级视图和自定义列分组很有用。还可以对 Bundle 进行子类化以返回其他数据结构；有关示例，请参阅 Bundle.create_row_processor()。

另请参阅

捆绑包

Bundle.create_row_processor()

选择 ORM 别名

正如在教程 使用别名 中所讨论的，使用 aliased() 结构针对映射类创建 ORM 实体的 SQL 别名

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User)
>>> print(select(u1).order_by(u1.id))
SELECT user_account_1.id, user_account_1.name, user_account_1.fullname
FROM user_account AS user_account_1 ORDER BY user_account_1.id

与使用 Table.alias() 一样，SQL 别名是匿名命名的。对于从具有显式名称的行中选择实体的情况，可以传递 aliased.name 参数

>>> from sqlalchemy.orm import aliased
>>> u1 = aliased(User, name="u1")
>>> stmt = select(u1).order_by(u1.id)
>>> row = session.execute(stmt).first()
SELECT u1.id, u1.name, u1.fullname
FROM user_account AS u1 ORDER BY u1.id
[...] ()
>>> print(f"{row.u1.name}")
spongebob

另请参阅

aliased 结构是几个用例的核心，包括

• 使用 ORM 进行子查询；部分 从子查询中选择实体 和 连接到子查询 进一步讨论了这一点。

• 控制结果集中实体的名称；有关示例，请参阅 同时选择多个 ORM 实体。

• 多次连接到同一个 ORM 实体；有关示例，请参阅 使用关系在别名目标之间进行连接。

从文本语句获取 ORM 结果

ORM 支持从其他来源的 SELECT 语句加载实体。典型的用例是文本 SELECT 语句，它在 SQLAlchemy 中使用 text() 结构表示。可以将 text() 结构与关于语句将加载的 ORM 映射列的信息进行增强；然后可以将其与 ORM 实体本身关联起来，以便可以基于此语句加载 ORM 对象。

给定我们要从其中加载的文本 SQL 语句

>>> from sqlalchemy import text
>>> textual_sql = text("SELECT id, name, fullname FROM user_account ORDER BY id")

我们可以使用 TextClause.columns() 方法将列信息添加到语句中；当调用此方法时，TextClause 对象将转换为 TextualSelect 对象，该对象扮演的角色与 Select 结构相当。TextClause.columns() 方法通常传递 Column 对象或等效对象，在本例中，我们可以直接使用 User 类上的 ORM 映射属性

>>> textual_sql = textual_sql.columns(User.id, User.name, User.fullname)

我们现在有一个 ORM 配置的 SQL 结构，它可以分别加载“id”、“name”和“fullname”列。要使用此 SELECT 语句作为完整 User 实体的来源，我们可以使用 Select.from_statement() 方法将这些列链接到常规的 ORM 启用的 Select 结构

>>> orm_sql = select(User).from_statement(textual_sql)
>>> for user_obj in session.execute(orm_sql).scalars():
...     print(user_obj)
SELECT id, name, fullname FROM user_account ORDER BY id
[...] ()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

同一个 TextualSelect 对象也可以使用 TextualSelect.subquery() 方法转换为子查询，并使用 aliased() 结构将其链接到 User 实体，方法类似于下面 从子查询中选择实体 中所讨论的

>>> orm_subquery = aliased(User, textual_sql.subquery())
>>> stmt = select(orm_subquery)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT id, name, fullname FROM user_account ORDER BY id) AS anon_1
[...] ()
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

直接使用 TextualSelect 以及 Select.from_statement() 与使用 aliased() 之间的区别在于，在前一种情况下，在生成的 SQL 中不会生成任何子查询。在某些情况下，这从性能或复杂性的角度来看可能是有利的。

从子查询中选择实体

前面部分讨论的 aliased() 结构可以与任何来自 Subquery 结构的方法（例如 Select.subquery()）一起使用，将 ORM 实体链接到该子查询返回的列；子查询提供的列与实体映射到的列之间必须存在**列对应关系**，这意味着子查询需要最终从这些实体派生，如下面的示例所示

>>> inner_stmt = select(User).where(User.id < 7).order_by(User.id)
>>> subq = inner_stmt.subquery()
>>> aliased_user = aliased(User, subq)
>>> stmt = select(aliased_user)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
 SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? ORDER BY user_account.id) AS anon_1
[generated in ...] (7,)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=2, name='sandy', fullname='Sandy Cheeks')
User(id=3, name='patrick', fullname='Patrick Star')
User(id=4, name='squidward', fullname='Squidward Tentacles')
User(id=5, name='ehkrabs', fullname='Eugene H. Krabs')

另请参阅

ORM 实体子查询/CTE - 在 SQLAlchemy 统一教程 中。

联接到子查询

从 UNION 和其他集合操作中选择实体

union() 和 union_all() 函数是最常见的集合操作，它们和其他集合操作（如 except_()、intersect() 等）一起，会生成一个称为 CompoundSelect 的对象，它由多个 Select 结构组成，并通过集合运算关键字连接在一起。ORM 实体可以使用 Select.from_statement() 方法从简单的组合选择中选择，该方法在之前的 从文本语句中获取 ORM 结果 中有说明。在这个方法中，UNION 语句是将要渲染的完整语句，在使用 Select.from_statement() 后无法添加其他条件。

>>> from sqlalchemy import union_all
>>> u = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).order_by(User.id)
>>> stmt = select(User).from_statement(u)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
WHERE user_account.id = ? ORDER BY id
[generated in ...] (2, 3)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

CompoundSelect 结构可以在查询中更灵活地使用，通过将其组织成子查询并使用 aliased() 与 ORM 实体关联，可以在查询中进一步修改，如之前 从子查询中选择实体 中所示。在下面的示例中，我们首先使用 CompoundSelect.subquery() 来创建 UNION ALL 语句的子查询，然后将其打包到 aliased() 结构中，该结构可以在 select() 结构中像任何其他映射实体一样使用，包括我们可以根据其导出的列添加过滤和排序条件。

>>> subq = union_all(
...     select(User).where(User.id < 2), select(User).where(User.id == 3)
... ).subquery()
>>> user_alias = aliased(User, subq)
>>> stmt = select(user_alias).order_by(user_alias.id)
>>> for user_obj in session.execute(stmt).scalars():
...     print(user_obj)
SELECT anon_1.id, anon_1.name, anon_1.fullname
FROM (SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id < ? UNION ALL SELECT user_account.id AS id, user_account.name AS name, user_account.fullname AS fullname
FROM user_account
WHERE user_account.id = ?) AS anon_1 ORDER BY anon_1.id
[generated in ...] (2, 3)
User(id=1, name='spongebob', fullname='Spongebob Squarepants')
User(id=3, name='patrick', fullname='Patrick Star')

另请参阅

从 Unions 中选择 ORM 实体 - 在 SQLAlchemy 统一教程 中。

联接

Select.join() 和 Select.join_from() 方法用于对 SELECT 语句构建 SQL 联接。

本节将详细说明这些方法的 ORM 用例。有关从 Core 角度进行使用的概览，请参阅 显式 FROM 子句和 JOIN，位于 SQLAlchemy 统一教程 中。

Select.join() 在 ORM 上下文中的 2.0 风格 查询中的使用方式基本等同，除了遗留用例外，与 1.x 风格 查询中的 Query.join() 方法的使用方式相同。

简单关系联接

考虑两个类 User 和 Address 之间的映射，关系 User.addresses 代表与每个 User 关联的 Address 对象的集合。 Select.join() 最常见的用法是沿此关系创建联接，使用 User.addresses 属性作为指示器，以指示应如何进行联接。

>>> stmt = select(User).join(User.addresses)

在上面的代码中，对 User.addresses 进行的 Select.join() 调用会导致 SQL 约等效于

>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

在上面的示例中，我们称传递给 Select.join() 的 User.addresses 为“on 子句”，也就是说，它指示应如何构造 JOIN 的“ON”部分。

提示

请注意，使用 Select.join() 从一个实体联接到另一个实体会影响 SELECT 语句的 FROM 子句，但不会影响列子句；本示例中的 SELECT 语句将继续仅返回来自 User 实体的行。要同时从 User 和 Address 中选择列/实体，Address 实体也必须在 select() 函数中命名，或使用 Select.add_columns() 方法添加到 Select 结构中。有关这两种形式的示例，请参阅 同时选择多个 ORM 实体 部分。

链接多个联接

要构建联接链，可以使用多个 Select.join() 调用。与关系绑定的属性同时暗示联接的左侧和右侧。考虑其他实体 Order 和 Item，其中 User.orders 关系引用 Order 实体，而 Order.items 关系通过关联表 order_items 引用 Item 实体。两个 Select.join() 调用将导致首先从 User 联接到 Order，然后从 Order 联接到 Item。但是，由于 Order.items 是 多对多 关系，它会导致两个独立的 JOIN 元素，在最终的 SQL 中总共产生三个 JOIN 元素。

>>> stmt = select(User).join(User.orders).join(Order.items)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id

每个 Select.join() 方法调用顺序的意义仅在于，我们需要联接到的“左侧”必须在 FROM 列表中出现，然后才能指定新的目标。例如，Select.join() 不会知道如何联接，如果我们指定 select(User).join(Order.items).join(User.orders)，并且会引发错误。在正确的实践中，Select.join() 方法的调用方式与我们希望 SQL 中的 JOIN 子句的渲染方式一致，每个调用都应代表从其前身到目标的清晰链接。

我们作为 FROM 子句中目标的所有元素都将继续可用作为联接 FROM 的潜在点。例如，我们可以继续向上面的 User 实体添加其他元素，从其进行联接，例如，将 User.addresses 关系添加到我们的联接链中。

>>> stmt = select(User).join(User.orders).join(Order.items).join(User.addresses)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN user_order ON user_account.id = user_order.user_id
JOIN order_items AS order_items_1 ON user_order.id = order_items_1.order_id
JOIN item ON item.id = order_items_1.item_id
JOIN address ON user_account.id = address.user_id

联接到目标实体

Select.join() 的第二种形式允许使用任何映射实体或核心可选择结构作为目标。在这种用法中，Select.join() 将尝试使用两个实体之间自然的外键关系来**推断** JOIN 的 ON 子句。

>>> stmt = select(User).join(Address)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

在上面的调用形式中，Select.join() 被调用以自动推断“on 子句”。如果两个映射的Table 构造之间没有设置ForeignKeyConstraint，或者它们之间存在多个ForeignKeyConstraint 链接，使得要使用的适当约束不明确，则此调用形式最终将引发错误。

注意

当使用Select.join() 或Select.join_from() 但不指定 ON 子句时，ORM 配置的relationship() 构造不会被考虑。只有在尝试推断 JOIN 的 ON 子句时，才会咨询映射的Table 对象级别上的实体之间的已配置ForeignKeyConstraint 关系。

使用 ON 子句连接到目标

第三种调用形式允许显式传递目标实体和 ON 子句。包含 SQL 表达式作为 ON 子句的示例如下

>>> stmt = select(User).join(Address, User.id == Address.user_id)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

基于表达式的 ON 子句也可能是一个relationship() 绑定的属性，与在简单关系连接 中使用的方式相同。

>>> stmt = select(User).join(Address, User.addresses)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account JOIN address ON user_account.id = address.user_id

上面的示例似乎是多余的，因为它以两种不同的方式指示了Address 的目标；但是，当连接到别名实体时，这种形式的效用就变得显而易见了；有关示例，请参见使用关系在别名目标之间连接 部分。

将关系与自定义 ON 条件结合使用

由relationship() 构造生成的 ON 子句可以添加额外的条件。这对于快速限制关系路径上特定连接的范围以及配置加载器策略（如joinedload() 和selectinload()）等情况很有用。PropComparator.and_() 方法位置接受一系列 SQL 表达式，这些表达式将通过 AND 连接到 JOIN 的 ON 子句。例如，如果我们想从User 连接到Address，但还要将 ON 条件限制在某些电子邮件地址上

>>> stmt = select(User.fullname).join(
...     User.addresses.and_(Address.email_address == "[email protected]")
... )
>>> session.execute(stmt).all()
SELECT user_account.fullname
FROM user_account
JOIN address ON user_account.id = address.user_id AND address.email_address = ?
[...] ('[email protected]',)
[('Sandy Cheeks',)]

另请参阅

The PropComparator.and_() 方法也适用于加载器策略，例如joinedload() 和selectinload()。请参见向加载器选项添加条件 部分。

使用关系在别名目标之间连接

当使用relationship() 绑定的属性来指示 ON 子句构建连接时，在使用 ON 子句连接到目标 中说明的双参数语法可以扩展到与aliased() 构造一起使用，以指示 SQL 别名为连接的目标，同时仍然使用relationship() 绑定的属性来指示 ON 子句，如以下示例所示，其中User 实体两次连接到针对Address 实体的两个不同的aliased() 构造

>>> address_alias_1 = aliased(Address)
>>> address_alias_2 = aliased(Address)
>>> stmt = (
...     select(User)
...     .join(address_alias_1, User.addresses)
...     .where(address_alias_1.email_address == "[email protected]")
...     .join(address_alias_2, User.addresses)
...     .where(address_alias_2.email_address == "[email protected]")
... )
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

可以使用修饰符PropComparator.of_type() 更简洁地表达相同的模式，该修饰符可以应用于relationship() 绑定的属性，并将目标实体传递到该属性以在一步骤中指示目标。以下示例使用PropComparator.of_type() 生成与刚刚说明的语句相同的 SQL 语句。

>>> print(
...     select(User)
...     .join(User.addresses.of_type(address_alias_1))
...     .where(address_alias_1.email_address == "[email protected]")
...     .join(User.addresses.of_type(address_alias_2))
...     .where(address_alias_2.email_address == "[email protected]")
... )
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN address AS address_1 ON user_account.id = address_1.user_id
JOIN address AS address_2 ON user_account.id = address_2.user_id
WHERE address_1.email_address = :email_address_1
AND address_2.email_address = :email_address_2

要使用relationship() 从别名实体构建连接，该属性可从aliased() 构造中直接获取。

>>> user_alias_1 = aliased(User)
>>> print(select(user_alias_1.name).join(user_alias_1.addresses))
SELECT user_account_1.name
FROM user_account AS user_account_1
JOIN address ON user_account_1.id = address.user_id

连接到子查询

连接的目标可以是任何“可选择”实体，包括子查询。当使用 ORM 时，这些目标通常以aliased() 构造来表示，但这并不是严格要求的，特别是如果连接的实体没有在结果中返回。例如，要从User 实体连接到Address 实体，其中Address 实体表示为一个受限制的行子查询，我们首先使用Subquery 对象构建一个Select.subquery() 对象，该对象随后可以用作Select.join() 方法的目标。

>>> subq = select(Address).where(Address.email_address == "[email protected]").subquery()
>>> stmt = select(User).join(subq, User.id == subq.c.user_id)
>>> print(stmt)
SELECT user_account.id, user_account.name, user_account.fullname
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = :email_address_1) AS anon_1
ON user_account.id = anon_1.user_id

上面通过Session.execute() 调用的 SELECT 语句将返回包含User 实体的行，但不包含Address 实体。为了将Address 实体包含在将返回结果集中的实体集中，我们针对Address 实体和Subquery 对象构建一个aliased() 对象。我们可能还想对aliased() 构造应用一个名称，例如下面使用的"address"，以便我们可以通过名称在结果行中引用它。

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(address_subq)
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('[email protected]',)
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='[email protected]')

沿着关系路径连接到子查询

上一节中说明的子查询形式可以使用relationship() 绑定的属性以在使用关系在别名目标之间连接 中指示的形式之一更具体地表达。例如，要创建相同的连接，同时确保连接沿着特定relationship() 进行，我们可以使用PropComparator.of_type() 方法，将包含Subquery 对象的aliased() 构造传递到该方法，该对象是连接的目标。

>>> address_subq = aliased(Address, subq, name="address")
>>> stmt = select(User, address_subq).join(User.addresses.of_type(address_subq))
>>> for row in session.execute(stmt):
...     print(f"{row.User} {row.address}")
SELECT user_account.id, user_account.name, user_account.fullname,
anon_1.id AS id_1, anon_1.user_id, anon_1.email_address
FROM user_account
JOIN (SELECT address.id AS id,
address.user_id AS user_id, address.email_address AS email_address
FROM address
WHERE address.email_address = ?) AS anon_1 ON user_account.id = anon_1.user_id
[...] ('[email protected]',)
User(id=3, name='patrick', fullname='Patrick Star') Address(id=4, email_address='[email protected]')

引用多个实体的子查询

包含跨越多个 ORM 实体的列的子查询可以应用于多个 aliased() 构造，并在同一 Select 构造中针对每个实体单独使用。生成的 SQL 将继续将所有这样的 aliased() 构造视为同一个子查询，但是从 ORM/Python 的角度来看，不同的返回值和对象属性可以通过使用适当的 aliased() 构造来引用。

例如，给定一个同时引用 User 和 Address 的子查询。

>>> user_address_subq = (
...     select(User.id, User.name, User.fullname, Address.id, Address.email_address)
...     .join_from(User, Address)
...     .where(Address.email_address.in_(["[email protected]", "[email protected]"]))
...     .subquery()
... )

我们可以针对 User 和 Address 创建 aliased() 构造，它们都引用同一个对象。

>>> user_alias = aliased(User, user_address_subq, name="user")
>>> address_alias = aliased(Address, user_address_subq, name="address")

一个从两个实体中选择的 Select 构造将只渲染子查询一次，但在结果行上下文中可以同时返回 User 和 Address 类的对象。

>>> stmt = select(user_alias, address_alias).where(user_alias.name == "sandy")
>>> for row in session.execute(stmt):
...     print(f"{row.user} {row.address}")
SELECT anon_1.id, anon_1.name, anon_1.fullname, anon_1.id_1, anon_1.email_address
FROM (SELECT user_account.id AS id, user_account.name AS name,
user_account.fullname AS fullname, address.id AS id_1,
address.email_address AS email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE address.email_address IN (?, ?)) AS anon_1
WHERE anon_1.name = ?
[...] ('[email protected]', '[email protected]', 'sandy')
User(id=2, name='sandy', fullname='Sandy Cheeks') Address(id=3, email_address='[email protected]')

设置连接中最左边的 FROM 子句

在当前 Select 状态的左侧与我们想要连接的内容不一致的情况下，可以使用 Select.join_from() 方法。

>>> stmt = select(Address).join_from(User, User.addresses).where(User.name == "sandy")
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

Select.join_from() 方法接受两个或三个参数，形式为 (<join from>, <onclause>) 或 (<join from>, <join to>, [<onclause>])。

>>> stmt = select(Address).join_from(User, Address).where(User.name == "sandy")
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

为了为 SELECT 设置初始 FROM 子句，以便可以随后使用 Select.join()，也可以使用 Select.select_from() 方法。

>>> stmt = select(Address).select_from(User).join(Address).where(User.name == "sandy")
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM user_account JOIN address ON user_account.id = address.user_id
WHERE user_account.name = :name_1

提示

Select.select_from() 方法实际上并不对 FROM 子句中表的顺序有最终决定权。如果语句还引用了一个 Join 构造，该构造以不同的顺序引用现有表，则 Join 构造优先。当我们使用 Select.join() 和 Select.join_from() 这样的方法时，这些方法最终会创建一个 Join 对象。因此，我们可以看到在这种情况下 Select.select_from() 的内容被覆盖。

>>> stmt = select(Address).select_from(User).join(Address.user).where(User.name == "sandy")
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

在上面，我们看到 FROM 子句是 address JOIN user_account，即使我们首先声明了 select_from(User)。由于 .join(Address.user) 方法调用，该语句最终等效于以下内容。

>>> from sqlalchemy.sql import join
>>>
>>> user_table = User.__table__
>>> address_table = Address.__table__
>>>
>>> j = address_table.join(user_table, user_table.c.id == address_table.c.user_id)
>>> stmt = (
...     select(address_table)
...     .select_from(user_table)
...     .select_from(j)
...     .where(user_table.c.name == "sandy")
... )
>>> print(stmt)
SELECT address.id, address.user_id, address.email_address
FROM address JOIN user_account ON user_account.id = address.user_id
WHERE user_account.name = :name_1

上面的 Join 构造作为 Select.select_from() 列表中的另一个条目添加，它取代了之前的条目。

关系 WHERE 操作符

除了在 Select.join() 和 Select.join_from() 方法中使用 relationship() 构造之外，relationship() 还通过使用 Select.where() 方法，在帮助构建通常用于 WHERE 子句的 SQL 表达式方面发挥作用。

EXISTS 形式：has() / any()

Exists 构造首先在 SQLAlchemy 统一教程 中的 EXISTS 子查询 部分介绍。此对象用于在与标量子查询结合使用时渲染 SQL EXISTS 关键字。relationship() 构造提供了一些辅助方法，可以用于根据关系生成一些常见的 EXISTS 样式的查询。

对于像 User.addresses 这样的 一对多 关系，可以使用 PropComparator.any() 生成一个针对 address 表的 EXISTS，该 EXISTS 与 user_account 表相关联。此方法接受可选的 WHERE 条件，以限制子查询匹配的行。

>>> stmt = select(User.fullname).where(
...     User.addresses.any(Address.email_address == "[email protected]")
... )
>>> session.execute(stmt).all()
SELECT user_account.fullname
FROM user_account
WHERE EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id AND address.email_address = ?)
[...] ('[email protected]',)
[('Sandy Cheeks',)]

由于 EXISTS 对于负面查找往往更有效，因此一个常见的查询是查找没有相关实体的实体。使用类似于 ~User.addresses.any() 的短语可以简洁地做到这一点，以选择没有相关 Address 行的 User 实体。

>>> stmt = select(User.fullname).where(~User.addresses.any())
>>> session.execute(stmt).all()
SELECT user_account.fullname
FROM user_account
WHERE NOT (EXISTS (SELECT 1
FROM address
WHERE user_account.id = address.user_id))
[...] ()
[('Eugene H. Krabs',)]

PropComparator.has() 方法的工作方式与 PropComparator.any() 大致相同，只是它用于 多对一 关系，例如，如果我们想要找到所有属于“sandy”的 Address 对象。

>>> stmt = select(Address.email_address).where(Address.user.has(User.name == "sandy"))
>>> session.execute(stmt).all()
SELECT address.email_address
FROM address
WHERE EXISTS (SELECT 1
FROM user_account
WHERE user_account.id = address.user_id AND user_account.name = ?)
[...] ('sandy',)
[('[email protected]',), ('[email protected]',)]

关系实例比较运算符

绑定到 relationship() 的属性还提供了一些 SQL 构造实现，这些实现旨在根据相关对象的特定实例过滤绑定到 relationship() 的属性，它可以从给定的 持久（或不太常见的 分离）对象实例中解包适当的属性值，并根据目标 relationship() 构造 WHERE 条件。

• **多对一 等于比较** - 可以将特定对象实例与多对一 关系进行比较，以选择目标实体的外键与给定对象的 主键 值匹配的行。

  >>> user_obj = session.get(User, 1)
  SELECT ...
  >>> print(select(Address).where(Address.user == user_obj))
  SELECT address.id, address.user_id, address.email_address
  FROM address
  WHERE :param_1 = address.user_id
  

• **多对一 不等于 比较** - 也可以使用 不等于 运算符。

  >>> print(select(Address).where(Address.user != user_obj))
  SELECT address.id, address.user_id, address.email_address
  FROM address
  WHERE address.user_id != :user_id_1 OR address.user_id IS NULL
  

• **对象包含在一个一对多 集合中** - 这本质上是“等于”比较的一对多版本，选择 主键 等于相关对象中外键值的 行。

  >>> address_obj = session.get(Address, 1)
  SELECT ...
  >>> print(select(User).where(User.addresses.contains(address_obj)))
  SELECT user_account.id, user_account.name, user_account.fullname
  FROM user_account
  WHERE user_account.id = :param_1
  

• **对象从一对多的角度具有特定父级** - with_parent() 函数生成一个比较，该比较返回被给定父级引用的行，这与对多对一 侧使用 == 运算符本质上相同。

  >>> from sqlalchemy.orm import with_parent
  >>> print(select(Address).where(with_parent(user_obj, User.addresses)))
  SELECT address.id, address.user_id, address.email_address
  FROM address
  WHERE :param_1 = address.user_id
  

ORM 查询指南

下一个查询指南部分：为继承映射编写 SELECT 语句