<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>
Mapping Class Inheritance Hierarchies
— SQLAlchemy 0.6.8 Documentation</title>
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/docs.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.6.8',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html'
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/init.js"></script>
<link rel="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="copyright" title="Copyright" href="../copyright.html" />
<link rel="top" title="SQLAlchemy 0.6.8 Documentation" href="../index.html" />
<link rel="up" title="SQLAlchemy ORM" href="index.html" />
<link rel="next" title="Using the Session" href="session.html" />
<link rel="prev" title="Collection Configuration and Techniques" href="collections.html" />
</head>
<body>
<h1>SQLAlchemy 0.6.8 Documentation</h1>
<div id="search">
Search:
<form class="search" action="../search.html" method="get">
<input type="text" name="q" size="18" /> <input type="submit" value="Search" />
<input type="hidden" name="check_keywords" value="yes" />
<input type="hidden" name="area" value="default" />
</form>
</div>
<div class="versionheader">
Version: <span class="versionnum">0.6.8</span> Last Updated: 06/05/2011 13:10:26
</div>
<div class="clearboth"></div>
<div id="topnav">
<div id="pagecontrol">
<ul>
<li>Prev:
<a href="collections.html" title="previous chapter">Collection Configuration and Techniques</a>
</li>
<li>Next:
<a href="session.html" title="next chapter">Using the Session</a>
</li>
<li>
<a href="../contents.html">Table of Contents</a> |
<a href="../genindex.html">Index</a>
| <a href="../_sources/orm/inheritance.txt">view source
</li>
</ul>
</div>
<div id="navbanner">
<a class="totoc" href="../index.html">SQLAlchemy 0.6.8 Documentation</a>
» <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
»
Mapping Class Inheritance Hierarchies
<h2>
Mapping Class Inheritance Hierarchies
</h2>
<ul>
<li><a class="reference internal" href="#">Mapping Class Inheritance Hierarchies</a><ul>
<li><a class="reference internal" href="#joined-table-inheritance">Joined Table Inheritance</a><ul>
<li><a class="reference internal" href="#basic-control-of-which-tables-are-queried">Basic Control of Which Tables are Queried</a></li>
<li><a class="reference internal" href="#advanced-control-of-which-tables-are-queried">Advanced Control of Which Tables are Queried</a></li>
<li><a class="reference internal" href="#creating-joins-to-specific-subtypes">Creating Joins to Specific Subtypes</a></li>
</ul>
</li>
<li><a class="reference internal" href="#single-table-inheritance">Single Table Inheritance</a></li>
<li><a class="reference internal" href="#concrete-table-inheritance">Concrete Table Inheritance</a></li>
<li><a class="reference internal" href="#using-relationships-with-inheritance">Using Relationships with Inheritance</a><ul>
<li><a class="reference internal" href="#relationships-with-concrete-inheritance">Relationships with Concrete Inheritance</a></li>
</ul>
</li>
<li><a class="reference internal" href="#using-inheritance-with-declarative">Using Inheritance with Declarative</a></li>
</ul>
</li>
</ul>
</div>
<div class="clearboth"></div>
</div>
<div class="document">
<div class="body">
<div class="section" id="mapping-class-inheritance-hierarchies">
<h1>Mapping Class Inheritance Hierarchies<a class="headerlink" href="#mapping-class-inheritance-hierarchies" title="Permalink to this headline">¶</a></h1>
<p>SQLAlchemy supports three forms of inheritance: <em>single table inheritance</em>,
where several types of classes are stored in one table, <em>concrete table
inheritance</em>, where each type of class is stored in its own table, and <em>joined
table inheritance</em>, where the parent/child classes are stored in their own
tables that are joined together in a select. Whereas support for single and
joined table inheritance is strong, concrete table inheritance is a less
common scenario with some particular problems so is not quite as flexible.</p>
<p>When mappers are configured in an inheritance relationship, SQLAlchemy has the
ability to load elements “polymorphically”, meaning that a single query can
return objects of multiple types.</p>
<p>For the following sections, assume this class relationship:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Employee</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
<span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">__class__</span><span class="o">.</span><span class="n">__name__</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">name</span>
<span class="k">class</span> <span class="nc">Manager</span><span class="p">(</span><span class="n">Employee</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">manager_data</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
<span class="bp">self</span><span class="o">.</span><span class="n">manager_data</span> <span class="o">=</span> <span class="n">manager_data</span>
<span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">__class__</span><span class="o">.</span><span class="n">__name__</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">manager_data</span>
<span class="k">class</span> <span class="nc">Engineer</span><span class="p">(</span><span class="n">Employee</span><span class="p">):</span>
<span class="k">def</span> <span class="nf">__init__</span><span class="p">(</span><span class="bp">self</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">engineer_info</span><span class="p">):</span>
<span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
<span class="bp">self</span><span class="o">.</span><span class="n">engineer_info</span> <span class="o">=</span> <span class="n">engineer_info</span>
<span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
<span class="k">return</span> <span class="bp">self</span><span class="o">.</span><span class="n">__class__</span><span class="o">.</span><span class="n">__name__</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">" "</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">engineer_info</span></pre></div>
</div>
<div class="section" id="joined-table-inheritance">
<h2>Joined Table Inheritance<a class="headerlink" href="#joined-table-inheritance" title="Permalink to this headline">¶</a></h2>
<p>In joined table inheritance, each class along a particular classes’ list of
parents is represented by a unique table. The total set of attributes for a
particular instance is represented as a join along all tables in its
inheritance path. Here, we first define a table to represent the <tt class="docutils literal"><span class="pre">Employee</span></tt>
class. This table will contain a primary key column (or columns), and a column
for each attribute that’s represented by <tt class="docutils literal"><span class="pre">Employee</span></tt>. In this case it’s just
<tt class="docutils literal"><span class="pre">name</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">employees</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'type'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">30</span><span class="p">),</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="p">)</span></pre></div>
</div>
<p>The table also has a column called <tt class="docutils literal"><span class="pre">type</span></tt>. It is strongly advised in both
single- and joined- table inheritance scenarios that the root table contains a
column whose sole purpose is that of the <strong>discriminator</strong>; it stores a value
which indicates the type of object represented within the row. The column may
be of any desired datatype. While there are some “tricks” to work around the
requirement that there be a discriminator column, they are more complicated to
configure when one wishes to load polymorphically.</p>
<p>Next we define individual tables for each of <tt class="docutils literal"><span class="pre">Engineer</span></tt> and <tt class="docutils literal"><span class="pre">Manager</span></tt>,
which contain columns that represent the attributes unique to the subclass
they represent. Each table also must contain a primary key column (or
columns), and in most cases a foreign key reference to the parent table. It is
standard practice that the same column is used for both of these roles, and
that the column is also named the same as that of the parent table. However
this is optional in SQLAlchemy; separate columns may be used for primary key
and parent-relationship, the column may be named differently than that of the
parent, and even a custom join condition can be specified between parent and
child tables instead of using a foreign key:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">engineers</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'engineers'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'employees.employee_id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">managers</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'managers'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'employees.employee_id'</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span></pre></div>
</div>
<p>One natural effect of the joined table inheritance configuration is that the
identity of any mapped object can be determined entirely from the base table.
This has obvious advantages, so SQLAlchemy always considers the primary key
columns of a joined inheritance class to be those of the base table only,
unless otherwise manually configured. In other words, the <tt class="docutils literal"><span class="pre">employee_id</span></tt>
column of both the <tt class="docutils literal"><span class="pre">engineers</span></tt> and <tt class="docutils literal"><span class="pre">managers</span></tt> table is not used to locate
the <tt class="docutils literal"><span class="pre">Engineer</span></tt> or <tt class="docutils literal"><span class="pre">Manager</span></tt> object itself - only the value in
<tt class="docutils literal"><span class="pre">employees.employee_id</span></tt> is considered, and the primary key in this case is
non-composite. <tt class="docutils literal"><span class="pre">engineers.employee_id</span></tt> and <tt class="docutils literal"><span class="pre">managers.employee_id</span></tt> are
still of course critical to the proper operation of the pattern overall as
they are used to locate the joined row, once the parent row has been
determined, either through a distinct SELECT statement or all at once within a
JOIN.</p>
<p>We then configure mappers as usual, except we use some additional arguments to
indicate the inheritance relationship, the polymorphic discriminator column,
and the <strong>polymorphic identity</strong> of each class; this is the value that will be
stored in the polymorphic discriminator column.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees</span><span class="p">,</span> <span class="n">polymorphic_on</span><span class="o">=</span><span class="n">employees</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span></pre></div>
</div>
<p>And that’s it. Querying against <tt class="docutils literal"><span class="pre">Employee</span></tt> will return a combination of
<tt class="docutils literal"><span class="pre">Employee</span></tt>, <tt class="docutils literal"><span class="pre">Engineer</span></tt> and <tt class="docutils literal"><span class="pre">Manager</span></tt> objects. Newly saved <tt class="docutils literal"><span class="pre">Engineer</span></tt>,
<tt class="docutils literal"><span class="pre">Manager</span></tt>, and <tt class="docutils literal"><span class="pre">Employee</span></tt> objects will automatically populate the
<tt class="docutils literal"><span class="pre">employees.type</span></tt> column with <tt class="docutils literal"><span class="pre">engineer</span></tt>, <tt class="docutils literal"><span class="pre">manager</span></tt>, or <tt class="docutils literal"><span class="pre">employee</span></tt>, as
appropriate.</p>
<div class="section" id="basic-control-of-which-tables-are-queried">
<h3>Basic Control of Which Tables are Queried<a class="headerlink" href="#basic-control-of-which-tables-are-queried" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> method of
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> affects the specific subclass tables
which the Query selects from. Normally, a query such as this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>...selects only from the <tt class="docutils literal"><span class="pre">employees</span></tt> table. When loading fresh from the
database, our joined-table setup will query from the parent table only, using
SQL such as this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><div class='show_sql'>
SELECT employees.employee_id AS employees_employee_id, employees.name AS employees_name, employees.type AS employees_type
FROM employees
[]</div></pre></div>
</div>
<p>As attributes are requested from those <tt class="docutils literal"><span class="pre">Employee</span></tt> objects which are
represented in either the <tt class="docutils literal"><span class="pre">engineers</span></tt> or <tt class="docutils literal"><span class="pre">managers</span></tt> 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:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><div class='show_sql'>
SELECT managers.employee_id AS managers_employee_id, managers.manager_data AS managers_manager_data
FROM managers
WHERE ? = managers.employee_id
[5]
SELECT engineers.employee_id AS engineers_employee_id, engineers.engineer_info AS engineers_engineer_info
FROM engineers
WHERE ? = engineers.employee_id
[2]</div></pre></div>
</div>
<p>This behavior works well when issuing searches for small numbers of items,
such as when using <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.get" title="sqlalchemy.orm.query.Query.get"><tt class="xref py py-meth docutils literal"><span class="pre">Query.get()</span></tt></a>, since the full range of joined tables are not
pulled in to the SQL statement unnecessarily. But when querying a larger span
of rows which are known to be of many types, you may want to actively join to
some or all of the joined tables. The <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> feature of
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> and <tt class="docutils literal"><span class="pre">mapper</span></tt> provides this.</p>
<p>Telling our query to polymorphically load <tt class="docutils literal"><span class="pre">Engineer</span></tt> and <tt class="docutils literal"><span class="pre">Manager</span></tt>
objects:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">query</span> <span class="o">=</span> <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">])</span></pre></div>
</div>
<p>produces a query which joins the <tt class="docutils literal"><span class="pre">employees</span></tt> table to both the <tt class="docutils literal"><span class="pre">engineers</span></tt> and <tt class="docutils literal"><span class="pre">managers</span></tt> tables like the following:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">query</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='show_sql'>
SELECT employees.employee_id AS employees_employee_id, engineers.employee_id AS engineers_employee_id, managers.employee_id AS managers_employee_id, employees.name AS employees_name, employees.type AS employees_type, engineers.engineer_info AS engineers_engineer_info, managers.manager_data AS managers_manager_data
FROM employees LEFT OUTER JOIN engineers ON employees.employee_id = engineers.employee_id LEFT OUTER JOIN managers ON employees.employee_id = managers.employee_id
[]</div></pre></div>
</div>
<p><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> accepts a single class or
mapper, a list of classes/mappers, or the string <tt class="docutils literal"><span class="pre">'*'</span></tt> to indicate all
subclasses:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># join to the engineers table</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">(</span><span class="n">Engineer</span><span class="p">)</span>
<span class="c"># join to the engineers and managers tables</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">])</span>
<span class="c"># join to all subclass tables</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">(</span><span class="s">'*'</span><span class="p">)</span></pre></div>
</div>
<p>It also accepts a second argument <tt class="docutils literal"><span class="pre">selectable</span></tt> which replaces the automatic
join creation and instead selects directly from the selectable given. This
feature is normally used with “concrete” inheritance, described later, but can
be used with any kind of inheritance setup in the case that specialized SQL
should be used to load polymorphically:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># custom selectable</span>
<span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">],</span> <span class="n">employees</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">managers</span><span class="p">)</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">engineers</span><span class="p">))</span></pre></div>
</div>
<p><a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> is also needed
when you wish to add filter criteria that are specific to one or more
subclasses; it makes the subclasses’ columns available to the WHERE clause:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">([</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">])</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">or_</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'w'</span><span class="p">,</span> <span class="n">Manager</span><span class="o">.</span><span class="n">manager_data</span><span class="o">==</span><span class="s">'q'</span><span class="p">))</span></pre></div>
</div>
<p>Note that if you only need to load a single subtype, such as just the
<tt class="docutils literal"><span class="pre">Engineer</span></tt> objects, <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> is
not needed since you would query against the <tt class="docutils literal"><span class="pre">Engineer</span></tt> class directly.</p>
<p>The mapper also accepts <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> as a configurational argument so
that the joined-style load will be issued automatically. This argument may be
the string <tt class="docutils literal"><span class="pre">'*'</span></tt>, a list of classes, or a tuple consisting of either,
followed by a selectable.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees</span><span class="p">,</span> <span class="n">polymorphic_on</span><span class="o">=</span><span class="n">employees</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> \
<span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">,</span> <span class="n">with_polymorphic</span><span class="o">=</span><span class="s">'*'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">Employee</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span></pre></div>
</div>
<p>The above mapping will produce a query similar to that of
<tt class="docutils literal"><span class="pre">with_polymorphic('*')</span></tt> for every query of <tt class="docutils literal"><span class="pre">Employee</span></tt> objects.</p>
<p>Using <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-func docutils literal"><span class="pre">with_polymorphic()</span></tt></a> with
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> will override the mapper-level
<tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> setting.</p>
</div>
<div class="section" id="advanced-control-of-which-tables-are-queried">
<h3>Advanced Control of Which Tables are Queried<a class="headerlink" href="#advanced-control-of-which-tables-are-queried" title="Permalink to this headline">¶</a></h3>
<p>The <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.with_polymorphic" title="sqlalchemy.orm.query.Query.with_polymorphic"><tt class="xref py py-meth docutils literal"><span class="pre">Query.with_polymorphic()</span></tt></a> method and configuration works fine for
simplistic scenarios. However, it currently does not work with any
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> that selects against individual columns or against multiple
classes - it also has to be called at the outset of a query.</p>
<p>For total control of how <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> joins along inheritance relationships,
use the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Table" title="sqlalchemy.schema.Table"><tt class="xref py py-class docutils literal"><span class="pre">Table</span></tt></a> objects directly and construct joins manually. For example, to
query the name of employees with particular criterion:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="o">.</span><span class="n">name</span><span class="p">)</span><span class="o">.</span>\
<span class="n">outerjoin</span><span class="p">((</span><span class="n">engineer</span><span class="p">,</span> <span class="n">engineer</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">employee_id</span><span class="o">==</span><span class="n">Employee</span><span class="o">.</span><span class="n">employee_id</span><span class="p">))</span><span class="o">.</span>\
<span class="n">outerjoin</span><span class="p">((</span><span class="n">manager</span><span class="p">,</span> <span class="n">manager</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">employee_id</span><span class="o">==</span><span class="n">Employee</span><span class="o">.</span><span class="n">employee_id</span><span class="p">))</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">or_</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'w'</span><span class="p">,</span> <span class="n">Manager</span><span class="o">.</span><span class="n">manager_data</span><span class="o">==</span><span class="s">'q'</span><span class="p">))</span></pre></div>
</div>
<p>The base table, in this case the “employees” table, isn’t always necessary. A
SQL query is always more efficient with fewer joins. Here, if we wanted to
just load information specific to managers or engineers, we can instruct
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query" title="sqlalchemy.orm.query.Query"><tt class="xref py py-class docutils literal"><span class="pre">Query</span></tt></a> to use only those tables. The <tt class="docutils literal"><span class="pre">FROM</span></tt> clause is determined by
what’s specified in the <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.query" title="sqlalchemy.orm.session.Session.query"><tt class="xref py py-meth docutils literal"><span class="pre">Session.query()</span></tt></a>, <a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.filter" title="sqlalchemy.orm.query.Query.filter"><tt class="xref py py-meth docutils literal"><span class="pre">Query.filter()</span></tt></a>, or
<a class="reference internal" href="query.html#sqlalchemy.orm.query.Query.select_from" title="sqlalchemy.orm.query.Query.select_from"><tt class="xref py py-meth docutils literal"><span class="pre">Query.select_from()</span></tt></a> methods:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Manager</span><span class="o">.</span><span class="n">manager_data</span><span class="p">)</span><span class="o">.</span><span class="n">select_from</span><span class="p">(</span><span class="n">manager</span><span class="p">)</span>
<span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">engineer</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">engineer</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="n">manager</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">manager_data</span><span class="p">)</span></pre></div>
</div>
</div>
<div class="section" id="creating-joins-to-specific-subtypes">
<h3>Creating Joins to Specific Subtypes<a class="headerlink" href="#creating-joins-to-specific-subtypes" title="Permalink to this headline">¶</a></h3>
<p>The <tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt> method is a
helper which allows the construction of joins along
<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> paths while narrowing the criterion to
specific subclasses. Suppose the <tt class="docutils literal"><span class="pre">employees</span></tt> table represents a collection
of employees which are associated with a <tt class="docutils literal"><span class="pre">Company</span></tt> object. We’ll add a
<tt class="docutils literal"><span class="pre">company_id</span></tt> column to the <tt class="docutils literal"><span class="pre">employees</span></tt> table and a new table
<tt class="docutils literal"><span class="pre">companies</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">companies</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'companies'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">employees</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'type'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">30</span><span class="p">),</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'companies.company_id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="k">class</span> <span class="nc">Company</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Company</span><span class="p">,</span> <span class="n">companies</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'employees'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>When querying from <tt class="docutils literal"><span class="pre">Company</span></tt> onto the <tt class="docutils literal"><span class="pre">Employee</span></tt> relationship, the
<tt class="docutils literal"><span class="pre">join()</span></tt> method as well as the <tt class="docutils literal"><span class="pre">any()</span></tt> and <tt class="docutils literal"><span class="pre">has()</span></tt> operators will create
a join from <tt class="docutils literal"><span class="pre">companies</span></tt> to <tt class="docutils literal"><span class="pre">employees</span></tt>, without including <tt class="docutils literal"><span class="pre">engineers</span></tt> or
<tt class="docutils literal"><span class="pre">managers</span></tt> in the mix. If we wish to have criterion which is specifically
against the <tt class="docutils literal"><span class="pre">Engineer</span></tt> class, we can tell those methods to join or subquery
against the joined table representing the subclass using the
<tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt> operator:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="o">.</span><span class="n">of_type</span><span class="p">(</span><span class="n">Engineer</span><span class="p">))</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">)</span></pre></div>
</div>
<p>A longhand version of this would involve spelling out the full target
selectable within a 2-tuple:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">((</span><span class="n">employees</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">engineers</span><span class="p">),</span> <span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="p">))</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">)</span></pre></div>
</div>
<p>Currently, <tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt> accepts a
single class argument. It may be expanded later on to accept multiple classes.
For now, to join to any group of subclasses, the longhand notation allows this
flexibility:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">join</span><span class="p">((</span><span class="n">employees</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">engineers</span><span class="p">)</span><span class="o">.</span><span class="n">outerjoin</span><span class="p">(</span><span class="n">managers</span><span class="p">),</span> <span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="p">))</span><span class="o">.</span>\
<span class="nb">filter</span><span class="p">(</span><span class="n">or_</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">,</span> <span class="n">Manager</span><span class="o">.</span><span class="n">manager_data</span><span class="o">==</span><span class="s">'somedata'</span><span class="p">))</span></pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">any()</span></tt> and <tt class="docutils literal"><span class="pre">has()</span></tt> operators also can be used with
<tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt> when the embedded
criterion is in terms of a subclass:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="o">.</span><span class="n">of_type</span><span class="p">(</span><span class="n">Engineer</span><span class="p">)</span><span class="o">.</span><span class="n">any</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">))</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>Note that the <tt class="docutils literal"><span class="pre">any()</span></tt> and <tt class="docutils literal"><span class="pre">has()</span></tt> are both shorthand for a correlated
EXISTS query. To build one by hand looks like:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Company</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span>
<span class="n">exists</span><span class="p">([</span><span class="mi">1</span><span class="p">],</span>
<span class="n">and_</span><span class="p">(</span><span class="n">Engineer</span><span class="o">.</span><span class="n">engineer_info</span><span class="o">==</span><span class="s">'someinfo'</span><span class="p">,</span> <span class="n">employees</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">company_id</span><span class="o">==</span><span class="n">companies</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">company_id</span><span class="p">),</span>
<span class="n">from_obj</span><span class="o">=</span><span class="n">employees</span><span class="o">.</span><span class="n">join</span><span class="p">(</span><span class="n">engineers</span><span class="p">)</span>
<span class="p">)</span>
<span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
</div>
<p>The EXISTS subquery above selects from the join of <tt class="docutils literal"><span class="pre">employees</span></tt> to
<tt class="docutils literal"><span class="pre">engineers</span></tt>, and also specifies criterion which correlates the EXISTS
subselect back to the parent <tt class="docutils literal"><span class="pre">companies</span></tt> table.</p>
</div>
</div>
<div class="section" id="single-table-inheritance">
<h2>Single Table Inheritance<a class="headerlink" href="#single-table-inheritance" title="Permalink to this headline">¶</a></h2>
<p>Single table inheritance is where the attributes of the base class as well as
all subclasses are represented within a single table. A column is present in
the table for every attribute mapped to the base class and all subclasses; the
columns which correspond to a single subclass are nullable. This configuration
looks much like joined-table inheritance except there’s only one table. In
this case, a <tt class="docutils literal"><span class="pre">type</span></tt> column is required, as there would be no other way to
discriminate between classes. The table is specified in the base mapper only;
for the inheriting classes, leave their <tt class="docutils literal"><span class="pre">table</span></tt> parameter blank:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'type'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">20</span><span class="p">),</span> <span class="n">nullable</span><span class="o">=</span><span class="bp">False</span><span class="p">)</span>
<span class="p">)</span>
<span class="n">employee_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">,</span> \
<span class="n">polymorphic_on</span><span class="o">=</span><span class="n">employees_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">manager_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span>
<span class="n">engineer_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span></pre></div>
</div>
<p>Note that the mappers for the derived classes Manager and Engineer omit the
specification of their associated table, as it is inherited from the
employee_mapper. Omitting the table specification for derived mappers in
single-table inheritance is required.</p>
</div>
<div class="section" id="concrete-table-inheritance">
<span id="concrete-inheritance"></span><h2>Concrete Table Inheritance<a class="headerlink" href="#concrete-table-inheritance" title="Permalink to this headline">¶</a></h2>
<p>This form of inheritance maps each class to a distinct table, as below:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">managers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'managers'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span>
<span class="n">engineers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'engineers'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="p">)</span></pre></div>
</div>
<p>Notice in this case there is no <tt class="docutils literal"><span class="pre">type</span></tt> column. If polymorphic loading is not
required, there’s no advantage to using <tt class="docutils literal"><span class="pre">inherits</span></tt> here; you just define a
separate mapper for each class.</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers_table</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers_table</span><span class="p">)</span></pre></div>
</div>
<p>To load polymorphically, the <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> argument is required, along
with a selectable indicating how rows should be loaded. In this case we must
construct a UNION of all three tables. SQLAlchemy includes a helper function
to create these called <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.util.polymorphic_union" title="sqlalchemy.orm.util.polymorphic_union"><tt class="xref py py-func docutils literal"><span class="pre">polymorphic_union()</span></tt></a>, which
will map all the different columns into a structure of selects with the same
numbers and names of columns, and also generate a virtual <tt class="docutils literal"><span class="pre">type</span></tt> column for
each subselect:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">pjoin</span> <span class="o">=</span> <span class="n">polymorphic_union</span><span class="p">({</span>
<span class="s">'employee'</span><span class="p">:</span> <span class="n">employees_table</span><span class="p">,</span>
<span class="s">'manager'</span><span class="p">:</span> <span class="n">managers_table</span><span class="p">,</span>
<span class="s">'engineer'</span><span class="p">:</span> <span class="n">engineers_table</span>
<span class="p">},</span> <span class="s">'type'</span><span class="p">,</span> <span class="s">'pjoin'</span><span class="p">)</span>
<span class="n">employee_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">,</span> <span class="n">with_polymorphic</span><span class="o">=</span><span class="p">(</span><span class="s">'*'</span><span class="p">,</span> <span class="n">pjoin</span><span class="p">),</span> \
<span class="n">polymorphic_on</span><span class="o">=</span><span class="n">pjoin</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">manager_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers_table</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> \
<span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span>
<span class="n">engineer_mapper</span> <span class="o">=</span> <span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers_table</span><span class="p">,</span> <span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span> \
<span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span></pre></div>
</div>
<p>Upon select, the polymorphic union produces a query like this:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span>
<div class='show_sql'>
SELECT pjoin.type AS pjoin_type, pjoin.manager_data AS pjoin_manager_data, pjoin.employee_id AS pjoin_employee_id,
pjoin.name AS pjoin_name, pjoin.engineer_info AS pjoin_engineer_info
FROM (
SELECT employees.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, employees.name AS name,
CAST(NULL AS VARCHAR(50)) AS engineer_info, 'employee' AS type
FROM employees
UNION ALL
SELECT managers.employee_id AS employee_id, managers.manager_data AS manager_data, managers.name AS name,
CAST(NULL AS VARCHAR(50)) AS engineer_info, 'manager' AS type
FROM managers
UNION ALL
SELECT engineers.employee_id AS employee_id, CAST(NULL AS VARCHAR(50)) AS manager_data, engineers.name AS name,
engineers.engineer_info AS engineer_info, 'engineer' AS type
FROM engineers
) AS pjoin
[]</div></pre></div>
</div>
<p>For a recipe that sets up concrete inheritance using declarative, see the <a class="reference external" href="http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DeclarativeAbstractConcreteBase">DeclarativeAbstractConcreteBase</a> recipe on the wiki.</p>
</div>
<div class="section" id="using-relationships-with-inheritance">
<h2>Using Relationships with Inheritance<a class="headerlink" href="#using-relationships-with-inheritance" title="Permalink to this headline">¶</a></h2>
<p>Both joined-table and single table inheritance scenarios produce mappings
which are usable in <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> functions; that is,
it’s possible to map a parent object to a child object which is polymorphic.
Similarly, inheriting mappers can have <a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
objects of their own at any level, which are inherited to each child class.
The only requirement for relationships is that there is a table relationship
between parent and child. An example is the following modification to the
joined table inheritance example, which sets a bi-directional relationship
between <tt class="docutils literal"><span class="pre">Employee</span></tt> and <tt class="docutils literal"><span class="pre">Company</span></tt>:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'companies.company_id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">companies</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'companies'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)))</span>
<span class="k">class</span> <span class="nc">Company</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
<span class="k">pass</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Company</span><span class="p">,</span> <span class="n">companies</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'employees'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">'company'</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<div class="section" id="relationships-with-concrete-inheritance">
<h3>Relationships with Concrete Inheritance<a class="headerlink" href="#relationships-with-concrete-inheritance" title="Permalink to this headline">¶</a></h3>
<p>In a concrete inheritance scenario, mapping relationships is more challenging
since the distinct classes do not share a table. In this case, you <em>can</em>
establish a relationship from parent to child if a join condition can be
constructed from parent to child, if each child table contains a foreign key
to the parent:</p>
<div class="highlight-python+sql"><div class="highlight"><pre><span class="n">companies</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'companies'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)))</span>
<span class="n">employees_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'employees'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'companies.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">managers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'managers'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'manager_data'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'companies.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">engineers_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">'engineers'</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'employee_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'name'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'engineer_info'</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
<span class="n">Column</span><span class="p">(</span><span class="s">'company_id'</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">(</span><span class="s">'companies.id'</span><span class="p">))</span>
<span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Employee</span><span class="p">,</span> <span class="n">employees_table</span><span class="p">,</span>
<span class="n">with_polymorphic</span><span class="o">=</span><span class="p">(</span><span class="s">'*'</span><span class="p">,</span> <span class="n">pjoin</span><span class="p">),</span>
<span class="n">polymorphic_on</span><span class="o">=</span><span class="n">pjoin</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span>
<span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'employee'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Manager</span><span class="p">,</span> <span class="n">managers_table</span><span class="p">,</span>
<span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span>
<span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span>
<span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'manager'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">engineers_table</span><span class="p">,</span>
<span class="n">inherits</span><span class="o">=</span><span class="n">employee_mapper</span><span class="p">,</span>
<span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span>
<span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'engineer'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">Company</span><span class="p">,</span> <span class="n">companies</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'employees'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span>
<span class="p">})</span></pre></div>
</div>
<p>The big limitation with concrete table inheritance is that
<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> objects placed on each concrete mapper do
<strong>not</strong> propagate to child mappers. If you want to have the same
<a class="reference internal" href="relationships.html#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> objects set up on all concrete mappers,
they must be configured manually on each. To configure back references in such
a configuration the <tt class="docutils literal"><span class="pre">back_populates</span></tt> keyword may be used instead of
<tt class="docutils literal"><span class="pre">backref</span></tt>, such as below where both <tt class="docutils literal"><span class="pre">A(object)</span></tt> and <tt class="docutils literal"><span class="pre">B(A)</span></tt>
bidirectionally reference <tt class="docutils literal"><span class="pre">C</span></tt>:</p>
<div class="highlight-python"><div class="highlight"><pre><span class="n">ajoin</span> <span class="o">=</span> <span class="n">polymorphic_union</span><span class="p">({</span>
<span class="s">'a'</span><span class="p">:</span><span class="n">a_table</span><span class="p">,</span>
<span class="s">'b'</span><span class="p">:</span><span class="n">b_table</span>
<span class="p">},</span> <span class="s">'type'</span><span class="p">,</span> <span class="s">'ajoin'</span><span class="p">)</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">a_table</span><span class="p">,</span> <span class="n">with_polymorphic</span><span class="o">=</span><span class="p">(</span><span class="s">'*'</span><span class="p">,</span> <span class="n">ajoin</span><span class="p">),</span>
<span class="n">polymorphic_on</span><span class="o">=</span><span class="n">ajoin</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">type</span><span class="p">,</span> <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'a'</span><span class="p">,</span>
<span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'some_c'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">C</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">'many_a'</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">B</span><span class="p">,</span> <span class="n">b_table</span><span class="p">,</span><span class="n">inherits</span><span class="o">=</span><span class="n">A</span><span class="p">,</span> <span class="n">concrete</span><span class="o">=</span><span class="bp">True</span><span class="p">,</span>
<span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">'b'</span><span class="p">,</span>
<span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'some_c'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">C</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">'many_a'</span><span class="p">)</span>
<span class="p">})</span>
<span class="n">mapper</span><span class="p">(</span><span class="n">C</span><span class="p">,</span> <span class="n">c_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
<span class="s">'many_a'</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="n">A</span><span class="p">,</span> <span class="n">collection_class</span><span class="o">=</span><span class="nb">set</span><span class="p">,</span> <span class="n">back_populates</span><span class="o">=</span><span class="s">'some_c'</span><span class="p">),</span>
<span class="p">})</span></pre></div>
</div>
</div>
</div>
<div class="section" id="using-inheritance-with-declarative">
<h2>Using Inheritance with Declarative<a class="headerlink" href="#using-inheritance-with-declarative" title="Permalink to this headline">¶</a></h2>
<p>Declarative makes inheritance configuration more intuitive. See the docs at <a class="reference internal" href="extensions/declarative.html#declarative-inheritance"><em>Inheritance Configuration</em></a>.</p>
</div>
</div>
</div>
</div>
<div class="bottomnav">
Previous:
<a href="collections.html" title="previous chapter">Collection Configuration and Techniques</a>
Next:
<a href="session.html" title="next chapter">Using the Session</a>
<div class="doc_copyright">
© <a href="../copyright.html">Copyright</a> 2007-2011, the SQLAlchemy authors and contributors.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.0.7.
</div>
</div>
</body>
</html>
