为继承映射编写 SELECT 语句

关于本文档

本节使用使用 ORM 继承 功能配置的 ORM 映射，该功能在 映射类继承层次结构 中描述。重点将放在 连接表继承 上，因为这是最复杂的 ORM 查询案例。

查看此页面的 ORM 设置.

从基类与特定子类中进行 SELECT

针对连接继承层次结构中的类构造的 SELECT 语句将查询该类映射到的表以及存在的任何父表，使用 JOIN 将它们链接在一起。然后，查询将返回属于请求类型的对象以及请求类型的任何子类型，使用每行中的 鉴别器 值来确定正确的类型。以下查询是针对 Manager 子类 Employee 建立的，然后返回的结果将仅包含 Manager 类型的对象

>>> from sqlalchemy import select
>>> stmt = select(Manager).order_by(Manager.id)
>>> managers = session.scalars(stmt).all()

BEGIN (implicit)
SELECT manager.id, employee.id AS id_1, employee.name, employee.type, employee.company_id, manager.manager_name
FROM employee JOIN manager ON employee.id = manager.id ORDER BY manager.id
[...] ()

>>> print(managers)
[Manager('Mr. Krabs')]

当 SELECT 语句针对层次结构中的基类时，默认行为是只有该类的表将包含在呈现的 SQL 中，并且不会使用 JOIN。与所有情况一样，鉴别器 列用于区分不同的请求子类型，这会导致返回任何可能的子类型的对象。返回的对象将具有与填充的基表对应的属性，而与子表对应的属性将以未加载状态启动，并在访问时自动加载。子属性的加载可以配置为以多种方式更“急切”，这将在本节稍后讨论。

下面的示例创建了一个针对 Employee 超类的查询。这表明任何类型的对象，包括 Manager、Engineer 和 Employee，都可能在结果集中

>>> from sqlalchemy import select
>>> stmt = select(Employee).order_by(Employee.id)
>>> objects = session.scalars(stmt).all()

BEGIN (implicit)
SELECT employee.id, employee.name, employee.type, employee.company_id
FROM employee ORDER BY employee.id
[...] ()

>>> print(objects)
[Manager('Mr. Krabs'), Engineer('SpongeBob'), Engineer('Squidward')]

上面，Manager 和 Engineer 的附加表未包含在 SELECT 中，这意味着返回的对象将不包含来自这些表的数据，在本例中为 Manager 类的 .manager_name 属性以及 Engineer 类的 .engineer_info 属性。这些属性以 过期 状态启动，并在首次使用 延迟加载 访问时自动填充自身

>>> mr_krabs = objects[0]
>>> print(mr_krabs.manager_name)

SELECT manager.manager_name AS manager_manager_name
FROM manager
WHERE ? = manager.id
[...] (1,)

Eugene H. Krabs

如果已加载大量对象，并且消耗应用程序需要访问子类特定的属性，则这种延迟加载行为是不可取的，因为这将是 N+1 问题的示例，该问题为每行发出额外的 SQL。这种额外的 SQL 可能会影响性能，并且可能与使用 asyncio 等方法不兼容。此外，在我们的 Employee 对象查询中，由于查询仅针对基表，因此我们无法在 Manager 或 Engineer 方面添加涉及子类特定属性的 SQL 条件。接下来的两节详细介绍了两种结构，它们以不同的方式为这两个问题提供了解决方案，即 selectin_polymorphic() 加载器选项和 with_polymorphic() 实体构造。

使用 selectin_polymorphic()

为了解决访问子类属性时的性能问题，可以使用 selectin_polymorphic() 加载器策略来 预加载 这些额外的属性，一次跨多个对象。此加载器选项的工作方式与 selectinload() 关系加载器策略类似，它针对层次结构中加载的对象的每个子表发出额外的 SELECT 语句，使用 IN 根据主键查询额外的行。

selectin_polymorphic() 接受作为其参数正在查询的基实体，后跟该实体的子类序列，应为传入的行加载其特定属性

>>> from sqlalchemy.orm import selectin_polymorphic
>>> loader_opt = selectin_polymorphic(Employee, [Manager, Engineer])

然后，selectin_polymorphic() 构造用作加载器选项，将其传递给 Select.options() 方法 Select。该示例说明了使用 selectin_polymorphic() 来预加载 Manager 和 Engineer 子类本地的列

>>> from sqlalchemy.orm import selectin_polymorphic
>>> loader_opt = selectin_polymorphic(Employee, [Manager, Engineer])
>>> stmt = select(Employee).order_by(Employee.id).options(loader_opt)
>>> objects = session.scalars(stmt).all()

BEGIN (implicit)
SELECT employee.id, employee.name, employee.type, employee.company_id
FROM employee ORDER BY employee.id
[...] ()
SELECT manager.id AS manager_id, employee.id AS employee_id,
employee.type AS employee_type, manager.manager_name AS manager_manager_name
FROM employee JOIN manager ON employee.id = manager.id
WHERE employee.id IN (?) ORDER BY employee.id
[...] (1,)
SELECT engineer.id AS engineer_id, employee.id AS employee_id,
employee.type AS employee_type, engineer.engineer_info AS engineer_engineer_info
FROM employee JOIN engineer ON employee.id = engineer.id
WHERE employee.id IN (?, ?) ORDER BY employee.id
[...] (2, 3)

>>> print(objects)
[Manager('Mr. Krabs'), Engineer('SpongeBob'), Engineer('Squidward')]

上面的示例说明了发出两个额外的 SELECT 语句，以便预先获取额外的属性，例如 Engineer.engineer_info 以及 Manager.manager_name。我们现在可以访问已加载对象上的这些子属性，而无需发出任何额外的 SQL 语句

>>> print(objects[0].manager_name)
Eugene H. Krabs

提示

selectin_polymorphic() 加载器选项尚未针对基本 employee 表不需要包含在第二个“预加载”查询中的事实进行优化；因此，在上面的示例中，我们看到了从 employee 到 manager 和 engineer 的 JOIN，即使来自 employee 的列已经加载。这与 selectinload() 关系策略形成对比，后者在这方面更加复杂，并且可以在不需要时分解出 JOIN。

将 selectin_polymorphic() 应用于现有的预加载

除了将 selectin_polymorphic() 指定为语句加载的顶级实体的选项之外，我们还可以指示 selectin_polymorphic() 作为现有加载的目标。由于我们的 设置 映射包括一个父 Company 实体，其中 Company.employees relationship() 引用 Employee 实体，我们可以说明针对 Company 实体的 SELECT，该实体预加载所有 Employee 对象以及其子类型上的所有属性，方法是应用 Load.selectin_polymorphic() 作为链式加载器选项；在这种形式中，第一个参数是从先前的加载器选项（在本例中为 selectinload()）隐式获得的，因此我们仅指示要加载的其他目标子类

>>> from sqlalchemy.orm import selectinload
>>> stmt = select(Company).options(
...     selectinload(Company.employees).selectin_polymorphic([Manager, Engineer])
... )
>>> for company in session.scalars(stmt):
...     print(f"company: {company.name}")
...     print(f"employees: {company.employees}")

BEGIN (implicit)
SELECT company.id, company.name
FROM company
[...] ()
SELECT employee.company_id AS employee_company_id, employee.id AS employee_id,
employee.name AS employee_name, employee.type AS employee_type
FROM employee
WHERE employee.company_id IN (?)
[...] (1,)
SELECT manager.id AS manager_id, employee.id AS employee_id,
employee.type AS employee_type,
manager.manager_name AS manager_manager_name
FROM employee JOIN manager ON employee.id = manager.id
WHERE employee.id IN (?) ORDER BY employee.id
[...] (1,)
SELECT engineer.id AS engineer_id, employee.id AS employee_id,
employee.type AS employee_type,
engineer.engineer_info AS engineer_engineer_info
FROM employee JOIN engineer ON employee.id = engineer.id
WHERE employee.id IN (?, ?) ORDER BY employee.id
[...] (2, 3)

company: Krusty Krab
employees: [Manager('Mr. Krabs'), Engineer('SpongeBob'), Engineer('Squidward')]

另请参阅

多态子类型的预加载 - 说明了使用 with_polymorphic() 的上述等效示例

将加载器选项应用于由 selectin_polymorphic 加载的子类

由 selectin_polymorphic() 发出的 SELECT 语句本身就是 ORM 语句，因此我们还可以添加其他加载器选项（例如 关系加载技术 中记录的选项），这些选项引用特定的子类。这些选项应作为 selectin_polymorphic() 选项的同级应用，也就是说，在 select.options() 中以逗号分隔。

例如，如果我们认为 Manager 映射器与名为 Paperwork 的实体具有 一对多 关系，我们可以结合使用 selectin_polymorphic() 和 selectinload() 来预加载所有 Manager 对象上的此集合，其中 Manager 对象的子属性本身也被预加载

>>> from sqlalchemy.orm import selectin_polymorphic
>>> stmt = (
...     select(Employee)
...     .order_by(Employee.id)
...     .options(
...         selectin_polymorphic(Employee, [Manager, Engineer]),
...         selectinload(Manager.paperwork),
...     )
... )
>>> objects = session.scalars(stmt).all()

SELECT employee.id, employee.name, employee.type, employee.company_id
FROM employee ORDER BY employee.id
[...] ()
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name
FROM employee JOIN manager ON employee.id = manager.id
WHERE employee.id IN (?) ORDER BY employee.id
[...] (1,)
SELECT paperwork.manager_id AS paperwork_manager_id, paperwork.id AS paperwork_id, paperwork.document_name AS paperwork_document_name
FROM paperwork
WHERE paperwork.manager_id IN (?)
[...] (1,)
SELECT engineer.id AS engineer_id, employee.id AS employee_id, employee.type AS employee_type, engineer.engineer_info AS engineer_engineer_info
FROM employee JOIN engineer ON employee.id = engineer.id
WHERE employee.id IN (?, ?) ORDER BY employee.id
[...] (2, 3)

>>> print(objects[0])
Manager('Mr. Krabs')
>>> print(objects[0].paperwork)
[Paperwork('Secret Recipes'), Paperwork('Krabby Patty Orders')]

当 selectin_polymorphic 本身是子选项时应用加载器选项

2.0.21 版本中的新增功能。

上一节说明了 selectin_polymorphic() 和 selectinload() 用作同级选项，两者都在对 select.options() 的单次调用中使用。如果目标实体是已从父关系加载的实体，如 将 selectin_polymorphic() 应用于现有的预加载 中的示例所示，我们可以使用 Load.options() 方法应用此“同级”模式，该方法将子选项应用于父级，如 使用 Load.options() 指定子选项 中所示。下面，我们结合了两个示例来加载 Company.employees，还加载了 Manager 和 Engineer 类的属性，以及预加载 `Manager.paperwork` 属性

>>> from sqlalchemy.orm import selectinload
>>> stmt = select(Company).options(
...     selectinload(Company.employees).options(
...         selectin_polymorphic(Employee, [Manager, Engineer]),
...         selectinload(Manager.paperwork),
...     )
... )
>>> for company in session.scalars(stmt):
...     print(f"company: {company.name}")
...     for employee in company.employees:
...         if isinstance(employee, Manager):
...             print(f"manager: {employee.name} paperwork: {employee.paperwork}")

BEGIN (implicit)
SELECT company.id, company.name
FROM company
[...] ()
SELECT employee.company_id AS employee_company_id, employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type
FROM employee
WHERE employee.company_id IN (?)
[...] (1,)
SELECT manager.id AS manager_id, employee.id AS employee_id, employee.type AS employee_type, manager.manager_name AS manager_manager_name
FROM employee JOIN manager ON employee.id = manager.id
WHERE employee.id IN (?) ORDER BY employee.id
[...] (1,)
SELECT paperwork.manager_id AS paperwork_manager_id, paperwork.id AS paperwork_id, paperwork.document_name AS paperwork_document_name
FROM paperwork
WHERE paperwork.manager_id IN (?)
[...] (1,)
SELECT engineer.id AS engineer_id, employee.id AS employee_id, employee.type AS employee_type, engineer.engineer_info AS engineer_engineer_info
FROM employee JOIN engineer ON employee.id = engineer.id
WHERE employee.id IN (?, ?) ORDER BY employee.id
[...] (2, 3)

company: Krusty Krab
manager: Mr. Krabs paperwork: [Paperwork('Secret Recipes'), Paperwork('Krabby Patty Orders')]

在映射器上配置 selectin_polymorphic()

selectin_polymorphic() 的行为可以在特定映射器上配置，以便默认发生，方法是使用 Mapper.polymorphic_load 参数，在每个子类的基础上使用值 "selectin"。下面的示例说明了在 Engineer 和 Manager 子类中使用此参数

class Employee(Base):
    __tablename__ = "employee"
    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(50))
    type = mapped_column(String(50))

    __mapper_args__ = {"polymorphic_identity": "employee", "polymorphic_on": type}


class Engineer(Employee):
    __tablename__ = "engineer"
    id = mapped_column(Integer, ForeignKey("employee.id"), primary_key=True)
    engineer_info = mapped_column(String(30))

    __mapper_args__ = {
        "polymorphic_load": "selectin",
        "polymorphic_identity": "engineer",
    }


class Manager(Employee):
    __tablename__ = "manager"
    id = mapped_column(Integer, ForeignKey("employee.id"), primary_key=True)
    manager_name = mapped_column(String(30))

    __mapper_args__ = {
        "polymorphic_load": "selectin",
        "polymorphic_identity": "manager",
    }

使用上述映射，针对 Employee 类的 SELECT 语句在发出语句时将自动假定使用 selectin_polymorphic(Employee, [Engineer, Manager]) 作为加载器选项。

使用 with_polymorphic()

与仅影响对象加载的 selectin_polymorphic() 相比，with_polymorphic() 构造会影响多态结构的 SQL 查询的呈现方式，最常见的是一系列到每个包含的子表的 LEFT OUTER JOIN。这种连接结构称为多态可选。通过一次提供多个子表的视图，with_polymorphic() 提供了一种跨多个继承类一次编写 SELECT 语句的方法，并能够根据各个子表添加过滤条件。

with_polymorphic() 本质上是 aliased() 构造的一种特殊形式。它接受的参数形式与 selectin_polymorphic() 类似，它是正在查询的基实体，后跟该实体的子类序列，应为传入的行加载其特定属性

>>> from sqlalchemy.orm import with_polymorphic
>>> employee_poly = with_polymorphic(Employee, [Engineer, Manager])

为了指示所有子类都应成为实体的一部分，with_polymorphic() 也将接受字符串 "*"，该字符串可以代替类序列传递，以指示所有类（请注意，selectin_polymorphic() 尚不支持此功能）

>>> employee_poly = with_polymorphic(Employee, "*")

下面的示例说明了与上一节中说明的相同的操作，即一次加载 Manager 和 Engineer 的所有列

>>> stmt = select(employee_poly).order_by(employee_poly.id)
>>> objects = session.scalars(stmt).all()

BEGIN (implicit)
SELECT employee.id, employee.name, employee.type, employee.company_id,
manager.id AS id_1, manager.manager_name, engineer.id AS id_2, engineer.engineer_info
FROM employee
LEFT OUTER JOIN manager ON employee.id = manager.id
LEFT OUTER JOIN engineer ON employee.id = engineer.id ORDER BY employee.id
[...] ()

>>> print(objects)
[Manager('Mr. Krabs'), Engineer('SpongeBob'), Engineer('Squidward')]

与 selectin_polymorphic() 的情况一样，子类上的属性已加载

>>> print(objects[0].manager_name)
Eugene H. Krabs

由于 with_polymorphic() 生成的默认可选对象使用 LEFT OUTER JOIN，因此从数据库的角度来看，查询的优化不如 selectin_polymorphic() 所采用的方法，后者使用仅基于每个表发出的 JOIN 的简单 SELECT 语句。

使用 with_polymorphic() 过滤子类属性

with_polymorphic() 构造通过包含允许引用子类的命名空间，使包含的子类映射器上的属性可用。在前一节中创建的 employee_poly 构造包括名为 .Engineer 和 .Manager 的属性，这些属性为多态 SELECT 方面提供了 Engineer 和 Manager 的命名空间。在下面的示例中，我们可以使用 or_() 构造来一次创建针对两个类的条件

>>> from sqlalchemy import or_
>>> employee_poly = with_polymorphic(Employee, [Engineer, Manager])
>>> stmt = (
...     select(employee_poly)
...     .where(
...         or_(
...             employee_poly.Manager.manager_name == "Eugene H. Krabs",
...             employee_poly.Engineer.engineer_info
...             == "Senior Customer Engagement Engineer",
...         )
...     )
...     .order_by(employee_poly.id)
... )
>>> objects = session.scalars(stmt).all()

SELECT employee.id, employee.name, employee.type, employee.company_id, manager.id AS id_1,
manager.manager_name, engineer.id AS id_2, engineer.engineer_info
FROM employee
LEFT OUTER JOIN manager ON employee.id = manager.id
LEFT OUTER JOIN engineer ON employee.id = engineer.id
WHERE manager.manager_name = ? OR engineer.engineer_info = ?
ORDER BY employee.id
[...] ('Eugene H. Krabs', 'Senior Customer Engagement Engineer')

>>> print(objects)
[Manager('Mr. Krabs'), Engineer('Squidward')]

将别名与 with_polymorphic 一起使用

with_polymorphic() 构造作为 aliased() 的特殊情况，还提供了 aliased() 的基本功能，即多态可选对象本身的“别名”。具体来说，这意味着可以在单个语句中一次使用两个或多个引用同一类层次结构的 with_polymorphic() 实体。

要将此功能与连接继承映射一起使用，我们通常需要传递两个参数，with_polymorphic.aliased 以及 with_polymorphic.flat。with_polymorphic.aliased 参数指示多态可选对象应由对该构造唯一的别名引用。with_polymorphic.flat 参数特定于默认的 LEFT OUTER JOIN 多态可选对象，并指示应在该语句中使用更优化的别名形式。

为了说明此功能，以下示例为两个单独的多态实体发出 SELECT，Employee 与 Engineer 连接，以及 Employee 与 Manager 连接。由于这两个多态实体都将在其多态可选对象中包含基本 employee 表，因此必须应用别名以区分此表在其两种不同上下文中的情况。这两个多态实体被视为两个单独的表，因此通常需要以某种方式相互连接，如下所示，其中实体在 company_id 列上连接，以及针对 Employee / Manager 实体的一些其他限制条件

>>> manager_employee = with_polymorphic(Employee, [Manager], aliased=True, flat=True)
>>> engineer_employee = with_polymorphic(Employee, [Engineer], aliased=True, flat=True)
>>> stmt = (
...     select(manager_employee, engineer_employee)
...     .join(
...         engineer_employee,
...         engineer_employee.company_id == manager_employee.company_id,
...     )
...     .where(
...         or_(
...             manager_employee.name == "Mr. Krabs",
...             manager_employee.Manager.manager_name == "Eugene H. Krabs",
...         )
...     )
...     .order_by(engineer_employee.name, manager_employee.name)
... )
>>> for manager, engineer in session.execute(stmt):
...     print(f"{manager} {engineer}")

SELECT
employee_1.id, employee_1.name, employee_1.type, employee_1.company_id,
manager_1.id AS id_1, manager_1.manager_name,
employee_2.id AS id_2, employee_2.name AS name_1, employee_2.type AS type_1,
employee_2.company_id AS company_id_1, engineer_1.id AS id_3, engineer_1.engineer_info
FROM employee AS employee_1
LEFT OUTER JOIN manager AS manager_1 ON employee_1.id = manager_1.id
JOIN
   (employee AS employee_2 LEFT OUTER JOIN engineer AS engineer_1 ON employee_2.id = engineer_1.id)
ON employee_2.company_id = employee_1.company_id
WHERE employee_1.name = ? OR manager_1.manager_name = ?
ORDER BY employee_2.name, employee_1.name
[...] ('Mr. Krabs', 'Eugene H. Krabs')

Manager('Mr. Krabs') Manager('Mr. Krabs')
Manager('Mr. Krabs') Engineer('SpongeBob')
Manager('Mr. Krabs') Engineer('Squidward')

在上面的示例中，with_polymorphic.flat 的行为是多态可选对象仍然是其各个表的 LEFT OUTER JOIN，这些表本身被赋予匿名别名。还生成了右嵌套 JOIN。

当省略 with_polymorphic.flat 参数时，通常的行为是将每个多态可选对象都包含在子查询中，从而生成更详细的形式

>>> manager_employee = with_polymorphic(Employee, [Manager], aliased=True)
>>> engineer_employee = with_polymorphic(Employee, [Engineer], aliased=True)
>>> stmt = (
...     select(manager_employee, engineer_employee)
...     .join(
...         engineer_employee,
...         engineer_employee.company_id == manager_employee.company_id,
...     )
...     .where(
...         or_(
...             manager_employee.name == "Mr. Krabs",
...             manager_employee.Manager.manager_name == "Eugene H. Krabs",
...         )
...     )
...     .order_by(engineer_employee.name, manager_employee.name)
... )
>>> print(stmt)

SELECT anon_1.employee_id, anon_1.employee_name, anon_1.employee_type,
anon_1.employee_company_id, anon_1.manager_id, anon_1.manager_manager_name, anon_2.employee_id AS employee_id_1,
anon_2.employee_name AS employee_name_1, anon_2.employee_type AS employee_type_1,
anon_2.employee_company_id AS employee_company_id_1, anon_2.engineer_id, anon_2.engineer_engineer_info
FROM
(SELECT employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type,
employee.company_id AS employee_company_id,
manager.id AS manager_id, manager.manager_name AS manager_manager_name
FROM employee LEFT OUTER JOIN manager ON employee.id = manager.id) AS anon_1
JOIN
(SELECT employee.id AS employee_id, employee.name AS employee_name, employee.type AS employee_type,
employee.company_id AS employee_company_id, engineer.id AS engineer_id, engineer.engineer_info AS engineer_engineer_info
FROM employee LEFT OUTER JOIN engineer ON employee.id = engineer.id) AS anon_2
ON anon_2.employee_company_id = anon_1.employee_company_id
WHERE anon_1.employee_name = :employee_name_2 OR anon_1.manager_manager_name = :manager_manager_name_1
ORDER BY anon_2.employee_name, anon_1.employee_name

从历史上看，上述形式对于不一定支持右嵌套 JOIN 的后端更具可移植性，并且当 with_polymorphic() 使用的“多态可选对象”不是表的简单 LEFT OUTER JOIN 时，它可能也适用，例如在使用诸如 具体表继承 映射以及通常使用替代多态可选对象时。

在映射器上配置 with_polymorphic()

与 selectin_polymorphic() 的情况一样，with_polymorphic() 构造也支持映射器配置的版本，该版本可以通过两种不同的方式配置，一种是在基类上使用 mapper.with_polymorphic 参数，另一种是以更现代的形式在每个子类的基础上使用 Mapper.polymorphic_load 参数，并传递值 "inline"。

警告

对于连接继承映射，建议在查询中显式使用 with_polymorphic()，或者对于隐式预加载子类，请使用 Mapper.polymorphic_load 和 "selectin"，而不是使用本节中描述的映射器级 mapper.with_polymorphic 参数。此参数调用复杂的启发式方法，旨在重写 SELECT 语句中的 FROM 子句，这可能会干扰更复杂语句的构造，尤其是那些引用同一映射实体的嵌套子查询的语句。

例如，我们可以使用 Mapper.polymorphic_load 作为 "inline" 来声明我们的 Employee 映射，如下所示

class Employee(Base):
    __tablename__ = "employee"
    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(50))
    type = mapped_column(String(50))

    __mapper_args__ = {"polymorphic_identity": "employee", "polymorphic_on": type}


class Engineer(Employee):
    __tablename__ = "engineer"
    id = mapped_column(Integer, ForeignKey("employee.id"), primary_key=True)
    engineer_info = mapped_column(String(30))

    __mapper_args__ = {
        "polymorphic_load": "inline",
        "polymorphic_identity": "engineer",
    }


class Manager(Employee):
    __tablename__ = "manager"
    id = mapped_column(Integer, ForeignKey("employee.id"), primary_key=True)
    manager_name = mapped_column(String(30))

    __mapper_args__ = {
        "polymorphic_load": "inline",
        "polymorphic_identity": "manager",
    }

使用上述映射，针对 Employee 类的 SELECT 语句在发出语句时将自动假定使用 with_polymorphic(Employee, [Engineer, Manager]) 作为主要实体

print(select(Employee))

SELECT employee.id, employee.name, employee.type, engineer.id AS id_1,
engineer.engineer_info, manager.id AS id_2, manager.manager_name
FROM employee
LEFT OUTER JOIN engineer ON employee.id = engineer.id
LEFT OUTER JOIN manager ON employee.id = manager.id

当使用映射器级“with polymorphic”时，查询也可以直接引用子类实体，其中它们隐式表示多态查询中的连接表。在上面，我们可以针对默认的 Employee 实体自由引用 Manager 和 Engineer

print(
    select(Employee).where(
        or_(Manager.manager_name == "x", Engineer.engineer_info == "y")
    )
)

SELECT employee.id, employee.name, employee.type, engineer.id AS id_1,
engineer.engineer_info, manager.id AS id_2, manager.manager_name
FROM employee
LEFT OUTER JOIN engineer ON employee.id = engineer.id
LEFT OUTER JOIN manager ON employee.id = manager.id
WHERE manager.manager_name = :manager_name_1
OR engineer.engineer_info = :engineer_info_1

但是，如果我们需要在单独的别名上下文中引用 Employee 实体或其子实体，我们将再次直接使用 with_polymorphic() 来定义这些别名实体，如 将别名与 with_polymorphic 一起使用 中所示。

为了更集中地控制多态可选对象，可以使用更旧式的映射器级多态控制形式，即在基类上配置的 Mapper.with_polymorphic 参数。此参数接受与 with_polymorphic() 构造相当的参数，但是，与连接继承映射的常见用法是纯星号，指示所有子表都应 LEFT OUTER JOIN，如

class Employee(Base):
    __tablename__ = "employee"
    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(50))
    type = mapped_column(String(50))

    __mapper_args__ = {
        "polymorphic_identity": "employee",
        "with_polymorphic": "*",
        "polymorphic_on": type,
    }


class Engineer(Employee):
    __tablename__ = "engineer"
    id = mapped_column(Integer, ForeignKey("employee.id"), primary_key=True)
    engineer_info = mapped_column(String(30))

    __mapper_args__ = {
        "polymorphic_identity": "engineer",
    }


class Manager(Employee):
    __tablename__ = "manager"
    id = mapped_column(Integer, ForeignKey("employee.id"), primary_key=True)
    manager_name = mapped_column(String(30))

    __mapper_args__ = {
        "polymorphic_identity": "manager",
    }

总的来说，with_polymorphic() 使用的 LEFT OUTER JOIN 格式，以及诸如 Mapper.with_polymorphic 等选项，从 SQL 和数据库优化器的角度来看可能很繁琐；对于在连接继承映射中加载子类属性的一般情况，selectin_polymorphic() 方法，或其映射器级别的等效设置 Mapper.polymorphic_load 为 "selectin" 的方式可能更优，仅在需要时才在每个查询的基础上使用 with_polymorphic()。

连接到特定子类型或 with_polymorphic() 实体

由于 with_polymorphic() 实体是 aliased() 的一个特例，为了将多态实体视为连接的目标，特别是在使用 relationship() 构造作为 ON 子句时，我们使用与常规别名相同的技术，详见 使用 Relationship 在别名目标之间进行连接，最简洁地使用 PropComparator.of_type()。在下面的示例中，我们演示了从父实体 Company 沿着一对多关系 Company.employees 进行连接，该关系在 setup 中配置为链接到 Employee 对象，使用 with_polymorphic() 实体作为目标

>>> employee_plus_engineer = with_polymorphic(Employee, [Engineer])
>>> stmt = (
...     select(Company.name, employee_plus_engineer.name)
...     .join(Company.employees.of_type(employee_plus_engineer))
...     .where(
...         or_(
...             employee_plus_engineer.name == "SpongeBob",
...             employee_plus_engineer.Engineer.engineer_info
...             == "Senior Customer Engagement Engineer",
...         )
...     )
... )
>>> for company_name, emp_name in session.execute(stmt):
...     print(f"{company_name} {emp_name}")

SELECT company.name, employee.name AS name_1
FROM company JOIN (employee LEFT OUTER JOIN engineer ON employee.id = engineer.id) ON company.id = employee.company_id
WHERE employee.name = ? OR engineer.engineer_info = ?
[...] ('SpongeBob', 'Senior Customer Engagement Engineer')

Krusty Krab SpongeBob
Krusty Krab Squidward

更直接地说，PropComparator.of_type() 也用于任何类型的继承映射，以限制沿着 relationship() 的连接到 relationship() 目标的特定子类型。上面的查询可以严格按照 Engineer 目标编写，如下所示

>>> stmt = (
...     select(Company.name, Engineer.name)
...     .join(Company.employees.of_type(Engineer))
...     .where(
...         or_(
...             Engineer.name == "SpongeBob",
...             Engineer.engineer_info == "Senior Customer Engagement Engineer",
...         )
...     )
... )
>>> for company_name, emp_name in session.execute(stmt):
...     print(f"{company_name} {emp_name}")

SELECT company.name, employee.name AS name_1
FROM company JOIN (employee JOIN engineer ON employee.id = engineer.id) ON company.id = employee.company_id
WHERE employee.name = ? OR engineer.engineer_info = ?
[...] ('SpongeBob', 'Senior Customer Engagement Engineer')

Krusty Krab SpongeBob
Krusty Krab Squidward

可以观察到，直接连接到 Engineer 目标，而不是 with_polymorphic(Employee, [Engineer]) 的“多态可选择项”，具有使用 inner JOIN 而不是 LEFT OUTER JOIN 的有用特性，从 SQL 优化器的角度来看，inner JOIN 通常性能更高。

急切加载多态子类型

在上一节中使用 Select.join() 方法说明的 PropComparator.of_type() 的使用也可以等效地应用于 关系加载器选项，例如 selectinload() 和 joinedload()。

作为一个基本示例，如果我们希望加载 Company 对象，并额外急切加载 Company.employees 的所有元素，使用针对完整层次结构的 with_polymorphic() 构造，我们可以这样写

>>> all_employees = with_polymorphic(Employee, "*")
>>> stmt = select(Company).options(selectinload(Company.employees.of_type(all_employees)))
>>> for company in session.scalars(stmt):
...     print(f"company: {company.name}")
...     print(f"employees: {company.employees}")

SELECT company.id, company.name
FROM company
[...] ()
SELECT employee.company_id AS employee_company_id, employee.id AS employee_id,
employee.name AS employee_name, employee.type AS employee_type, manager.id AS manager_id,
manager.manager_name AS manager_manager_name, engineer.id AS engineer_id,
engineer.engineer_info AS engineer_engineer_info
FROM employee
LEFT OUTER JOIN manager ON employee.id = manager.id
LEFT OUTER JOIN engineer ON employee.id = engineer.id
WHERE employee.company_id IN (?)
[...] (1,)
company: Krusty Krab
employees: [Manager('Mr. Krabs'), Engineer('SpongeBob'), Engineer('Squidward')]

上面的查询可以直接与上一节 将 selectin_polymorphic() 应用于现有的急切加载 中说明的 selectin_polymorphic() 版本进行比较。

另请参阅

将 selectin_polymorphic() 应用于现有的急切加载 - 说明了使用 selectin_polymorphic() 代替的等效示例

单表继承映射的 SELECT 语句

单表继承设置

本节讨论单表继承，详见 单表继承，它使用单个表来表示层次结构中的多个类。

查看本节的 ORM 设置.

与连接继承映射相比，单表继承映射的 SELECT 语句构造往往更简单，因为对于全单表继承层次结构，只有一个表。

无论继承层次结构是全单表继承还是混合了连接继承和单表继承，单表继承的 SELECT 语句都通过使用额外的 WHERE 条件限制 SELECT 语句来区分针对基类和子类的查询。

例如，针对 Employee 的单表继承示例映射的查询将使用表的简单 SELECT 加载 Manager、Engineer 和 Employee 类型的对象

>>> stmt = select(Employee).order_by(Employee.id)
>>> for obj in session.scalars(stmt):
...     print(f"{obj}")

BEGIN (implicit)
SELECT employee.id, employee.name, employee.type
FROM employee ORDER BY employee.id
[...] ()

Manager('Mr. Krabs')
Engineer('SpongeBob')
Engineer('Squidward')

当为特定子类发出加载时，会向 SELECT 添加额外的条件来限制行，例如下面针对 Engineer 实体执行 SELECT 的情况

>>> stmt = select(Engineer).order_by(Engineer.id)
>>> objects = session.scalars(stmt).all()

SELECT employee.id, employee.name, employee.type, employee.engineer_info
FROM employee
WHERE employee.type IN (?) ORDER BY employee.id
[...] ('engineer',)

>>> for obj in objects:
...     print(f"{obj}")
Engineer('SpongeBob')
Engineer('Squidward')

优化单表继承的属性加载

单表继承映射关于如何 SELECT 子类属性的默认行为与连接继承类似，因为子类特定的属性默认情况下仍然会发出第二个 SELECT。在下面的示例中，加载了一个 Manager 类型的 Employee，但是由于请求的类是 Employee，因此默认情况下不存在 Manager.manager_name 属性，并且在访问它时会发出额外的 SELECT

>>> mr_krabs = session.scalars(select(Employee).where(Employee.name == "Mr. Krabs")).one()

BEGIN (implicit)
SELECT employee.id, employee.name, employee.type
FROM employee
WHERE employee.name = ?
[...] ('Mr. Krabs',)

>>> mr_krabs.manager_name

SELECT employee.manager_name AS employee_manager_name
FROM employee
WHERE employee.id = ? AND employee.type IN (?)
[...] (1, 'manager')

'Eugene H. Krabs'

要更改此行为，用于急切加载这些附加属性的相同通用概念也适用于单表继承，包括使用 selectin_polymorphic() 选项以及 with_polymorphic() 选项，后者只是简单地包含附加列，并且从 SQL 的角度来看，对于单表继承映射器来说效率更高

>>> employees = with_polymorphic(Employee, "*")
>>> stmt = select(employees).order_by(employees.id)
>>> objects = session.scalars(stmt).all()

BEGIN (implicit)
SELECT employee.id, employee.name, employee.type,
employee.manager_name, employee.engineer_info
FROM employee ORDER BY employee.id
[...] ()

>>> for obj in objects:
...     print(f"{obj}")
Manager('Mr. Krabs')
Engineer('SpongeBob')
Engineer('Squidward')
>>> objects[0].manager_name
'Eugene H. Krabs'

由于加载单表继承子类映射的开销通常很小，因此建议单表继承映射包含 Mapper.polymorphic_load 参数，并将 "inline" 设置用于那些预计经常加载其特定子类属性的子类。下面是一个示例，说明了 setup，已修改为包含此选项

>>> class Base(DeclarativeBase):
...     pass
>>> class Employee(Base):
...     __tablename__ = "employee"
...     id: Mapped[int] = mapped_column(primary_key=True)
...     name: Mapped[str]
...     type: Mapped[str]
...
...     def __repr__(self):
...         return f"{self.__class__.__name__}({self.name!r})"
...
...     __mapper_args__ = {
...         "polymorphic_identity": "employee",
...         "polymorphic_on": "type",
...     }
>>> class Manager(Employee):
...     manager_name: Mapped[str] = mapped_column(nullable=True)
...     __mapper_args__ = {
...         "polymorphic_identity": "manager",
...         "polymorphic_load": "inline",
...     }
>>> class Engineer(Employee):
...     engineer_info: Mapped[str] = mapped_column(nullable=True)
...     __mapper_args__ = {
...         "polymorphic_identity": "engineer",
...         "polymorphic_load": "inline",
...     }

使用上面的映射，Manager 和 Engineer 类将在针对 Employee 实体的 SELECT 语句中自动包含它们的列

>>> print(select(Employee))

SELECT employee.id, employee.name, employee.type,
employee.manager_name, employee.engineer_info
FROM employee

继承加载 API

对象名称 描述

selectin_polymorphic(base_cls, classes)

指示应该为子类特有的所有属性执行急切加载。

with_polymorphic(base, classes[, selectable, flat, ...])

生成一个 AliasedClass 构造，该构造指定给定基类的后代映射器的列。
function sqlalchemy.orm.with_polymorphic(base: Type[_O] | Mapper[_O], classes: Literal['*'] | Iterable[Type[Any]], selectable: Literal[False, None] | FromClause = False, flat: bool = False, polymorphic_on: ColumnElement[Any] | None = None, aliased: bool = False, innerjoin: bool = False, adapt_on_names: bool = False, name: str | None = None, _use_mapper_path: bool = False) → AliasedClass[_O]

生成一个 AliasedClass 构造，该构造指定给定基类的后代映射器的列。

使用此方法将确保每个后代映射器的表都包含在 FROM 子句中，并且允许针对这些表使用 filter() 条件。生成的实例也将预先加载这些列，因此不需要对这些列进行“后获取”。

另请参阅

使用 with_polymorphic() - 关于 with_polymorphic() 的完整讨论。

参数:

  • base – 要别名的基类。

  • classes – 单个类或映射器，或继承自基类的类/映射器列表。或者，它也可以是字符串 '*'，在这种情况下，所有后代映射类都将添加到 FROM 子句中。

  • aliased – 如果为 True，则可选择项将被别名化。对于 JOIN，这意味着 JOIN 将从子查询内部 SELECT，除非 with_polymorphic.flat 标志设置为 True，对于更简单的用例，建议将其设置为 True。

  • flat – 布尔值，将传递给 FromClause.alias() 调用，以便 Join 对象的别名将别名化连接内部的各个表，而不是创建子查询。通常，所有现代数据库都支持关于右嵌套连接的这一点，并且通常会产生更高效的查询。只要生成的 SQL 功能正常，就建议设置此标志。

  • selectable –

    一个表或子查询，将用于代替生成的 FROM 子句。如果任何所需的类使用具体表继承，则此参数是必需的，因为 SQLAlchemy 目前无法自动生成表之间的 UNION。如果使用，selectable 参数必须表示每个映射类映射的完整表和列集。否则，未考虑的映射列将导致其表直接附加到 FROM 子句，这通常会导致不正确的结果。

    当保留其默认值 False 时，分配给基本映射器的多态可选择项用于选择行。但是，它也可以作为 None 传递，这将绕过配置的多态可选择项，而是为给定的目标类构造一个临时的可选择项；对于连接表继承，这将是一个包含所有目标映射器及其子类的连接。

  • polymorphic_on – 用作给定可选择项的“鉴别器”列的列。如果未给出，将使用基类的映射器的 polymorphic_on 属性（如果有）。这对于默认情况下没有多态加载行为的映射很有用。

  • innerjoin – 如果为 True，将使用 INNER JOIN。只有在仅查询一个特定子类型时才应指定此项

  • adapt_on_names –

    将 aliased.adapt_on_names 参数传递给别名对象。当给定的可选择项与现有的映射可选择项没有直接关系时，这可能很有用。

    1.4.33 版本新增。

  • name –

    赋予生成的 AliasedClass 的名称。

    2.0.31 版本新增。

function sqlalchemy.orm.selectin_polymorphic(base_cls: _EntityType[Any], classes: Iterable[Type[Any]]) → _AbstractLoad

指示应该为子类特有的所有属性执行急切加载。

这使用额外的 SELECT 和 IN 来针对所有匹配的主键值，并且是 mapper.polymorphic_load 参数上 "selectin" 设置的按查询对等物。

1.2 版本新增。

另请参阅

使用 selectin_polymorphic()