<!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>