3
3
Mapping Class Inheritance Hierarchies
4
4
======================================
6
SQLAlchemy supports three forms of inheritance: *single table inheritance*,
7
where several types of classes are stored in one table, *concrete table
8
inheritance*, where each type of class is stored in its own table, and *joined
9
table inheritance*, where the parent/child classes are stored in their own
10
tables that are joined together in a select. Whereas support for single and
11
joined table inheritance is strong, concrete table inheritance is a less
12
common scenario with some particular problems so is not quite as flexible.
6
SQLAlchemy supports three forms of inheritance: **single table inheritance**,
7
where several types of classes are represented by a single table, **concrete table
8
inheritance**, where each type of class is represented by independent tables,
10
table inheritance**, where the class hierarchy is broken up
11
among dependent tables, each class represented by its own table that only
12
includes those attributes local to that class.
14
The most common forms of inheritance are single and joined table, while
15
concrete inheritance presents more configurational challenges.
14
17
When mappers are configured in an inheritance relationship, SQLAlchemy has the
15
ability to load elements "polymorphically", meaning that a single query can
18
ability to load elements :term:`polymorphically`, meaning that a single query can
16
19
return objects of multiple types.
20
This section currently uses classical mappings to illustrate inheritance
21
configurations, and will soon be updated to standardize on Declarative.
22
Until then, please refer to :ref:`declarative_inheritance` for information on
23
how common inheritance mappings are constructed declaratively.
25
For the following sections, assume this class relationship:
27
.. sourcecode:: python+sql
29
class Employee(object):
30
def __init__(self, name):
33
return self.__class__.__name__ + " " + self.name
35
class Manager(Employee):
36
def __init__(self, name, manager_data):
38
self.manager_data = manager_data
41
self.__class__.__name__ + " " +
42
self.name + " " + self.manager_data
45
class Engineer(Employee):
46
def __init__(self, name, engineer_info):
48
self.engineer_info = engineer_info
51
self.__class__.__name__ + " " +
52
self.name + " " + self.engineer_info
55
21
Joined Table Inheritance
56
22
-------------------------
58
24
In joined table inheritance, each class along a particular classes' list of
59
25
parents is represented by a unique table. The total set of attributes for a
60
26
particular instance is represented as a join along all tables in its
61
inheritance path. Here, we first define a table to represent the ``Employee``
62
class. This table will contain a primary key column (or columns), and a column
27
inheritance path. Here, we first define the ``Employee`` class.
28
This table will contain a primary key column (or columns), and a column
63
29
for each attribute that's represented by ``Employee``. In this case it's just
66
employees = Table('employees', metadata,
67
Column('employee_id', Integer, primary_key=True),
68
Column('name', String(50)),
69
Column('type', String(30), nullable=False)
72
The table also has a column called ``type``. It is strongly advised in both
73
single- and joined- table inheritance scenarios that the root table contains a
74
column whose sole purpose is that of the **discriminator**; it stores a value
33
__tablename__ = 'employee'
34
id = Column(Integer, primary_key=True)
35
name = Column(String(50))
36
type = Column(String(50))
39
'polymorphic_identity':'employee',
43
The mapped table also has a column called ``type``. The purpose of
44
this column is to act as the **discriminator**, and stores a value
75
45
which indicates the type of object represented within the row. The column may
76
be of any desired datatype. While there are some "tricks" to work around the
77
requirement that there be a discriminator column, they are more complicated to
78
configure when one wishes to load polymorphically.
80
Next we define individual tables for each of ``Engineer`` and ``Manager``,
81
which contain columns that represent the attributes unique to the subclass
46
be of any datatype, though string and integer are the most common.
48
The discriminator column is only needed if polymorphic loading is
49
desired, as is usually the case. It is not strictly necessary that
50
it be present directly on the base mapped table, and can instead be defined on a
51
derived select statement that's used when the class is queried;
52
however, this is a much more sophisticated configuration scenario.
54
The mapping receives additional arguments via the ``__mapper_args__``
55
dictionary. Here the ``type`` column is explicitly stated as the
56
discriminator column, and the **polymorphic identity** of ``employee``
57
is also given; this is the value that will be
58
stored in the polymorphic discriminator column for instances of this
61
We next define ``Engineer`` and ``Manager`` subclasses of ``Employee``.
62
Each contains columns that represent the attributes unique to the subclass
82
63
they represent. Each table also must contain a primary key column (or
83
columns), and in most cases a foreign key reference to the parent table. It is
84
standard practice that the same column is used for both of these roles, and
85
that the column is also named the same as that of the parent table. However
86
this is optional in SQLAlchemy; separate columns may be used for primary key
87
and parent-relationship, the column may be named differently than that of the
88
parent, and even a custom join condition can be specified between parent and
89
child tables instead of using a foreign key::
91
engineers = Table('engineers', metadata,
92
Column('employee_id', Integer,
93
ForeignKey('employees.employee_id'),
95
Column('engineer_info', String(50)),
98
managers = Table('managers', metadata,
99
Column('employee_id', Integer,
100
ForeignKey('employees.employee_id'),
102
Column('manager_data', String(50)),
105
One natural effect of the joined table inheritance configuration is that the
106
identity of any mapped object can be determined entirely from the base table.
107
This has obvious advantages, so SQLAlchemy always considers the primary key
108
columns of a joined inheritance class to be those of the base table only,
109
unless otherwise manually configured. In other words, the ``employee_id``
110
column of both the ``engineers`` and ``managers`` table is not used to locate
111
the ``Engineer`` or ``Manager`` object itself - only the value in
112
``employees.employee_id`` is considered, and the primary key in this case is
113
non-composite. ``engineers.employee_id`` and ``managers.employee_id`` are
114
still of course critical to the proper operation of the pattern overall as
115
they are used to locate the joined row, once the parent row has been
116
determined, either through a distinct SELECT statement or all at once within a
119
We then configure mappers as usual, except we use some additional arguments to
120
indicate the inheritance relationship, the polymorphic discriminator column,
121
and the **polymorphic identity** of each class; this is the value that will be
122
stored in the polymorphic discriminator column.
124
.. sourcecode:: python+sql
126
mapper(Employee, employees, polymorphic_on=employees.c.type,
127
polymorphic_identity='employee')
128
mapper(Engineer, engineers, inherits=Employee,
129
polymorphic_identity='engineer')
130
mapper(Manager, managers, inherits=Employee,
131
polymorphic_identity='manager')
133
And that's it. Querying against ``Employee`` will return a combination of
64
columns), and in most cases a foreign key reference to the parent table::
66
class Engineer(Employee):
67
__tablename__ = 'engineer'
68
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
69
engineer_name = Column(String(30))
72
'polymorphic_identity':'engineer',
75
class Manager(Employee):
76
__tablename__ = 'manager'
77
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
78
manager_name = Column(String(30))
81
'polymorphic_identity':'manager',
84
It is standard practice that the same column is used for both the role
85
of primary key as well as foreign key to the parent table,
86
and that the column is also named the same as that of the parent table.
87
However, both of these practices are optional. Separate columns may be used for
88
primary key and parent-relationship, the column may be named differently than
89
that of the parent, and even a custom join condition can be specified between
90
parent and child tables instead of using a foreign key.
92
.. topic:: Joined inheritance primary keys
94
One natural effect of the joined table inheritance configuration is that the
95
identity of any mapped object can be determined entirely from the base table.
96
This has obvious advantages, so SQLAlchemy always considers the primary key
97
columns of a joined inheritance class to be those of the base table only.
98
In other words, the ``id``
99
columns of both the ``engineer`` and ``manager`` tables are not used to locate
100
``Engineer`` or ``Manager`` objects - only the value in
101
``employee.id`` is considered. ``engineer.id`` and ``manager.id`` are
102
still of course critical to the proper operation of the pattern overall as
103
they are used to locate the joined row, once the parent row has been
104
determined within a statement.
106
With the joined inheritance mapping complete, querying against ``Employee`` will return a combination of
134
107
``Employee``, ``Engineer`` and ``Manager`` objects. Newly saved ``Engineer``,
135
108
``Manager``, and ``Employee`` objects will automatically populate the
136
``employees.type`` column with ``engineer``, ``manager``, or ``employee``, as
109
``employee.type`` column with ``engineer``, ``manager``, or ``employee``, as
139
112
.. _with_polymorphic:
170
142
.. sourcecode:: python+sql
173
SELECT managers.employee_id AS managers_employee_id,
174
managers.manager_data AS managers_manager_data
176
WHERE ? = managers.employee_id
145
SELECT manager.id AS manager_id,
146
manager.manager_data AS manager_manager_data
178
SELECT engineers.employee_id AS engineers_employee_id,
179
engineers.engineer_info AS engineers_engineer_info
181
WHERE ? = engineers.employee_id
150
SELECT engineer.id AS engineer_id,
151
engineer.engineer_info AS engineer_engineer_info
153
WHERE ? = engineer.id
184
156
This behavior works well when issuing searches for small numbers of items,
185
157
such as when using :meth:`.Query.get`, since the full range of joined tables are not
186
158
pulled in to the SQL statement unnecessarily. But when querying a larger span
187
159
of rows which are known to be of many types, you may want to actively join to
188
some or all of the joined tables. The ``with_polymorphic`` feature of
189
:class:`~sqlalchemy.orm.query.Query` and ``mapper`` provides this.
160
some or all of the joined tables. The ``with_polymorphic`` feature
191
163
Telling our query to polymorphically load ``Engineer`` and ``Manager``
194
.. sourcecode:: python+sql
196
query = session.query(Employee).with_polymorphic([Engineer, Manager])
198
produces a query which joins the ``employees`` table to both the ``engineers`` and ``managers`` tables like the following:
164
objects, we can use the :func:`.orm.with_polymorphic` function
165
to create a new aliased class which represents a select of the base
166
table combined with outer joins to each of the inheriting tables::
168
from sqlalchemy.orm import with_polymorphic
170
eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
172
query = session.query(eng_plus_manager)
174
The above produces a query which joins the ``employee`` table to both the
175
``engineer`` and ``manager`` tables like the following:
200
177
.. sourcecode:: python+sql
204
SELECT employees.employee_id AS employees_employee_id,
205
engineers.employee_id AS engineers_employee_id,
206
managers.employee_id AS managers_employee_id,
207
employees.name AS employees_name,
208
employees.type AS employees_type,
209
engineers.engineer_info AS engineers_engineer_info,
210
managers.manager_data AS managers_manager_data
212
LEFT OUTER JOIN engineers
213
ON employees.employee_id = engineers.employee_id
214
LEFT OUTER JOIN managers
215
ON employees.employee_id = managers.employee_id
181
SELECT employee.id AS employee_id,
182
engineer.id AS engineer_id,
183
manager.id AS manager_id,
184
employee.name AS employee_name,
185
employee.type AS employee_type,
186
engineer.engineer_info AS engineer_engineer_info,
187
manager.manager_data AS manager_manager_data
189
LEFT OUTER JOIN engineer
190
ON employee.id = engineer.id
191
LEFT OUTER JOIN manager
192
ON employee.id = manager.id
218
:func:`~sqlalchemy.orm.query.Query.with_polymorphic` accepts a single class or
195
The entity returned by :func:`.orm.with_polymorphic` is an :class:`.AliasedClass`
196
object, which can be used in a :class:`.Query` like any other alias, including
197
named attributes for those attributes on the ``Employee`` class. In our
198
example, ``eng_plus_manager`` becomes the entity that we use to refer to the
199
three-way outer join above. It also includes namespaces for each class named
200
in the list of classes, so that attributes specific to those subclasses can be
201
called upon as well. The following example illustrates calling upon attributes
202
specific to ``Engineer`` as well as ``Manager`` in terms of ``eng_plus_manager``::
204
eng_plus_manager = with_polymorphic(Employee, [Engineer, Manager])
205
query = session.query(eng_plus_manager).filter(
207
eng_plus_manager.Engineer.engineer_info=='x',
208
eng_plus_manager.Manager.manager_data=='y'
212
:func:`.orm.with_polymorphic` accepts a single class or
219
213
mapper, a list of classes/mappers, or the string ``'*'`` to indicate all
222
216
.. sourcecode:: python+sql
224
# join to the engineers table
225
query.with_polymorphic(Engineer)
218
# join to the engineer table
219
entity = with_polymorphic(Employee, Engineer)
227
# join to the engineers and managers tables
228
query.with_polymorphic([Engineer, Manager])
221
# join to the engineer and manager tables
222
entity = with_polymorphic(Employee, [Engineer, Manager])
230
224
# join to all subclass tables
231
query.with_polymorphic('*')
225
entity = query.with_polymorphic(Employee, '*')
228
session.query(entity).all()
233
230
It also accepts a second argument ``selectable`` which replaces the automatic
234
231
join creation and instead selects directly from the selectable given. This
235
232
feature is normally used with "concrete" inheritance, described later, but can
236
233
be used with any kind of inheritance setup in the case that specialized SQL
237
should be used to load polymorphically:
239
.. sourcecode:: python+sql
234
should be used to load polymorphically::
241
236
# custom selectable
242
query.with_polymorphic(
237
employee = Employee.__table__
238
manager = Manager.__table__
239
engineer = Engineer.__table__
240
entity = with_polymorphic(
243
242
[Engineer, Manager],
244
employees.outerjoin(managers).outerjoin(engineers)
243
employee.outerjoin(manager).outerjoin(engineer)
247
:func:`~sqlalchemy.orm.query.Query.with_polymorphic` is also needed
248
when you wish to add filter criteria that are specific to one or more
249
subclasses; it makes the subclasses' columns available to the WHERE clause:
251
.. sourcecode:: python+sql
253
session.query(Employee).with_polymorphic([Engineer, Manager]).\
254
filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q'))
247
session.query(entity).all()
256
249
Note that if you only need to load a single subtype, such as just the
257
``Engineer`` objects, :func:`~sqlalchemy.orm.query.Query.with_polymorphic` is
250
``Engineer`` objects, :func:`.orm.with_polymorphic` is
258
251
not needed since you would query against the ``Engineer`` class directly.
253
:meth:`.Query.with_polymorphic` has the same purpose
254
as :func:`.orm.with_polymorphic`, except is not as
255
flexible in its usage patterns in that it only applies to the first full
256
mapping, which then impacts all occurrences of that class or the target
257
subclasses within the :class:`.Query`. For simple cases it might be
258
considered to be more succinct::
260
session.query(Employee).with_polymorphic([Engineer, Manager]).\
261
filter(or_(Engineer.engineer_info=='w', Manager.manager_data=='q'))
263
.. versionadded:: 0.8
264
:func:`.orm.with_polymorphic`, an improved version of
265
:meth:`.Query.with_polymorphic` method.
260
267
The mapper also accepts ``with_polymorphic`` as a configurational argument so
261
268
that the joined-style load will be issued automatically. This argument may be
262
269
the string ``'*'``, a list of classes, or a tuple consisting of either,
263
followed by a selectable.
265
.. sourcecode:: python+sql
267
mapper(Employee, employees, polymorphic_on=employees.c.type,
268
polymorphic_identity='employee',
269
with_polymorphic='*')
270
mapper(Engineer, engineers, inherits=Employee,
271
polymorphic_identity='engineer')
272
mapper(Manager, managers, inherits=Employee,
273
polymorphic_identity='manager')
270
followed by a selectable::
272
class Employee(Base):
273
__tablename__ = 'employee'
274
id = Column(Integer, primary_key=True)
275
type = Column(String(20))
278
'polymorphic_on':type,
279
'polymorphic_identity':'employee',
280
'with_polymorphic':'*'
283
class Engineer(Employee):
284
__tablename__ = 'engineer'
285
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
286
__mapper_args__ = {'polymorphic_identity':'engineer'}
288
class Manager(Employee):
289
__tablename__ = 'manager'
290
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
291
__mapper_args__ = {'polymorphic_identity':'manager'}
275
293
The above mapping will produce a query similar to that of
276
294
``with_polymorphic('*')`` for every query of ``Employee`` objects.
278
Using :func:`~sqlalchemy.orm.query.Query.with_polymorphic` with
279
:class:`~sqlalchemy.orm.query.Query` will override the mapper-level
280
``with_polymorphic`` setting.
296
Using :func:`.orm.with_polymorphic` or :meth:`.Query.with_polymorphic`
297
will override the mapper-level ``with_polymorphic`` setting.
299
.. autofunction:: sqlalchemy.orm.with_polymorphic
282
301
Advanced Control of Which Tables are Queried
283
302
+++++++++++++++++++++++++++++++++++++++++++++
285
The :meth:`.Query.with_polymorphic` method and configuration works fine for
286
simplistic scenarios. However, it currently does not work with any
287
:class:`.Query` that selects against individual columns or against multiple
288
classes - it also has to be called at the outset of a query.
304
The ``with_polymorphic`` functions work fine for
305
simplistic scenarios. However, direct control of table rendering
306
is called for, such as the case when one wants to
307
render to only the subclass table and not the parent table.
290
For total control of how :class:`.Query` joins along inheritance relationships,
291
use the :class:`.Table` objects directly and construct joins manually. For example, to
309
This use case can be achieved by using the mapped :class:`.Table`
310
objects directly. For example, to
292
311
query the name of employees with particular criterion::
313
engineer = Engineer.__table__
314
manager = Manager.__table__
294
316
session.query(Employee.name).\
295
317
outerjoin((engineer, engineer.c.employee_id==Employee.employee_id)).\
296
318
outerjoin((manager, manager.c.employee_id==Employee.employee_id)).\
322
346
.. sourcecode:: python+sql
324
companies = Table('companies', metadata,
325
Column('company_id', Integer, primary_key=True),
326
Column('name', String(50))
329
employees = Table('employees', metadata,
330
Column('employee_id', Integer, primary_key=True),
331
Column('name', String(50)),
332
Column('type', String(30), nullable=False),
333
Column('company_id', Integer, ForeignKey('companies.company_id'))
336
class Company(object):
339
mapper(Company, companies, properties={
340
'employees': relationship(Employee)
349
__tablename__ = 'company'
350
id = Column(Integer, primary_key=True)
351
name = Column(String(50))
352
employees = relationship("Employee",
354
cascade='all, delete-orphan')
356
class Employee(Base):
357
__tablename__ = 'employee'
358
id = Column(Integer, primary_key=True)
359
type = Column(String(20))
360
company_id = Column(Integer, ForeignKey('company.id'))
362
'polymorphic_on':type,
363
'polymorphic_identity':'employee',
364
'with_polymorphic':'*'
367
class Engineer(Employee):
368
__tablename__ = 'engineer'
369
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
370
engineer_info = Column(String(50))
371
__mapper_args__ = {'polymorphic_identity':'engineer'}
373
class Manager(Employee):
374
__tablename__ = 'manager'
375
id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
376
manager_data = Column(String(50))
377
__mapper_args__ = {'polymorphic_identity':'manager'}
343
379
When querying from ``Company`` onto the ``Employee`` relationship, the
344
380
``join()`` method as well as the ``any()`` and ``has()`` operators will create
345
a join from ``companies`` to ``employees``, without including ``engineers`` or
346
``managers`` in the mix. If we wish to have criterion which is specifically
381
a join from ``company`` to ``employee``, without including ``engineer`` or
382
``manager`` in the mix. If we wish to have criterion which is specifically
347
383
against the ``Engineer`` class, we can tell those methods to join or subquery
348
384
against the joined table representing the subclass using the
349
:func:`~sqlalchemy.orm.interfaces.PropComparator.of_type` operator::
385
:meth:`~.orm.interfaces.PropComparator.of_type` operator::
351
387
session.query(Company).\
352
388
join(Company.employees.of_type(Engineer)).\
355
391
A longhand version of this would involve spelling out the full target
356
392
selectable within a 2-tuple::
394
employee = Employee.__table__
395
engineer = Engineer.__table__
358
397
session.query(Company).\
359
join((employees.join(engineers), Company.employees)).\
398
join((employee.join(engineer), Company.employees)).\
360
399
filter(Engineer.engineer_info=='someinfo')
362
Currently, :func:`~sqlalchemy.orm.interfaces.PropComparator.of_type` accepts a
363
single class argument. It may be expanded later on to accept multiple classes.
364
For now, to join to any group of subclasses, the longhand notation allows this
367
.. sourcecode:: python+sql
369
session.query(Company).\
371
(employees.outerjoin(engineers).outerjoin(managers),
375
or_(Engineer.engineer_info=='someinfo',
376
Manager.manager_data=='somedata')
401
:func:`~sqlalchemy.orm.interfaces.PropComparator.of_type` accepts a
402
single class argument. More flexibility can be achieved either by
403
joining to an explicit join as above, or by using the :func:`.orm.with_polymorphic`
404
function to create a polymorphic selectable::
406
manager_and_engineer = with_polymorphic(
407
Employee, [Manager, Engineer],
410
session.query(Company).\
411
join(manager_and_engineer, Company.employees).\
413
or_(manager_and_engineer.Engineer.engineer_info=='someinfo',
414
manager_and_engineer.Manager.manager_data=='somedata')
417
Above, we use the ``aliased=True`` argument with :func:`.orm.with_polymorhpic`
418
so that the right hand side of the join between ``Company`` and ``manager_and_engineer``
419
is converted into an aliased subquery. Some backends, such as SQLite and older
420
versions of MySQL can't handle a FROM clause of the following form::
422
FROM x JOIN (y JOIN z ON <onclause>) ON <onclause>
424
Using ``aliased=True`` instead renders it more like::
426
FROM x JOIN (SELECT * FROM y JOIN z ON <onclause>) AS anon_1 ON <onclause>
428
The above join can also be expressed more succinctly by combining ``of_type()``
429
with the polymorphic construct::
431
manager_and_engineer = with_polymorphic(
432
Employee, [Manager, Engineer],
435
session.query(Company).\
436
join(Company.employees.of_type(manager_and_engineer)).\
438
or_(manager_and_engineer.Engineer.engineer_info=='someinfo',
439
manager_and_engineer.Manager.manager_data=='somedata')
379
442
The ``any()`` and ``has()`` operators also can be used with
380
443
:func:`~sqlalchemy.orm.interfaces.PropComparator.of_type` when the embedded
381
criterion is in terms of a subclass:
383
.. sourcecode:: python+sql
444
criterion is in terms of a subclass::
385
446
session.query(Company).\
420
501
.. sourcecode:: python+sql
422
employees_table = Table('employees', metadata,
423
Column('employee_id', Integer, primary_key=True),
424
Column('name', String(50)),
425
Column('manager_data', String(50)),
426
Column('engineer_info', String(50)),
427
Column('type', String(20), nullable=False)
430
employee_mapper = mapper(Employee, employees_table, \
431
polymorphic_on=employees_table.c.type, polymorphic_identity='employee')
432
manager_mapper = mapper(Manager, inherits=employee_mapper,
433
polymorphic_identity='manager')
434
engineer_mapper = mapper(Engineer, inherits=employee_mapper,
435
polymorphic_identity='engineer')
503
class Employee(Base):
504
__tablename__ = 'employee'
505
id = Column(Integer, primary_key=True)
506
name = Column(String(50))
507
manager_data = Column(String(50))
508
engineer_info = Column(String(50))
509
type = Column(String(20))
512
'polymorphic_on':type,
513
'polymorphic_identity':'employee'
516
class Manager(Employee):
518
'polymorphic_identity':'manager'
521
class Engineer(Employee):
523
'polymorphic_identity':'engineer'
437
526
Note that the mappers for the derived classes Manager and Engineer omit the
438
specification of their associated table, as it is inherited from the
439
employee_mapper. Omitting the table specification for derived mappers in
440
single-table inheritance is required.
527
``__tablename__``, indicating they do not have a mapped table of
442
530
.. _concrete_inheritance:
444
532
Concrete Table Inheritance
445
533
--------------------------
537
this section is currently using classical mappings. The
538
Declarative system fully supports concrete inheritance
539
however. See the links below for more information on using
540
declarative with concrete table inheritance.
447
542
This form of inheritance maps each class to a distinct table, as below:
449
544
.. sourcecode:: python+sql
added
removed
doc/build/orm/inheritance.rst
