与多态性一起使用¶

对于以下部分，假设 Employee / Engineer / Manager 示例介绍于 映射类继承层次结构 .

通常，当 Query 指定继承层次结构的基类，仅查询该基类的本地列：：

session.query(Employee).all()

上面，对于单表继承和联接表继承，只有本地列 Employee 将出现在选择中。我们可以取回 Engineer 或 Manager 但是，在我们第一次访问它们之前，它们不会加载其他属性，此时将发出一个延迟加载。

同样，如果我们想引用映射到 Engineer 或 Manager 在我们的质询中 Employee ，这些列在单表继承或联接表继承情况下都不直接可用，因为 Employee 实体不引用这些列（请注意，对于单表继承，如果使用声明性的，这是很常见的，但对于经典映射则不常见）。

为了解决这两个问题， with_polymorphic() 函数提供一个特殊的 AliasedClass 它表示跨子类的一系列列。此对象可用于 Query 像其他别名一样。查询时，它表示给定类中存在的所有列：

from sqlalchemy.orm import with_polymorphic

eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])

query = session.query(eng_plus_manager)

如果上面的映射使用联接表继承，上面的select语句将是：

query.all()
SELECT
    employee.id AS employee_id,
    engineer.id AS engineer_id,
    manager.id AS manager_id,
    employee.name AS employee_name,
    employee.type AS employee_type,
    engineer.engineer_info AS engineer_engineer_info,
    manager.manager_data AS manager_manager_data
FROM
    employee
    LEFT OUTER JOIN engineer ON employee.id = engineer.id
    LEFT OUTER JOIN manager ON employee.id = manager.id
[]

在上面的位置，包括“工程师”和“经理”的附加表/列。在单表继承的情况下也会发生类似的行为。

with_polymorphic() 接受单个类或映射器、类/映射器列表或字符串 '*' 要指示所有子类：

# include columns for Engineer
entity = with_polymorphic(Employee, Engineer)

# include columns for Engineer, Manager
entity = with_polymorphic(Employee, [Engineer, Manager])

# include columns for all mapped subclasses
entity = with_polymorphic(Employee, '*')

engineer_employee = with_polymorphic(
    Employee, [Engineer], aliased=True)
manager_employee = with_polymorphic(
    Employee, [Manager], aliased=True)

q = s.query(engineer_employee, manager_employee).\
    join(
        manager_employee,
        and_(
            engineer_employee.id > manager_employee.id,
            engineer_employee.name == manager_employee.name
        )
)
q.all()
SELECT
    anon_1.employee_id AS anon_1_employee_id,
    anon_1.employee_name AS anon_1_employee_name,
    anon_1.employee_type AS anon_1_employee_type,
    anon_1.engineer_id AS anon_1_engineer_id,
    anon_1.engineer_engineer_name AS anon_1_engineer_engineer_name,
    anon_2.employee_id AS anon_2_employee_id,
    anon_2.employee_name AS anon_2_employee_name,
    anon_2.employee_type AS anon_2_employee_type,
    anon_2.manager_id AS anon_2_manager_id,
    anon_2.manager_manager_name AS anon_2_manager_manager_name
FROM (
    SELECT
        employee.id AS employee_id,
        employee.name AS employee_name,
        employee.type AS employee_type,
        engineer.id AS engineer_id,
        engineer.engineer_name AS engineer_engineer_name
    FROM employee
    LEFT OUTER JOIN engineer ON employee.id = engineer.id
) AS anon_1
JOIN (
    SELECT
        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
    FROM employee
    LEFT OUTER JOIN manager ON employee.id = manager.id
) AS anon_2
ON anon_1.employee_id > anon_2.employee_id
AND anon_1.employee_name = anon_2.employee_name

engineer_employee = with_polymorphic(
    Employee, [Engineer], flat=True)
manager_employee = with_polymorphic(
    Employee, [Manager], flat=True)

q = s.query(engineer_employee, manager_employee).\
    join(
        manager_employee,
        and_(
            engineer_employee.id > manager_employee.id,
            engineer_employee.name == manager_employee.name
        )
)
q.all()
SELECT
    employee_1.id AS employee_1_id,
    employee_1.name AS employee_1_name,
    employee_1.type AS employee_1_type,
    engineer_1.id AS engineer_1_id,
    engineer_1.engineer_name AS engineer_1_engineer_name,
    employee_2.id AS employee_2_id,
    employee_2.name AS employee_2_name,
    employee_2.type AS employee_2_type,
    manager_1.id AS manager_1_id,
    manager_1.manager_name AS manager_1_manager_name
FROM employee AS employee_1
LEFT OUTER JOIN engineer AS engineer_1
ON employee_1.id = engineer_1.id
JOIN (
    employee AS employee_2
    LEFT OUTER JOIN manager AS manager_1
    ON employee_2.id = manager_1.id
)
ON employee_1.id > employee_2.id
AND employee_1.name = employee_2.name

eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
query = session.query(eng_plus_manager).filter(
                or_(
                    eng_plus_manager.Engineer.engineer_info=='x',
                    eng_plus_manager.Manager.manager_data=='y'
                )
            )

query.all()
SELECT
    employee.id AS employee_id,
    engineer.id AS engineer_id,
    manager.id AS manager_id,
    employee.name AS employee_name,
    employee.type AS employee_type,
    engineer.engineer_info AS engineer_engineer_info,
    manager.manager_data AS manager_manager_data
FROM
    employee
    LEFT OUTER JOIN engineer ON employee.id = engineer.id
    LEFT OUTER JOIN manager ON employee.id = manager.id
WHERE
  engineer.engineer_info=? OR
  manager.manager_data=?
['x', 'y']

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

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

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_info = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity':'engineer',
        'polymorphic_load': 'inline'
    }

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_data = Column(String(50))
    __mapper_args__ = {
        'polymorphic_identity':'manager',
        'polymorphic_load': 'inline'
    }

session.query(Employee).\
    with_polymorphic([Engineer, Manager]).\
    filter(
        or_(
            Engineer.engineer_info=='w',
            Manager.manager_data=='q'
        )
    )

多态性选择素加载¶

替代使用 with_polymorphic() 在继承映射上“急切地”加载附加子类的函数家族，主要是在使用联合表继承时，将使用多态的“selectin”加载。这是一个紧急加载功能，其工作原理与 加载时选择 关系加载的功能。对于我们的示例映射，我们可以指示 Employee 通过使用 selectin_polymorphic() 加载器选项：

from sqlalchemy.orm import selectin_polymorphic

query = session.query(Employee).options(
    selectin_polymorphic(Employee, [Manager, Engineer])
)

当运行上述查询时，将发出两条附加的select语句：

query.all()
SELECT
    employee.id AS employee_id,
    employee.name AS employee_name,
    employee.type AS employee_type
FROM employee
()

SELECT
    engineer.id AS engineer_id,
    employee.id AS employee_id,
    employee.type AS employee_type,
    engineer.engineer_name AS engineer_engineer_name
FROM employee JOIN engineer ON employee.id = engineer.id
WHERE employee.id IN (?, ?) ORDER BY employee.id
(1, 2)

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
(3,)

我们也可以通过指定 mapper.polymorphic_load 参数，使用值 "selectin" 根据子类：

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

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

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_name = Column(String(30))

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

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

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

与使用时不同 with_polymorphic() ，当使用 selectin_polymorphic() 装载方式，我们有 not 有能力参考 Engineer 或 Manager 主查询中的实体作为筛选、排序依据或其他条件，因为这些实体在用于定位结果的初始查询中不存在。但是，我们可以应用应用于 Engineer 或 Manager ，在发出次选择时生效。下面我们假设 Manager 有额外的关系 Manager.paperwork ，我们也很乐意装载。我们可以使用任何类型的预加载，例如通过 joinedload() 功能：

from sqlalchemy.orm import joinedload
from sqlalchemy.orm import selectin_polymorphic

query = session.query(Employee).options(
    selectin_polymorphic(Employee, [Manager, Engineer]),
    joinedload(Manager.paperwork)
)

使用上面的查询，我们得到三个发出的select语句，但是其中一个针对 Manager 将：

SELECT
    manager.id AS manager_id,
    employee.id AS employee_id,
    employee.type AS employee_type,
    manager.manager_name AS manager_manager_name,
    paperwork_1.id AS paperwork_1_id,
    paperwork_1.manager_id AS paperwork_1_manager_id,
    paperwork_1.data AS paperwork_1_data
FROM employee JOIN manager ON employee.id = manager.id
LEFT OUTER JOIN paperwork AS paperwork_1
ON manager.id = paperwork_1.manager_id
WHERE employee.id IN (?) ORDER BY employee.id
(3,)

注意，selectin多态加载与selectin关系加载有类似的注意事项；对于使用复合主键的实体，使用中的数据库必须支持tuples with“in”，目前已知该tuples可用于mysql和postgresql。

# use "Employee" example from the enclosing section

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

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

class VicePresident(Manager):
    vp_info = Column(String(30))

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

SELECT employee.id AS employee_id, employee.name AS employee_name,
       employee.type AS employee_type
FROM employee
)

SELECT manager.id AS manager_id, employee.id AS employee_id,
       employee.type AS employee_type,
       manager.manager_name AS manager_manager_name,
       manager.vp_info AS manager_vp_info
FROM employee JOIN manager ON employee.id = manager.id
WHERE employee.id IN (?) ORDER BY employee.id
(1,)

from sqlalchemy.orm import with_polymorphic, selectin_polymorphic

manager_poly = with_polymorphic(Manager, [VicePresident])

s.query(Employee).options(
    selectin_polymorphic(Employee, [manager_poly])).all()

在关系中引用特定的子类型¶

对应于 relationship() 用于查询以引用两个映射之间的链接。常用的用法是指 relationship() 在里面 Query.join() 以及加载程序选项，如 joinedload() . 使用时 relationship() 如果目标类是继承层次结构，则API允许联接、预加载或其他链接以特定的子类、别名或 with_polymorphic() 该类层次结构的别名，而不是 relationship() .

这个 of_type() 方法允许沿着 relationship() 将条件缩小到特定派生别名或子类时的路径。假设 employees 表表示与 Company 对象。我们将添加一个 company_id 列到 employees 桌子和新桌子 companies ：

class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    employees = relationship("Employee",
                    backref='company')

class Employee(Base):
    __tablename__ = 'employee'
    id = Column(Integer, primary_key=True)
    type = Column(String(20))
    company_id = Column(Integer, ForeignKey('company.id'))
    __mapper_args__ = {
        'polymorphic_on':type,
        'polymorphic_identity':'employee',
    }

class Engineer(Employee):
    __tablename__ = 'engineer'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    engineer_info = Column(String(50))
    __mapper_args__ = {'polymorphic_identity':'engineer'}

class Manager(Employee):
    __tablename__ = 'manager'
    id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
    manager_data = Column(String(50))
    __mapper_args__ = {'polymorphic_identity':'manager'}

从查询时 Company 上 Employee 关系 Query.join() 方法和运算符 PropComparator.any() 和 PropComparator.has() 将从创建联接 company 到 employee ，不包括 engineer 或 manager 在混合中。如果我们希望有一个特别反对 Engineer 类，我们可以告诉这些方法使用 PropComparator.of_type() 操作员：

session.query(Company).\
    join(Company.employees.of_type(Engineer)).\
    filter(Engineer.engineer_info=='someinfo')

同样，从 Company 到包含两个元素的多态实体 Engineer 和 Manager 柱：：

manager_and_engineer = with_polymorphic(
                            Employee, [Manager, Engineer])

session.query(Company).\
    join(Company.employees.of_type(manager_and_engineer)).\
    filter(
        or_(
            manager_and_engineer.Engineer.engineer_info == 'someinfo',
            manager_and_engineer.Manager.manager_data == 'somedata'
        )
    )

这个 PropComparator.any() 和 PropComparator.has() 操作员也可以与 of_type() 例如，当嵌入的条件是子类时：

session.query(Company).\
        filter(
            Company.employees.of_type(Engineer).
                any(Engineer.engineer_info=='someinfo')
            ).all()

session.query(Company).\
    options(
        subqueryload(Company.employees.of_type(Engineer)).
        subqueryload(Engineer.machines)
        )
    )

manager_and_engineer = with_polymorphic(
                            Employee, [Manager, Engineer],
                            flat=True)

session.query(Company).\
    options(
        joinedload(
            Company.employees.of_type(manager_and_engineer)
        )
    )

session.query(Company).\
    options(
        joinedload(Company.employees.of_type(manager_and_engineer)).\
        subqueryload(manager_and_engineer.Engineer.computers)
        )
    )

正在加载具有联接表继承的对象¶

当使用联接表继承时，如果我们查询表示两个表（如 Engineer 继承部分的示例中，发出的SQL是一个联接：：

session.query(Engineer).all()

上面的查询将发出类似SQL的信息：

SELECT employee.id AS employee_id,
    employee.name AS employee_name, employee.type AS employee_type,
    engineer.name AS engineer_name
FROM employee JOIN engineer
ON employee.id = engineer.id

然后我们会收集 Engineer 对象返回，其中将包含来自 employee 和 engineer 加载。

但是，当发出 Query 对于基类，行为仅从基表加载：：

session.query(Employee).all()

在上面，默认行为将是仅从 employee 表而不是任何“子”表 (engineer 和 manager ，在前面的示例中）：

SELECT employee.id AS employee_id,
    employee.name AS employee_name, employee.type AS employee_type
FROM employee
[]

在收集 Employee 对象已从查询中返回，因为属性是从这些查询中请求的 Employee 对象，在 engineer 或 manager 子表，如果尚未加载数据，则为该相关行中的列发出第二次加载。因此，在访问对象之后，您将看到沿着以下行发布的更多SQL：

SELECT manager.id AS manager_id,
    manager.manager_data AS manager_manager_data
FROM manager
WHERE ? = manager.id
[5]
SELECT engineer.id AS engineer_id,
    engineer.engineer_info AS engineer_engineer_info
FROM engineer
WHERE ? = engineer.id
[2]

这个 with_polymorphic() 函数和相关的配置选项允许我们发出一个预先连接，它将根据 employee ， engineer 或 manager ，非常类似于joined-eager-loading为关系工作，消除了每个实体加载一秒钟的必要性：

from sqlalchemy.orm import with_polymorphic

eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])

query = session.query(eng_plus_manager)

上面生成的查询将 employee 两人的桌子 engineer 和 manager 如下表格：

query.all()
SELECT employee.id AS employee_id,
    engineer.id AS engineer_id,
    manager.id AS manager_id,
    employee.name AS employee_name,
    employee.type AS employee_type,
    engineer.engineer_info AS engineer_engineer_info,
    manager.manager_data AS manager_manager_data
FROM employee
    LEFT OUTER JOIN engineer
    ON employee.id = engineer.id
    LEFT OUTER JOIN manager
    ON employee.id = manager.id
[]

断面 与多态性一起使用 讨论 with_polymorphic() 函数及其构型变体。

加载具有单表继承的对象¶

在现代的声明式中，单继承映射产生 Column 对象只映射到一个子类，而不从超类中可用，即使它们存在于同一个表中。在我们的示例中， 单表继承 , the Manager 例如，映射具有 Column 明确规定：：

class Manager(Employee):
    manager_data = Column(String(50))

    __mapper_args__ = {
        'polymorphic_identity':'manager'
    }

上面没有 Employee.manager_data 属性，即使 employee 表有 manager_data 列。对…的质疑 Manager 将在查询中包含此列，以及只将行限制为的In子句 Manager 物体：

session.query(Manager).all()
SELECT
    employee.id AS employee_id,
    employee.name AS employee_name,
    employee.type AS employee_type,
    employee.manager_data AS employee_manager_data
FROM employee
WHERE employee.type IN (?)

('manager',)

但是，以与联接表继承类似的方式，查询 Employee 将只查询映射到的列 Employee ：

session.query(Employee).all()
SELECT employee.id AS employee_id,
    employee.name AS employee_name,
    employee.type AS employee_type
FROM employee

如果我们得到一个 Manager 根据我们的结果，访问附加的列只映射到 Manager 以类似于联接继承的方式为这些列发出延迟加载：

SELECT employee.manager_data AS employee_manager_data
FROM employee
WHERE employee.id = ? AND employee.type IN (?)

这个 with_polymorphic() 在单继承的情况下，函数的作用类似于联合继承；它既允许预先加载子类属性，也允许在查询中指定子类，而不需要使用外部联接的开销：

employee_poly = with_polymorphic(Employee, '*')

q = session.query(employee_poly).filter(
    or_(
        employee_poly.name == 'a',
        employee_poly.Manager.manager_data == 'b'
    )
)

上面，我们的查询仍然针对单个表，但是我们可以引用 Manager 或 Engineer 使用“多态”名称空间。因为我们指定了 "*" 对于实体，两者都是 Engineer 和 Manager 将立即加载。发出的SQL将是：

q.all()
SELECT
    employee.id AS employee_id, employee.name AS employee_name,
    employee.type AS employee_type,
    employee.manager_data AS employee_manager_data,
    employee.engineer_info AS employee_engineer_info
FROM employee
WHERE employee.name = :name_1
OR employee.manager_data = :manager_data_1

继承加载API¶

function sqlalchemy.orm.with_polymorphic(base, classes, selectable=False, flat=False, polymorphic_on=None, aliased=False, innerjoin=False, _use_mapper_path=False, _existing_alias=None)¶

产生一个 AliasedClass 为给定基的后代映射器指定列的构造。

使用此方法将确保每个子代映射器的表都包含在FROM子句中，并允许对这些表使用filter（）条件。结果实例还将加载这些列，这样就不需要对这些列进行“后提取”。

参见

与多态性一起使用 -充分讨论 with_polymorphic() .

参数

• base¶ -- 要别名的基类。

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

• aliased¶ -- 如果为True，则可选择项将具有别名。对于联接，这意味着将从子查询内部选择联接，除非 with_polymorphic.flat flag设置为True，对于更简单的用例，建议这样做。

• flat¶ -- 布尔值，将传递给 FromClause.alias() 调用以便 Join 对象将对联接内的各个表进行别名，而不是创建子查询。对于右嵌套联接，这通常由所有现代数据库支持，并且通常生成更高效的查询。只要生成的SQL正常工作，就建议设置此标志。

• selectable¶ -- 一种表或子查询，它将用来代替生成自子句。如果任何所需类使用具体的表继承，则需要此参数，因为SQLAlchemy当前无法在表之间自动生成联合。如果使用，则 selectable 参数必须表示由每个映射类映射的完整表和列集。否则，未计数的映射列将导致其表直接附加到FROM子句，这通常会导致错误的结果。

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

• innerjoin¶ -- 如果为true，则将使用内部联接。只有在仅查询一个特定子类型时才应指定此项

function sqlalchemy.orm.selectin_polymorphic(base_cls, classes)¶

指示应为特定于子类的所有属性执行一个紧急加载。

这将对所有匹配的主键值使用一个带有in的附加select，并且每个查询类似于 "selectin" 设置在 mapper.polymorphic_load 参数。

1.2 新版功能.

参见

多态性选择素加载