SQLAlchemy 1.4 Documentation
SQLAlchemy ORM
- ORM Quick Start
- Object Relational Tutorial (1.x API)
- ORM Mapped Class Configuration
- Relationship Configuration
- Querying Data, Loading Objects
- ORM Querying Guide
- Loading Columns
- Relationship Loading Techniques
- Loading Inheritance Hierarchies¶
- Query API
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
- Previous: Relationship Loading Techniques
- Next: Query API
- Up: Home
- On this page:
Loading Inheritance Hierarchies¶
When classes are mapped in inheritance hierarchies using the “joined”, “single”, or “concrete” table inheritance styles as described at Mapping Class Inheritance Hierarchies, the usual behavior is that a query for a particular base class will also yield objects corresponding to subclasses as well. When a single query is capable of returning a result with a different class or subclasses per result row, we use the term “polymorphic loading”.
Within the realm of polymorphic loading, specifically with joined and single
table inheritance, there is an additional problem of which subclass attributes
are to be queried up front, and which are to be loaded later. When an attribute
of a particular subclass is queried up front, we can use it in our query as
something to filter on, and it also will be loaded when we get our objects
back. If it’s not queried up front, it gets loaded later when we first need
to access it. Basic control of this behavior is provided using the
with_polymorphic()
function, as well as two variants, the mapper
configuration mapper.with_polymorphic
in conjunction with
the mapper.polymorphic_load
option, and the Query
-level Query.with_polymorphic()
method. The “with_polymorphic” family
each provide a means of specifying which specific subclasses of a particular
base class should be included within a query, which implies what columns and
tables will be available in the SELECT.
Using with_polymorphic¶
For the following sections, assume the Employee
/ Engineer
/ Manager
examples introduced in Mapping Class Inheritance Hierarchies.
Normally, when a Query
specifies the base class of an
inheritance hierarchy, only the columns that are local to that base
class are queried:
session.query(Employee).all()
Above, for both single and joined table inheritance, only the columns
local to Employee
will be present in the SELECT. We may get back
instances of Engineer
or Manager
, however they will not have the
additional attributes loaded until we first access them, at which point a
lazy load is emitted.
Similarly, if we wanted to refer to columns mapped
to Engineer
or Manager
in our query that’s against Employee
,
these columns aren’t available directly in either the single or joined table
inheritance 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 with_polymorphic()
function
provides a special AliasedClass
that represents a range of
columns across subclasses. This object can be used in a Query
like any other alias. When queried, it represents all the columns present in
the 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 SELECT statement 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” are included. Similar behavior occurs in the case of single table inheritance.
with_polymorphic()
accepts a single class or
mapper, a list of classes/mappers, or the string '*'
to indicate all
subclasses:
# 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, "*")
Tip
It’s important to note that with_polymorphic()
only affects the
columns that are included in fetched rows, and not the types of
objects returned. A call to with_polymorphic(Employee, [Manager])
will refer to rows that contain all types of Employee
objects,
including not only Manager
objects, but also Engineer
objects as
these are subclasses of Employee
, as well as Employee
instances if
these are present in the database. The effect of using
with_polymorphic(Employee, [Manager])
would only provide the behavior
that additional columns specific to Manager
will be eagerly loaded in
result rows, and as described below in
Referring to Specific Subclass Attributes also be available for use
within the WHERE clause of the SELECT statement.
Using aliasing with with_polymorphic¶
The with_polymorphic()
function also provides “aliasing” of the
polymorphic selectable itself, meaning, two different with_polymorphic()
entities, referring to the same class hierarchy, can be used together. This
is available using the with_polymorphic.aliased
flag.
For a polymorphic selectable that is across multiple tables, the default behavior
is to wrap the selectable into a subquery. Below we emit a query that will
select for “employee or manager” paired with “employee or engineer” on employees
with 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 best
encapsulation of the two distinct queries, it may be inefficient.
with_polymorphic()
includes an additional flag to help with this
situation, with_polymorphic.flat
, which will “flatten” the
subquery / join combination into straight joins, applying aliasing to the
individual tables instead. Setting with_polymorphic.flat
implies with_polymorphic.aliased
, so only one flag
is 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 with_polymorphic.flat
, it is often the
case when used in conjunction with joined table inheritance that we get a
right-nested JOIN in our statement. Some older databases, in particular older
versions of SQLite, may have a problem with this syntax, although virtually all
modern database versions now support this syntax.
Note
The with_polymorphic.flat
flag only applies to the use
of with_polymorphic
with joined table inheritance and when
the with_polymorphic.selectable
argument is not used.
Referring to Specific Subclass Attributes¶
The entity returned by with_polymorphic()
is an AliasedClass
object, which can be used in a Query
like any other alias, including
named attributes for those attributes on the Employee
class. In our
previous example, eng_plus_manager
becomes the entity that we use to refer to the
three-way outer join above. It also includes namespaces for each class named
in the list of classes, so that attributes specific to those subclasses can be
called upon as well. The following example illustrates calling upon attributes
specific 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",
)
)
A query as above would generate SQL resembling 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
WHERE
engineer.engineer_info=? OR
manager.manager_data=?
['x', 'y']
Setting with_polymorphic at mapper configuration time¶
The with_polymorphic()
function serves the purpose of allowing
“eager” loading of attributes from subclass tables, as well as the ability
to refer to the attributes from subclass tables at query time. Historically,
the “eager loading” of columns has been the more important part of the
equation. So just as eager loading for relationships can be specified
as a configurational option, the mapper.with_polymorphic
configuration parameter allows an entity to use a polymorphic load by
default. We can add the parameter to our Employee
mapping
first 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 the
case of joined table inheritance, this option
should be used sparingly, as it implies that the mapping will always emit
a (often large) series of LEFT OUTER JOIN to many tables, which is not
efficient from a SQL perspective. For single table inheritance, specifying the
asterisk 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 with_polymorphic()
or Query.with_polymorphic()
will override the mapper-level mapper.with_polymorphic
setting.
The mapper.with_polymorphic
option also accepts a list of
classes just like with_polymorphic()
to polymorphically load among
a subset of classes. However, when using Declarative, providing classes
to this list is not directly possible as the subclasses we’d like to add
are not available yet. Instead, we can specify on each subclass
that they should individually participate in polymorphic loading by
default 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 above
are part of the “polymorphic load” of the base Employee
class by default,
exactly as though they had been appended to the
mapper.with_polymorphic
list of classes.
Setting with_polymorphic against a query¶
The with_polymorphic()
function evolved from a query-level
method Query.with_polymorphic()
. This method has the same purpose
as with_polymorphic()
, except is not as
flexible in its usage patterns in that it only applies to the first entity
of the Query
. It then takes effect for all occurrences of
that entity, so that the entity (and its subclasses) can be referred to
directly, rather than using an alias object. For simple cases it might be
considered 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 job
than the with_polymorphic()
function, as it needs to correctly
transform entities like Engineer
and Manager
appropriately, but
not interfere with other entities. If its flexibility is lacking, switch
to using with_polymorphic()
.
Polymorphic Selectin Loading¶
An alternative to using the with_polymorphic()
family of
functions to “eagerly” load the additional subclasses on an inheritance
mapping, primarily when using joined table inheritance, is to use polymorphic
“selectin” loading. This is an eager loading
feature which works similarly to the Select IN loading feature
of relationship loading. Given our example mapping, we can instruct
a load of Employee
to emit an extra SELECT per subclass by using
the 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 will be 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 place
by 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 with_polymorphic()
, when using the
selectin_polymorphic()
style of loading, we do not have the
ability to refer to the Engineer
or Manager
entities within our main
query as filter, order by, or other criteria, as these entities are not present
in the initial query that is used to locate results. However, we can apply
loader options that apply towards Engineer
or Manager
, which will take
effect when the secondary SELECT is emitted. Below we assume Manager
has
an additional relationship Manager.paperwork
, that we’d like to eagerly
load as well. We can use any type of eager loading, such as joined eager
loading 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, however
the 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 of selectin relationship loading; for entities that make use of a composite primary key, the database in use must support tuples with “IN”, currently known to work with MySQL and PostgreSQL.
New in version 1.2.
Warning
The selectin polymorphic loading feature should be considered as 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 hierarchy
that itself uses “with_polymorphic”. A particular use case is that of
using selectin loading to load a joined-inheritance subtable, which then
uses “with_polymorphic” to refer to further sub-classes, which may be
joined- or single-table inheritance. If we added a class VicePresident
that
extends Manager
using single-table inheritance, we could ensure that
a 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 the
VicePresident
subclass. This subclass is linked to the polymorphic
identity "vp"
which refers to rows which have this data. By setting the
load style to “inline”, it means that a load of Manager
objects will also
ensure that the vp_info
column is queried for in the same SELECT statement.
A query against Employee
that encounters a Manager
row would emit
similarly 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” polymorphic loading with query-time
with_polymorphic()
usage is also possible (though this is very
outer-space stuff!); assuming the above mappings had no polymorphic_load
set 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 used
in querying in order to refer to the linkage between two mappings. Common
uses for this are to refer to a relationship()
in Query.join()
as well as in loader options like joinedload()
. When using
relationship()
where the target class is an inheritance hierarchy,
the API allows that the join, eager load, or other linkage should target a specific
subclass, alias, or with_polymorphic()
alias, of that class hierarchy,
rather than the class directly targeted by the relationship()
.
The of_type()
method allows the
construction of joins along relationship()
paths while
narrowing the criterion to specific derived aliases or subclasses. Suppose the
employees
table represents a collection of employees which are associated
with a Company
object. We’ll add a company_id
column to the
employees
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, the
Query.join()
method as well as operators like PropComparator.any()
and PropComparator.has()
will create
a join from company
to employee
, without including engineer
or
manager
in the mix. If we wish to have criterion which is specifically
against the Engineer
class, we can tell those methods to join or subquery
against the set of columns representing the subclass using the
PropComparator.of_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 both
Engineer
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 also
can 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()
and
other eagerloader options support
paths which make use of of_type()
.
Below, we load Company
rows while eagerly loading related Engineer
objects, 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()
, PropComparator.of_type()
can be used to combine eager loading and with_polymorphic()
,
so that all sub-attributes of all referenced subtypes
can 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 with
joinedload()
, the with_polymorphic()
object must be against
an “aliased” object, that is an instance of Alias
, so that the
polymorphic selectable is aliased (an informative error message is raised
otherwise).
The typical way to do this is to include the
with_polymorphic.aliased
or flat
flag, which will
apply this aliasing automatically. However, if the
with_polymorphic.selectable
argument is being used to pass an
object that is already an Alias
object then this flag should
not be set. The “flat” option implies the “aliased” option and is an
alternate form of aliasing against join objects that produces fewer
subqueries.
Once PropComparator.of_type()
is the target of the eager load,
that’s the entity we would use for subsequent chaining, not the original class
or derived class. If we wanted to further eager load a collection on the
eager-loaded Engineer
class, we access this class from the namespace of the
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 subclass
that represents a JOIN of two tables such as our Engineer
example
from 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 will
contain all columns from employee
and engineer
loaded.
However, when emitting a Query
against a base class, the behavior
is to load only from the base table:
session.query(Employee).all()
Above, the default behavior would be to SELECT only from the employee
table and not from any “sub” tables (engineer
and manager
, in our
previous 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 the
query, and as attributes are requested from those Employee
objects which are
represented in either the engineer
or manager
child tables, a second
load is issued for the columns in that related row, if the data was not
already loaded. So above, after accessing the objects you’d see further SQL
issued 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 with_polymorphic()
function and related configuration options allow us to instead emit a JOIN up
front which will conditionally load against employee
, engineer
, or
manager
, 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 the
engineer
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 with_polymorphic()
function and its configurational variants.
See also
Loading objects with single table inheritance¶
In modern Declarative, single inheritance mappings produce Column
objects that are mapped only to a subclass, and not available from the
superclass, even though they are present on the same table.
In our example from Single Table Inheritance, the Manager
mapping for example had a
Column
specified:
class Manager(Employee):
manager_data = Column(String(50))
__mapper_args__ = {"polymorphic_identity": "manager"}
Above, there would be no Employee.manager_data
attribute, even though the employee
table has a manager_data
column.
A query against Manager
will include this column in the query, as well
as 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 query
against 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, accessing
additional columns only mapped to Manager
emits a lazy load
for 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 with_polymorphic()
function serves a similar role as joined
inheritance in the case of single inheritance; it allows both for eager loading
of 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 the
columns present in Manager
or Engineer
using the “polymorphic” namespace.
Since we specified "*"
for the entities, both Engineer
and
Manager
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¶
Object Name | Description |
---|---|
selectin_polymorphic(base_cls, classes) |
Indicate an eager load should take place for all attributes specific to a subclass. |
with_polymorphic(base, classes[, selectable, flat, ...]) |
Produce an |
- function sqlalchemy.orm.with_polymorphic(base, classes, selectable=False, flat=False, polymorphic_on=None, aliased=False, adapt_on_names=False, innerjoin=False, _use_mapper_path=False, _existing_alias=None)¶
Produce an
AliasedClass
construct which specifies columns for descendant mappers of the given base.Using this method will ensure that each descendant mapper’s tables are included in the FROM clause, and will allow filter() criterion to be used against those tables. The resulting instances will also have those columns already loaded so that no “post fetch” of those columns will be required.
See also
Using with_polymorphic - full discussion of
with_polymorphic()
.- Parameters:
base¶ – Base class to be aliased.
classes¶ – a single class or mapper, or list of class/mappers, which inherit from the base class. Alternatively, it may also be the string
'*'
, in which case all descending mapped classes will be added to the FROM clause.aliased¶ – when True, the selectable will be aliased. For a JOIN, this means the JOIN will be SELECTed from inside of a subquery unless the
with_polymorphic.flat
flag is set to True, which is recommended for simpler use cases.flat¶ – Boolean, will be passed through to the
FromClause.alias()
call so that aliases ofJoin
objects will alias the individual tables inside the join, rather than creating a subquery. This is generally supported by all modern databases with regards to right-nested joins and generally produces more efficient queries. Setting this flag is recommended as long as the resulting SQL is functional.selectable¶ –
a table or subquery that will be used in place of the generated FROM clause. This argument is required if any of the desired classes use concrete table inheritance, since SQLAlchemy currently cannot generate UNIONs among tables automatically. If used, the
selectable
argument must represent the full set of tables and columns mapped by every mapped class. Otherwise, the unaccounted mapped columns will result in their table being appended directly to the FROM clause which will usually lead to incorrect results.When left at its default value of
False
, the polymorphic selectable assigned to the base mapper is used for selecting rows. However, it may also be passed asNone
, which will bypass the configured polymorphic selectable and instead construct an ad-hoc selectable for the target classes given; for joined table inheritance this will be a join that includes all target mappers and their subclasses.polymorphic_on¶ – a column to be used as the “discriminator” column for the given selectable. If not given, the polymorphic_on attribute of the base classes’ mapper will be used, if any. This is useful for mappings that don’t have polymorphic loading behavior by default.
innerjoin¶ – if True, an INNER JOIN will be used. This should only be specified if querying for one specific subtype only
adapt_on_names¶ –
Passes through the
aliased.adapt_on_names
parameter to the aliased object. This may be useful in situations where the given selectable is not directly related to the existing mapped selectable.New in version 1.4.33.
- function sqlalchemy.orm.selectin_polymorphic(base_cls, classes)¶
Indicate an eager load should take place for all attributes specific to a subclass.
This uses an additional SELECT with IN against all matched primary key values, and is the per-query analogue to the
"selectin"
setting on themapper.polymorphic_load
parameter.New in version 1.2.
See also
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Wed 30 Oct 2024 02:18:58 PM EDT