Mapping Class Inheritance Hierarchies

SQLAlchemy supports three forms of inheritance: single table inheritance,where several types of classes are represented by a single table, concretetable inheritance, where each type of class is represented by independenttables, and joined table inheritance, where the class hierarchy is brokenup among dependent tables, each class represented by its own table that onlyincludes those attributes local to that class.

The most common forms of inheritance are single and joined table, whileconcrete inheritance presents more configurational challenges.

When mappers are configured in an inheritance relationship, SQLAlchemy has theability to load elements polymorphically, meaning that a single query canreturn objects of multiple types.

See also

Inheritance Mapping Recipes - complete examples of joined, single andconcrete inheritance

Joined Table Inheritance

In joined table inheritance, each class along a hierarchy of classesis represented by a distinct table. Querying for a particular subclassin the hierarchy will render as a SQL JOIN along all tables in itsinheritance path. If the queried class is the base class, the default behavioris to include only the base table in a SELECT statement. In all cases, theultimate class to instantiate for a given row is determined by a discriminatorcolumn or an expression that works against the base table. When a subclassis loaded only against a base table, resulting objects will have base attributespopulated at first; attributes that are local to the subclass will lazy loadwhen they are accessed. Alternatively, there are options which can changethe default behavior, allowing the query to include columns corresponding tomultiple tables/subclasses up front.

The base class in a joined inheritance hierarchy is configured withadditional arguments that will refer to the polymorphic discriminatorcolumn as well as the identifier for the base class:

  1. class Employee(Base):
  2.     __tablename__ = 'employee'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String(50))
  5.     type = Column(String(50))
  6.  
  7.     __mapper_args__ = {
  8.         'polymorphic_identity':'employee',
  9.         'polymorphic_on':type
  10.     }

Above, an additional column type is established to act as thediscriminator, configured as such using the mapper.polymorphic_onparameter. This column will store a value which indicates the type of objectrepresented within the row. The column may be of any datatype, though stringand integer are the most common.

While a polymorphic discriminator expression is not strictly necessary, it isrequired if polymorphic loading is desired. Establishing a simple column onthe base table is the easiest way to achieve this, however very sophisticatedinheritance mappings may even configure a SQL expression such as a CASEstatement as the polymorphic discriminator.

Note

Currently, only one discriminator column or SQL expression may beconfigured for the entire inheritance hierarchy, typically on the base-most class in the hierarchy. “Cascading” polymorphic discriminatorexpressions are not yet supported.

We next define Engineer and Manager subclasses of Employee.Each contains columns that represent the attributes unique to the subclassthey represent. Each table also must contain a primary key column (orcolumns), as well as a foreign key reference to the parent table:

  1. class Engineer(Employee):
  2.     __tablename__ = 'engineer'
  3.     id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
  4.     engineer_name = Column(String(30))
  5.  
  6.     __mapper_args__ = {
  7.         'polymorphic_identity':'engineer',
  8.     }
  9.  
  10. class Manager(Employee):
  11.     __tablename__ = 'manager'
  12.     id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
  13.     manager_name = Column(String(30))
  14.  
  15.     __mapper_args__ = {
  16.         'polymorphic_identity':'manager',
  17.     }

It is most common that the foreign key constraint is established on the samecolumn or columns as the primary key itself, however this is not required; acolumn distinct from the primary key may also be made to refer to the parentvia foreign key. The way that a JOIN is constructed from the base table tosubclasses is also directly customizable, however this is rarely necessary.

Joined inheritance primary keys

One natural effect of the joined table inheritance configuration is thatthe identity of any mapped object can be determined entirely from rows inthe base table alone. This has obvious advantages, so SQLAlchemy alwaysconsiders the primary key columns of a joined inheritance class to be thoseof the base table only. In other words, the id columns of both theengineer and manager tables are not used to locate Engineer orManager objects - only the value in employee.id is considered.engineer.id and manager.id are still of course critical to theproper operation of the pattern overall as they are used to locate thejoined row, once the parent row has been determined within a statement.

With the joined inheritance mapping complete, querying against Employeewill return a combination of Employee, Engineer and Managerobjects. Newly saved Engineer, Manager, and Employee objects willautomatically populate the employee.type column with the correct“discriminator” value in this case "engineer","manager", or "employee", as appropriate.

Relationships with Joined Inheritance

Relationships are fully supported with joined table inheritance. Therelationship involving a joined-inheritance class should target the classin the hierarchy that also corresponds to the foreign key constraint;below, as the employee table has a foreign key constraint back tothe company table, the relationships are set up between Companyand Employee:

  1. class Company(Base):
  2.     __tablename__ = 'company'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String(50))
  5.     employees = relationship("Employee", back_populates="company")
  6.  
  7. class Employee(Base):
  8.     __tablename__ = 'employee'
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String(50))
  11.     type = Column(String(50))
  12.     company_id = Column(ForeignKey('company.id'))
  13.     company = relationship("Company", back_populates="employees")
  14.  
  15.     __mapper_args__ = {
  16.         'polymorphic_identity':'employee',
  17.         'polymorphic_on':type
  18.     }
  19.  
  20. class Manager(Employee):
  21.     # ...
  22.  
  23. class Engineer(Employee):
  24.     # ...

If the foreign key constraint is on a table corresponding to a subclass,the relationship should target that subclass instead. In the examplebelow, there is a foreignkey constraint from manager to company, so the relationships areestablished between the Manager and Company classes:

  1. class Company(Base):
  2.     __tablename__ = 'company'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String(50))
  5.     managers = relationship("Manager", back_populates="company")
  6.  
  7. class Employee(Base):
  8.     __tablename__ = 'employee'
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String(50))
  11.     type = Column(String(50))
  12.  
  13.     __mapper_args__ = {
  14.         'polymorphic_identity':'employee',
  15.         'polymorphic_on':type
  16.     }
  17.  
  18. class Manager(Employee):
  19.     __tablename__ = 'manager'
  20.     id = Column(Integer, ForeignKey('employee.id'), primary_key=True)
  21.     manager_name = Column(String(30))
  22.  
  23.     company_id = Column(ForeignKey('company.id'))
  24.     company = relationship("Company", back_populates="managers")
  25.  
  26.     __mapper_args__ = {
  27.         'polymorphic_identity':'manager',
  28.     }
  29.  
  30. class Engineer(Employee):
  31.     # ...

Above, the Manager class will have a Manager.company attribute;Company will have a Company.managers attribute that alwaysloads against a join of the employee and manager tables together.

Loading Joined Inheritance Mappings

See the sections Loading Inheritance Hierarchies andLoading objects with joined table inheritance for background on inheritanceloading techniques, including configuration of tablesto be queried both at mapper configuration time as well as query time.

Single Table Inheritance

Single table inheritance represents all attributes of all subclasseswithin a single table. A particular subclass that has attributes uniqueto that class will persist them within columns in the table that are otherwiseNULL if the row refers to a different kind of object.

Querying for a particular subclassin the hierarchy will render as a SELECT against the base table, whichwill include a WHERE clause that limits rows to those with a particularvalue or values present in the discriminator column or expression.

Single table inheritance has the advantage of simplicity compared tojoined table inheritance; queries are much more efficient as only one tableneeds to be involved in order to load objects of every represented class.

Single-table inheritance configuration looks much like joined-tableinheritance, except only the base class specifies tablename. Adiscriminator column is also required on the base table so that classes can bedifferentiated from each other.

Even though subclasses share the base table for all of their attributes,when using Declarative, Column objects may still be specified onsubclasses, indicating that the column is to be mapped only to that subclass;the Column will be applied to the same base Table object:

  1. class Employee(Base):
  2.     __tablename__ = 'employee'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String(50))
  5.     type = Column(String(20))
  6.  
  7.     __mapper_args__ = {
  8.         'polymorphic_on':type,
  9.         'polymorphic_identity':'employee'
  10.     }
  11.  
  12. class Manager(Employee):
  13.     manager_data = Column(String(50))
  14.  
  15.     __mapper_args__ = {
  16.         'polymorphic_identity':'manager'
  17.     }
  18.  
  19. class Engineer(Employee):
  20.     engineer_info = Column(String(50))
  21.  
  22.     __mapper_args__ = {
  23.         'polymorphic_identity':'engineer'
  24.     }

Note that the mappers for the derived classes Manager and Engineer omit thetablename, indicating they do not have a mapped table oftheir own.

Relationships with Single Table Inheritance

Relationships are fully supported with single table inheritance. Configurationis done in the same manner as that of joined inheritance; a foreign keyattribute should be on the same class that’s the “foreign” side of therelationship:

  1. class Company(Base):
  2.     __tablename__ = 'company'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String(50))
  5.     employees = relationship("Employee", back_populates="company")
  6.  
  7. class Employee(Base):
  8.     __tablename__ = 'employee'
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String(50))
  11.     type = Column(String(50))
  12.     company_id = Column(ForeignKey('company.id'))
  13.     company = relationship("Company", back_populates="employees")
  14.  
  15.     __mapper_args__ = {
  16.         'polymorphic_identity':'employee',
  17.         'polymorphic_on':type
  18.     }
  19.  
  20.  
  21. class Manager(Employee):
  22.     manager_data = Column(String(50))
  23.  
  24.     __mapper_args__ = {
  25.         'polymorphic_identity':'manager'
  26.     }
  27.  
  28. class Engineer(Employee):
  29.     engineer_info = Column(String(50))
  30.  
  31.     __mapper_args__ = {
  32.         'polymorphic_identity':'engineer'
  33.     }

Also, like the case of joined inheritance, we can create relationshipsthat involve a specific subclass. When queried, the SELECT statement willinclude a WHERE clause that limits the class selection to that subclassor subclasses:

  1. class Company(Base):
  2.     __tablename__ = 'company'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String(50))
  5.     managers = relationship("Manager", back_populates="company")
  6.  
  7. class Employee(Base):
  8.     __tablename__ = 'employee'
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String(50))
  11.     type = Column(String(50))
  12.  
  13.     __mapper_args__ = {
  14.         'polymorphic_identity':'employee',
  15.         'polymorphic_on':type
  16.     }
  17.  
  18.  
  19. class Manager(Employee):
  20.     manager_name = Column(String(30))
  21.  
  22.     company_id = Column(ForeignKey('company.id'))
  23.     company = relationship("Company", back_populates="managers")
  24.  
  25.     __mapper_args__ = {
  26.         'polymorphic_identity':'manager',
  27.     }
  28.  
  29.  
  30. class Engineer(Employee):
  31.     engineer_info = Column(String(50))
  32.  
  33.     __mapper_args__ = {
  34.         'polymorphic_identity':'engineer'
  35.     }

Above, the Manager class will have a Manager.company attribute;Company will have a Company.managers attribute that alwaysloads against the employee with an additional WHERE clause thatlimits rows to those with type = 'manager'.

Loading Single Inheritance Mappings

The loading techniques for single-table inheritance are mostly identical tothose used for joined-table inheritance, and a high degree of abstraction isprovided between these two mapping types such that it is easy to switch betweenthem as well as to intermix them in a single hierarchy (just omittablename from whichever subclasses are to be single-inheriting). Seethe sections Loading Inheritance Hierarchies andLoading objects with single table inheritance for documentation on inheritance loadingtechniques, including configuration of classes to be queried both at mapperconfiguration time as well as query time.

Concrete Table Inheritance

Concrete inheritance maps each subclass to its own distinct table, eachof which contains all columns necessary to produce an instance of that class.A concrete inheritance configuration by default queries non-polymorphically;a query for a particular class will only query that class’ tableand only return instances of that class. Polymorphic loading of concreteclasses is enabled by configuring within the mappera special SELECT that typically is produced as a UNION of all the tables.

Warning

Concrete table inheritance is much more complicated than joinedor single table inheritance, and is much more limited in functionalityespecially pertaining to using it with relationships, eager loading,and polymorphic loading. When used polymorphically it producesvery large queries with UNIONS that won’t perform as well as simplejoins. It is strongly advised that if flexibility in relationship loadingand polymorphic loading is required, that joined or single table inheritancebe used if at all possible. If polymorphic loading isn’t required, thenplain non-inheriting mappings can be used if each class refers to itsown table completely.

Whereas joined and single table inheritance are fluent in “polymorphic”loading, it is a more awkward affair in concrete inheritance. For thisreason, concrete inheritance is more appropriate when polymorphic loadingis not required. Establishing relationships that involve concrete inheritanceclasses is also more awkward.

To establish a class as using concrete inheritance, add themapper.concrete parameter within the mapper_args.This indicates to Declarative as well as the mapping that the superclasstable should not be considered as part of the mapping:

  1. class Employee(Base):
  2.     __tablename__ = 'employee'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     name = Column(String(50))
  6.  
  7. class Manager(Employee):
  8.     __tablename__ = 'manager'
  9.  
  10.     id = Column(Integer, primary_key=True)
  11.     name = Column(String(50))
  12.     manager_data = Column(String(50))
  13.  
  14.     __mapper_args__ = {
  15.         'concrete': True
  16.     }
  17.  
  18. class Engineer(Employee):
  19.     __tablename__ = 'engineer'
  20.  
  21.     id = Column(Integer, primary_key=True)
  22.     name = Column(String(50))
  23.     engineer_info = Column(String(50))
  24.  
  25.     __mapper_args__ = {
  26.         'concrete': True
  27.     }

Two critical points should be noted:

  • We must define all columns explicitly on each subclass, even those ofthe same name. A column such asEmployee.name here is not copied out to the tables mappedby Manager or Engineer for us.

  • while the Engineer and Manager classes aremapped in an inheritance relationship with Employee, they still do notinclude polymorphic loading. Meaning, if we query for Employeeobjects, the manager and engineer tables are not queried at all.

Concrete Polymorphic Loading Configuration

Polymorphic loading with concrete inheritance requires that a specializedSELECT is configured against each base class that should have polymorphicloading. This SELECT needs to be capable of accessing all themapped tables individually, and is typically a UNION statement that isconstructed using a SQLAlchemy helper polymorphic_union().

As discussed in Loading Inheritance Hierarchies, mapper inheritanceconfigurations of any type can be configured to load from a special selectableby default using the mapper.with_polymorphic argument. Currentpublic API requires that this argument is set on a Mapper whenit is first constructed.

However, in the case of Declarative, both the mapper and the Tablethat is mapped are created at once, the moment the mapped class is defined.This means that the mapper.with_polymorphic argument cannotbe provided yet, since the Table objects that correspond to thesubclasses haven’t yet been defined.

There are a few strategies available to resolve this cycle, howeverDeclarative provides helper classes ConcreteBase andAbstractConcreteBase which handle this issue behind the scenes.

Using ConcreteBase, we can set up our concrete mapping inalmost the same way as we do other forms of inheritance mappings:

  1. from sqlalchemy.ext.declarative import ConcreteBase
  2.  
  3. class Employee(ConcreteBase, Base):
  4.     __tablename__ = 'employee'
  5.     id = Column(Integer, primary_key=True)
  6.     name = Column(String(50))
  7.  
  8.     __mapper_args__ = {
  9.         'polymorphic_identity': 'employee',
  10.         'concrete': True
  11.     }
  12.  
  13. class Manager(Employee):
  14.     __tablename__ = 'manager'
  15.     id = Column(Integer, primary_key=True)
  16.     name = Column(String(50))
  17.     manager_data = Column(String(40))
  18.  
  19.     __mapper_args__ = {
  20.         'polymorphic_identity': 'manager',
  21.         'concrete': True
  22.     }
  23.  
  24. class Engineer(Employee):
  25.     __tablename__ = 'engineer'
  26.     id = Column(Integer, primary_key=True)
  27.     name = Column(String(50))
  28.     engineer_info = Column(String(40))
  29.  
  30.     __mapper_args__ = {
  31.         'polymorphic_identity': 'engineer',
  32.         'concrete': True
  33.     }

Above, Declarative sets up the polymorphic selectable for theEmployee class at mapper “initialization” time; this is the late-configurationstep for mappers that resolves other dependent mappers. The ConcreteBasehelper uses thepolymorphic_union() function to create a UNION of all concrete-mappedtables after all the other classes are set up, and then configures this statementwith the already existing base-class mapper.

Upon select, the polymorphic union produces a query like this:

  1. session.query(Employee).all()
  3. SELECT
  4.     pjoin.id AS pjoin_id,
  5.     pjoin.name AS pjoin_name,
  6.     pjoin.type AS pjoin_type,
  7.     pjoin.manager_data AS pjoin_manager_data,
  8.     pjoin.engineer_info AS pjoin_engineer_info
  9. FROM (
  10.     SELECT
  11.         employee.id AS id,
  12.         employee.name AS name,
  13.         CAST(NULL AS VARCHAR(50)) AS manager_data,
  14.         CAST(NULL AS VARCHAR(50)) AS engineer_info,
  15.         'employee' AS type
  16.     FROM employee
  17.     UNION ALL
  18.     SELECT
  19.         manager.id AS id,
  20.         manager.name AS name,
  21.         manager.manager_data AS manager_data,
  22.         CAST(NULL AS VARCHAR(50)) AS engineer_info,
  23.         'manager' AS type
  24.     FROM manager
  25.     UNION ALL
  26.     SELECT
  27.         engineer.id AS id,
  28.         engineer.name AS name,
  29.         CAST(NULL AS VARCHAR(50)) AS manager_data,
  30.         engineer.engineer_info AS engineer_info,
  31.         'engineer' AS type
  32.     FROM engineer
  33. ) AS pjoin

The above UNION query needs to manufacture “NULL” columns for each subtablein order to accommodate for those columns that aren’t members of thatparticular subclass.

Abstract Concrete Classes

The concrete mappings illustrated thus far show both the subclasses as wellas the base class mapped to individual tables. In the concrete inheritanceuse case, it is common that the base class is not represented within thedatabase, only the subclasses. In other words, the base class is“abstract”.

Normally, when one would like to map two different subclasses to individualtables, and leave the base class unmapped, this can be achieved very easily.When using Declarative, just declare thebase class with the abstract indicator:

  1. class Employee(Base):
  2.     __abstract__ = True
  3.  
  4. class Manager(Employee):
  5.     __tablename__ = 'manager'
  6.     id = Column(Integer, primary_key=True)
  7.     name = Column(String(50))
  8.     manager_data = Column(String(40))
  9.  
  10.     __mapper_args__ = {
  11.         'polymorphic_identity': 'manager',
  12.     }
  13.  
  14. class Engineer(Employee):
  15.     __tablename__ = 'engineer'
  16.     id = Column(Integer, primary_key=True)
  17.     name = Column(String(50))
  18.     engineer_info = Column(String(40))
  19.  
  20.     __mapper_args__ = {
  21.         'polymorphic_identity': 'engineer',
  22.     }

Above, we are not actually making use of SQLAlchemy’s inheritance mappingfacilities; we can load and persist instances of Manager and Engineernormally. The situation changes however when we need to query polymorphically,that is, we’d like to emit session.query(Employee) and get back a collectionof Manager and Engineer instances. This brings us back into thedomain of concrete inheritance, and we must build a special mapper againstEmployee in order to achieve this.

Mappers can always SELECT

In SQLAlchemy, a mapper for a class always has to refer to some“selectable”, which is normally a Table but may also refer to anyselect() object as well. While it may appear that a “single tableinheritance” mapper does not map to a table, these mappers in factimplicitly refer to the table that is mapped by a superclass.

To modify our concrete inheritance example to illustrate an “abstract” basethat is capable of polymorphic loading,we will have only an engineer and a manager table and no employeetable, however the Employee mapper will be mapped directly to the“polymorphic union”, rather than specifying it locally to themapper.with_polymorphic parameter.

To help with this, Declarative offers a variant of the ConcreteBaseclass called AbstractConcreteBase which achieves this automatically:

  1. from sqlalchemy.ext.declarative import AbstractConcreteBase
  2.  
  3. class Employee(AbstractConcreteBase, Base):
  4.     pass
  5.  
  6. class Manager(Employee):
  7.     __tablename__ = 'manager'
  8.     id = Column(Integer, primary_key=True)
  9.     name = Column(String(50))
  10.     manager_data = Column(String(40))
  11.  
  12.     __mapper_args__ = {
  13.         'polymorphic_identity': 'manager',
  14.         'concrete': True
  15.     }
  16.  
  17. class Engineer(Employee):
  18.     __tablename__ = 'engineer'
  19.     id = Column(Integer, primary_key=True)
  20.     name = Column(String(50))
  21.     engineer_info = Column(String(40))
  22.  
  23.     __mapper_args__ = {
  24.         'polymorphic_identity': 'engineer',
  25.         'concrete': True
  26.     }

The AbstractConcreteBase helper class has a more complex internalprocess than that of ConcreteBase, in that the entire mappingof the base class must be delayed until all the subclasses have been declared.With a mapping like the above, only instances of Manager and Engineermay be persisted; querying against the Employee class will always produceManager and Engineer objects.

See also

Concrete Table Inheritance - in the Declarative reference documentation

Classical and Semi-Classical Concrete Polymorphic Configuration

The Declarative configurations illustrated with ConcreteBaseand AbstractConcreteBase are equivalent to two other formsof configuration that make use of polymorphic_union() explicitly.These configurational forms make use of the Table object explicitlyso that the “polymorphic union” can be created first, then appliedto the mappings. These are illustrated here to clarify the roleof the polymorphic_union() function in terms of mapping.

A semi-classical mapping for example makes use of Declarative, butestablishes the Table objects separately:

  1. metadata = Base.metadata
  2.  
  3. employees_table = Table(
  4.     'employee', metadata,
  5.     Column('id', Integer, primary_key=True),
  6.     Column('name', String(50)),
  7. )
  8.  
  9. managers_table = Table(
  10.     'manager', metadata,
  11.     Column('id', Integer, primary_key=True),
  12.     Column('name', String(50)),
  13.     Column('manager_data', String(50)),
  14. )
  15.  
  16. engineers_table = Table(
  17.     'engineer', metadata,
  18.     Column('id', Integer, primary_key=True),
  19.     Column('name', String(50)),
  20.     Column('engineer_info', String(50)),
  21. )

Next, the UNION is produced using polymorphic_union():

  1. from sqlalchemy.orm import polymorphic_union
  2.  
  3. pjoin = polymorphic_union({
  4.     'employee': employees_table,
  5.     'manager': managers_table,
  6.     'engineer': engineers_table
  7. }, 'type', 'pjoin')

With the above Table objects, the mappings can be produced using “semi-classical” style,where we use Declarative in conjunction with the table argument;our polymorphic union above is passed via mapper_args tothe mapper.with_polymorphic parameter:

  1. class Employee(Base):
  2.     __table__ = employee_table
  3.     __mapper_args__ = {
  4.         'polymorphic_on': pjoin.c.type,
  5.         'with_polymorphic': ('*', pjoin),
  6.         'polymorphic_identity': 'employee'
  7.     }
  8.  
  9. class Engineer(Employee):
  10.     __table__ = engineer_table
  11.     __mapper_args__ = {
  12.         'polymorphic_identity': 'engineer',
  13.         'concrete': True}
  14.  
  15. class Manager(Employee):
  16.     __table__ = manager_table
  17.     __mapper_args__ = {
  18.         'polymorphic_identity': 'manager',
  19.         'concrete': True}

Alternatively, the same Table objects can be used infully “classical” style, without using Declarative at all.A constructor similar to that supplied by Declarative is illustrated:

  1. class Employee(object):
  2.     def __init__(self, **kw):
  3.         for k in kw:
  4.             setattr(self, k, kw[k])
  5.  
  6. class Manager(Employee):
  7.     pass
  8.  
  9. class Engineer(Employee):
  10.     pass
  11.  
  12. employee_mapper = mapper(Employee, pjoin,
  13.                                     with_polymorphic=('*', pjoin),
  14.                                     polymorphic_on=pjoin.c.type)
  15. manager_mapper = mapper(Manager, managers_table,
  16.                                     inherits=employee_mapper,
  17.                                     concrete=True,
  18.                                     polymorphic_identity='manager')
  19. engineer_mapper = mapper(Engineer, engineers_table,
  20.                                     inherits=employee_mapper,
  21.                                     concrete=True,
  22.                                     polymorphic_identity='engineer')

The “abstract” example can also be mapped using “semi-classical” or “classical”style. The difference is that instead of applying the “polymorphic union”to the mapper.with_polymorphic parameter, we apply it directlyas the mapped selectable on our basemost mapper. The semi-classicalmapping is illustrated below:

  1. from sqlalchemy.orm import polymorphic_union
  2.  
  3. pjoin = polymorphic_union({
  4.     'manager': managers_table,
  5.     'engineer': engineers_table
  6. }, 'type', 'pjoin')
  7.  
  8. class Employee(Base):
  9.     __table__ = pjoin
  10.     __mapper_args__ = {
  11.         'polymorphic_on': pjoin.c.type,
  12.         'with_polymorphic': '*',
  13.         'polymorphic_identity': 'employee'
  14.     }
  15.  
  16. class Engineer(Employee):
  17.     __table__ = engineer_table
  18.     __mapper_args__ = {
  19.         'polymorphic_identity': 'engineer',
  20.         'concrete': True}
  21.  
  22. class Manager(Employee):
  23.     __table__ = manager_table
  24.     __mapper_args__ = {
  25.         'polymorphic_identity': 'manager',
  26.         'concrete': True}

Above, we use polymorphic_union() in the same manner as before, exceptthat we omit the employee table.

See also

Classical Mappings - background information on “classical” mappings

Relationships with Concrete Inheritance

In a concrete inheritance scenario, mapping relationships is challengingsince the distinct classes do not share a table. If the relationshipsonly involve specific classes, such as a relationship between Company inour previous examples and Manager, special steps aren’t needed as theseare just two related tables.

However, if Company is to have a one-to-many relationshipto Employee, indicating that the collection may include bothEngineer and Manager objects, that implies that Employee musthave polymorphic loading capabilities and also that each table to be relatedmust have a foreign key back to the company table. An example ofsuch a configuration is as follows:

  1. from sqlalchemy.ext.declarative import ConcreteBase
  2.  
  3.  
  4. class Company(Base):
  5.     __tablename__ = 'company'
  6.     id = Column(Integer, primary_key=True)
  7.     name = Column(String(50))
  8.     employees = relationship("Employee")
  9.  
  10.  
  11. class Employee(ConcreteBase, Base):
  12.     __tablename__ = 'employee'
  13.     id = Column(Integer, primary_key=True)
  14.     name = Column(String(50))
  15.     company_id = Column(ForeignKey('company.id'))
  16.  
  17.     __mapper_args__ = {
  18.         'polymorphic_identity': 'employee',
  19.         'concrete': True
  20.     }
  21.  
  22.  
  23. class Manager(Employee):
  24.     __tablename__ = 'manager'
  25.     id = Column(Integer, primary_key=True)
  26.     name = Column(String(50))
  27.     manager_data = Column(String(40))
  28.     company_id = Column(ForeignKey('company.id'))
  29.  
  30.     __mapper_args__ = {
  31.         'polymorphic_identity': 'manager',
  32.         'concrete': True
  33.     }
  34.  
  35.  
  36. class Engineer(Employee):
  37.     __tablename__ = 'engineer'
  38.     id = Column(Integer, primary_key=True)
  39.     name = Column(String(50))
  40.     engineer_info = Column(String(40))
  41.     company_id = Column(ForeignKey('company.id'))
  42.  
  43.     __mapper_args__ = {
  44.         'polymorphic_identity': 'engineer',
  45.         'concrete': True
  46.     }

The next complexity with concrete inheritance and relationships involveswhen we’d like one or all of Employee, Manager and Engineer tothemselves refer back to Company. For this case, SQLAlchemy hasspecial behavior in that a relationship() placed on Employeewhich links to Company does not workagainst the Manager and Engineer classes, when exercised at theinstance level. Instead, a distinctrelationship() must be applied to each class. In order to achievebi-directional behavior in terms of three separate relationships whichserve as the opposite of Company.employees, therelationship.back_populates parameter is used betweeneach of the relationships:

  1. from sqlalchemy.ext.declarative import ConcreteBase
  2.  
  3.  
  4. class Company(Base):
  5.     __tablename__ = 'company'
  6.     id = Column(Integer, primary_key=True)
  7.     name = Column(String(50))
  8.     employees = relationship("Employee", back_populates="company")
  9.  
  10.  
  11. class Employee(ConcreteBase, Base):
  12.     __tablename__ = 'employee'
  13.     id = Column(Integer, primary_key=True)
  14.     name = Column(String(50))
  15.     company_id = Column(ForeignKey('company.id'))
  16.     company = relationship("Company", back_populates="employees")
  17.  
  18.     __mapper_args__ = {
  19.         'polymorphic_identity': 'employee',
  20.         'concrete': True
  21.     }
  22.  
  23.  
  24. class Manager(Employee):
  25.     __tablename__ = 'manager'
  26.     id = Column(Integer, primary_key=True)
  27.     name = Column(String(50))
  28.     manager_data = Column(String(40))
  29.     company_id = Column(ForeignKey('company.id'))
  30.     company = relationship("Company", back_populates="employees")
  31.  
  32.     __mapper_args__ = {
  33.         'polymorphic_identity': 'manager',
  34.         'concrete': True
  35.     }
  36.  
  37.  
  38. class Engineer(Employee):
  39.     __tablename__ = 'engineer'
  40.     id = Column(Integer, primary_key=True)
  41.     name = Column(String(50))
  42.     engineer_info = Column(String(40))
  43.     company_id = Column(ForeignKey('company.id'))
  44.     company = relationship("Company", back_populates="employees")
  45.  
  46.     __mapper_args__ = {
  47.         'polymorphic_identity': 'engineer',
  48.         'concrete': True
  49.     }

The above limitation is related to the current implementation, includingthat concrete inheriting classes do not share any of the attributes ofthe superclass and therefore need distinct relationships to be set up.

Loading Concrete Inheritance Mappings

The options for loading with concrete inheritance are limited; generally,if polymorphic loading is configured on the mapper using one of thedeclarative concrete mixins, it can’t be modified at query timein current SQLAlchemy versions. Normally, the orm.with_polymorphic()function would be able to override the style of loading used by concrete,however due to current limitations this is not yet supported.