Loading Inheritance Hierarchies

Loading Inheritance Hierarchies

When classes are mapped in inheritance hierarchies using the “joined”,“single”, or “concrete” table inheritance styles as described atMapping Class Inheritance Hierarchies, the usual behavior is that a query for aparticular base class will also yield objects corresponding to subclassesas well. When a single query is capable of returning a result witha different class or subclasses per result row, we use the term“polymorphic loading”.

Within the realm of polymorphic loading, specifically with joined and singletable inheritance, there is an additional problem of which subclass attributesare to be queried up front, and which are to be loaded later. When an attributeof a particular subclass is queried up front, we can use it in our query assomething to filter on, and it also will be loaded when we get our objectsback. If it’s not queried up front, it gets loaded later when we first needto access it. Basic control of this behavior is provided using theorm.with_polymorphic() function, as well as two variants, the mapperconfiguration mapper.with_polymorphic in conjunction withthe mapper.polymorphic_load option, and the Query-level Query.with_polymorphic() method. The “with_polymorphic” familyeach provide a means of specifying which specific subclasses of a particularbase class should be included within a query, which implies what columns andtables will be available in the SELECT.

Using with_polymorphic

For the following sections, assume the Employee / Engineer / Managerexamples introduced in Mapping Class Inheritance Hierarchies.

Normally, when a Query specifies the base class of aninheritance hierarchy, only the columns that are local to that baseclass are queried:

session.query(Employee).all()

Above, for both single and joined table inheritance, only the columnslocal to Employee will be present in the SELECT. We may get backinstances of Engineer or Manager, however they will not have theadditional attributes loaded until we first access them, at which point alazy load is emitted.

Similarly, if we wanted to refer to columns mappedto Engineer or Manager in our query that’s against Employee,these columns aren’t available directly in either the single or joined tableinheritance case, since the Employee entity does not refer to these columns(note that for single-table inheritance, this is common if Declarative is used,but not for a classical mapping).

To solve both of these issues, the orm.with_polymorphic() functionprovides a special AliasedClass that represents a range ofcolumns across subclasses. This object can be used in a Querylike any other alias. When queried, it represents all the columns present inthe classes given:

from sqlalchemy.orm import with_polymorphic
 
eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
 
query = session.query(eng_plus_manager)

If the above mapping were using joined table inheritance, the SELECTstatement for the above would be:

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 above, the additional tables / columns for “engineer” and “manager” areincluded. Similar behavior occurs in the case of single table inheritance.

orm.with_polymorphic() accepts a single class ormapper, a list of classes/mappers, or the string '*' to indicate allsubclasses:

# 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, '*')

Using aliasing with with_polymorphic

The orm.with_polymorphic() function also provides “aliasing” of thepolymorphic selectable itself, meaning, two different orm.with_polymorphic()entities, referring to the same class hierarchy, can be used together. Thisis available using the orm.with_polymorphic.aliased flag.For a polymorphic selectable that is across multiple tables, the default behavioris to wrap the selectable into a subquery. Below we emit a query that willselect for “employee or manager” paired with “employee or engineer” on employeeswith the same name:

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

The creation of subqueries above is very verbose. While it creates the bestencapsulation of the two distinct queries, it may be inefficient.orm.with_polymorphic() includes an additional flag to help with thissituation, orm.with_polymorphic.flat, which will “flatten” thesubquery / join combination into straight joins, applying aliasing to theindividual tables instead. Setting orm.with_polymorphic.flatimplies orm.with_polymorphic.aliased, so only one flagis necessary:

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

Note above, when using orm.with_polymorphic.flat, it is often thecase when used in conjunction with joined table inheritance that we get aright-nested JOIN in our statement. Some older databases, in particular olderversions of SQLite, may have a problem with this syntax, although virtually allmodern database versions now support this syntax.

Note

The orm.with_polymorphic.flat flag only applies to the useof with_polymorphic with joined table inheritance and whenthe with_polymorphic.selectable argument is not used.

Referring to Specific Subclass Attributes

The entity returned by orm.with_polymorphic() is an AliasedClassobject, which can be used in a Query like any other alias, includingnamed attributes for those attributes on the Employee class. In ourprevious example, eng_plus_manager becomes the entity that we use to refer to thethree-way outer join above. It also includes namespaces for each class namedin the list of classes, so that attributes specific to those subclasses can becalled upon as well. The following example illustrates calling upon attributesspecific to Engineer as well as Manager in terms of eng_plus_manager:

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'
                )
            )

Setting with_polymorphic at mapper configuration time

The orm.with_polymorphic() function serves the purpose of allowing“eager” loading of attributes from subclass tables, as well as the abilityto refer to the attributes from subclass tables at query time. Historically,the “eager loading” of columns has been the more important part of theequation. So just as eager loading for relationships can be specifiedas a configurational option, the mapper.with_polymorphicconfiguration parameter allows an entity to use a polymorphic load bydefault. We can add the parameter to our Employee mappingfirst introduced at Joined Table Inheritance:

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': '*'
    }

Above is a common setting for mapper.with_polymorphic,which is to indicate an asterisk to load all subclass columns. In thecase of joined table inheritance, this optionshould be used sparingly, as it implies that the mapping will always emita (often large) series of LEFT OUTER JOIN to many tables, which is notefficient from a SQL perspective. For single table inheritance, specifying theasterisk is often a good idea as the load is still against a single table only,but an additional lazy load of subclass-mapped columns will be prevented.

Using orm.with_polymorphic() or Query.with_polymorphic()will override the mapper-level mapper.with_polymorphic setting.

The mapper.with_polymorphic option also accepts a list ofclasses just like orm.with_polymorphic() to polymorphically load amonga subset of classes. However, when using Declarative, providing classesto this list is not directly possible as the subclasses we’d like to addare not available yet. Instead, we can specify on each subclassthat they should individually participate in polymorphic loading bydefault using the mapper.polymorphic_load parameter:

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'
    }

Setting the mapper.polymorphic_load parameter to the value"inline" means that the Engineer and Manager classes aboveare part of the “polymorphic load” of the base Employee class by default,exactly as though they had been appended to themapper.with_polymorphic list of classes.

Setting with_polymorphic against a query

The orm.with_polymorphic() function evolved from a query-levelmethod Query.with_polymorphic(). This method has the same purposeas orm.with_polymorphic(), except is not asflexible in its usage patterns in that it only applies to the first entityof the Query. It then takes effect for all occurrences ofthat entity, so that the entity (and its subclasses) can be referred todirectly, rather than using an alias object. For simple cases it might beconsidered to be more succinct:

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

The Query.with_polymorphic() method has a more complicated jobthan the orm.with_polymorphic() function, as it needs to correctlytransform entities like Engineer and Manager appropriately, butnot interfere with other entities. If its flexibility is lacking, switchto using orm.with_polymorphic().

Polymorphic Selectin Loading

An alternative to using the orm.with_polymorphic() family offunctions to “eagerly” load the additional subclasses on an inheritancemapping, primarily when using joined table inheritance, is to use polymorphic“selectin” loading. This is an eager loadingfeature which works similarly to the Select IN loading featureof relationship loading. Given our example mapping, we can instructa load of Employee to emit an extra SELECT per subclass by usingthe orm.selectin_polymorphic() loader option:

from sqlalchemy.orm import selectin_polymorphic
 
query = session.query(Employee).options(
    selectin_polymorphic(Employee, [Manager, Engineer])
)

When the above query is run, two additional SELECT statements willbe emitted:

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

We can similarly establish the above style of loading to take placeby default by specifying the mapper.polymorphic_load parameter,using the value "selectin" on a per-subclass basis:

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',
    }

Unlike when using orm.with_polymorphic(), when using theorm.selectin_polymorphic() style of loading, we do not have theability to refer to the Engineer or Manager entities within our mainquery as filter, order by, or other criteria, as these entities are not presentin the initial query that is used to locate results. However, we can applyloader options that apply towards Engineer or Manager, which will takeeffect when the secondary SELECT is emitted. Below we assume Manager hasan additional relationship Manager.paperwork, that we’d like to eagerlyload as well. We can use any type of eager loading, such as joined eagerloading via the joinedload() function:

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

Using the query above, we get three SELECT statements emitted, howeverthe one against Manager will be:

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

Note that selectin polymorphic loading has similar caveats as that ofselectin relationship loading; for entities that make use of a compositeprimary key, the database in use must support tuples with “IN”, currentlyknown to work with MySQL and PostgreSQL.

New in version 1.2.

Warning

The selectin polymorphic loading feature should be consideredas experimental within early releases of the 1.2 series.

Combining selectin and with_polymorphic

Note

works as of 1.2.0b3

With careful planning, selectin loading can be applied against a hierarchythat itself uses “with_polymorphic”. A particular use case is that ofusing selectin loading to load a joined-inheritance subtable, which thenuses “with_polymorphic” to refer to further sub-classes, which may bejoined- or single-table inheritance. If we added a class VicePresident thatextends Manager using single-table inheritance, we could ensure thata load of Manager also fully loads VicePresident subtypes at the same time:

# 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"
    }

Above, we add a vp_info column to the manager table, local to theVicePresident subclass. This subclass is linked to the polymorphicidentity "vp" which refers to rows which have this data. By setting theload style to “inline”, it means that a load of Manager objects will alsoensure that the vp_info column is queried for in the same SELECT statement.A query against Employee that encounters a Manager row would emitsimilarly to the following:

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

Combining “selectin” polymorhic loading with query-timeorm.with_polymorphic() usage is also possible (though this is veryouter-space stuff!); assuming the above mappings had no polymorphic_loadset up, we could get the same result as follows:

from sqlalchemy.orm import with_polymorphic, selectin_polymorphic
 
manager_poly = with_polymorphic(Manager, [VicePresident])
 
s.query(Employee).options(
    selectin_polymorphic(Employee, [manager_poly])).all()

Referring to specific subtypes on relationships

Mapped attributes which correspond to a relationship() are usedin querying in order to refer to the linkage between two mappings. Commonuses for this are to refer to a relationship() in Query.join()as well as in loader options like joinedload(). When usingrelationship() where the target class is an inheritance hierarchy,the API allows that the join, eager load, or other linkage should target a specificsubclass, alias, or orm.with_polymorphic() alias, of that class hierarchy,rather than the class directly targeted by the relationship().

The of_type() method allows theconstruction of joins along relationship() paths whilenarrowing the criterion to specific derived aliases or subclasses. Suppose theemployees table represents a collection of employees which are associatedwith a Company object. We’ll add a company_id column to theemployees table and a new table 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'}

When querying from Company onto the Employee relationship, theQuery.join() method as well as operators like PropComparator.any()and PropComparator.has() will createa join from company to employee, without including engineer ormanager in the mix. If we wish to have criterion which is specificallyagainst the Engineer class, we can tell those methods to join or subqueryagainst the set of columns representing the subclass using theof_type() operator:

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

Similarly, to join from Company to the polymorphic entity that includes bothEngineer and Manager columns:

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'
        )
    )

The PropComparator.any() and PropComparator.has() operators alsocan be used with of_type(),such as when the embedded criterion is in terms of a subclass:

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

Eager Loading of Specific or Polymorphic Subtypes

The joinedload(), subqueryload(), contains_eager() andother eagerloader options supportpaths which make use of of_type().Below, we load Company rows while eagerly loading related Engineerobjects, querying the employee and engineer tables simultaneously:

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

As is the case with Query.join(), of_type()can be used to combine eager loading and orm.with_polymorphic(),so that all sub-attributes of all referenced subtypescan be loaded:

manager_and_engineer = with_polymorphic(
                            Employee, [Manager, Engineer],
                            flat=True)
 
session.query(Company).\
    options(
        joinedload(
            Company.employees.of_type(manager_and_engineer)
        )
    )

Note

When using with_polymorphic() in conjunction withjoinedload(), the with_polymorphic() object must be againstan “aliased” object, that is an instance of Alias, so that thepolymorphic selectable is aliased (an informative error message is raisedotherwise).

The typical way to do this is to include thewith_polymorphic.aliased or flat flag, which willapply this aliasing automatically. However, if thewith_polymorphic.selectable argument is being used to pass anobject that is already an Alias object then this flag shouldnot be set. The “flat” option implies the “aliased” option and is analternate form of aliasing against join objects that produces fewersubqueries.

Once of_type() is the target of the eager load,that’s the entity we would use for subsequent chaining, not the original classor derived class. If we wanted to further eager load a collection on theeager-loaded Engineer class, we access this class from the namespace of theorm.with_polymorphic() object:

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

Loading objects with joined table inheritance

When using joined table inheritance, if we query for a specific subclassthat represents a JOIN of two tables such as our Engineer examplefrom the inheritance section, the SQL emitted is a join:

session.query(Engineer).all()

The above query will emit SQL like:


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

We will then get a collection of Engineer objects back, which willcontain all columns from employee and engineer loaded.

However, when emitting a Query against a base class, the behavioris to load only from the base table:

session.query(Employee).all()

Above, the default behavior would be to SELECT only from the employeetable and not from any “sub” tables (engineer and manager, in ourprevious examples):


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

After a collection of Employee objects has been returned from thequery, and as attributes are requested from those Employee objects which arerepresented in either the engineer or manager child tables, a secondload is issued for the columns in that related row, if the data was notalready loaded. So above, after accessing the objects you’d see further SQLissued along the lines of:


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]

The orm.with_polymorphic()function and related configuration options allow us to instead emit a JOIN upfront which will conditionally load against employee, engineer, ormanager, very much like joined eager loading works for relationships,removing the necessity for a second per-entity load:

from sqlalchemy.orm import with_polymorphic
 
eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
 
query = session.query(eng_plus_manager)

The above produces a query which joins the employee table to both theengineer and manager tables like the following:

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
[]

The section Using with_polymorphic discusses the orm.with_polymorphic()function and its configurational variants.

See also

Using with_polymorphic

Loading objects with single table inheritance

In modern Declarative, single inheritance mappings produce Columnobjects that are mapped only to a subclass, and not available from thesuperclass, even though they are present on the same table.In our example from Single Table Inheritance, the Manager mapping for example had aColumn specified:

class Manager(Employee):
    manager_data = Column(String(50))
 
    __mapper_args__ = {
        'polymorphic_identity':'manager'
    }

Above, there would be no Employee.manager_dataattribute, even though the employee table has a manager_data column.A query against Manager will include this column in the query, as wellas an IN clause to limit rows only to Manager objects:

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',)

However, in a similar way to that of joined table inheritance, a queryagainst Employee will only query for columns mapped to Employee:

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

If we get back an instance of Manager from our result, accessingadditional columns only mapped to Manager emits a lazy loadfor those columns, in a similar way to joined inheritance:

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

The orm.with_polymorphic() function serves a similar role as joinedinheritance in the case of single inheritance; it allows both for eager loadingof subclass attributes as well as specification of subclasses in a query,just without the overhead of using OUTER JOIN:

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

Above, our query remains against a single table however we can refer to thecolumns present in Manager or Engineer using the “polymorphic” namespace.Since we specified "*" for the entities, both Engineer andManager will be loaded at once. SQL emitted would be:

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

Inheritance Loading API

sqlalchemy.orm.withpolymorphic(_base, classes, selectable=False, flat=False, polymorphic_on=None, aliased=False, innerjoin=False, usemapper_path=False, existingalias=None)
Produce an AliasedClass construct which specifiescolumns for descendant mappers of the given base.

Using this method will ensure that each descendant mapper’stables are included in the FROM clause, and will allow filter()criterion to be used against those tables. The resultinginstances will also have those columns already loaded so thatno “post fetch” of those columns will be required.

See also

Using with_polymorphic - full discussion oform.with_polymorphic().

Parameters
- base – Base class to be aliased.
- classes – a single class or mapper, or list ofclass/mappers, which inherit from the base class.Alternatively, it may also be the string '*', in which caseall descending mapped classes will be added to the FROM clause.
- aliased – when True, the selectable will be wrapped in analias, that is (SELECT * FROM <fromclauses>) AS anon_1.This can be important when using the with_polymorphic()to create the target of a JOIN on a backend that does notsupport parenthesized joins, such as SQLite and olderversions of MySQL. However if thewith_polymorphic.selectable parameter is in usewith an existing Alias construct, then you should notset this flag.
- flat –

Boolean, will be passed through to theFromClause.alias() call so that aliases of Joinobjects don’t include an enclosing SELECT. This can lead to moreefficient queries in many circumstances. A JOIN against a nested JOINwill be rewritten as a JOIN against an aliased SELECT subquery onbackends that don’t support this syntax.

Setting flat to True implies the aliased flag isalso True.

New in version 0.9.0.

See also

Join.alias()

selectable – a table or select() statement that willbe used in place of the generated FROM clause. This argument isrequired if any of the desired classes use concrete tableinheritance, since SQLAlchemy currently cannot generate UNIONsamong tables automatically. If used, the selectable argumentmust represent the full set of tables and columns mapped by everymapped class. Otherwise, the unaccounted mapped columns willresult in their table being appended directly to the FROM clausewhich will usually lead to incorrect results.

polymorphic_on – a column to be used as the “discriminator”column for the given selectable. If not given, the polymorphic_onattribute of the base classes’ mapper will be used, if any. Thisis useful for mappings that don’t have polymorphic loadingbehavior by default.

innerjoin – if True, an INNER JOIN will be used. This shouldonly be specified if querying for one specific subtype only

sqlalchemy.orm.selectinpolymorphic(_base_cls, classes)
Indicate an eager load should take place for all attributesspecific to a subclass.

This uses an additional SELECT with IN against all matched primarykey values, and is the per-query analogue to the "selectin"setting on the mapper.polymorphic_load parameter.

New in version 1.2.

See also

inheritance_polymorphic_load