/SQLAlchemy-0.7.8/doc/orm/inheritance.html
# · HTML · 752 lines · 675 code · 77 blank · 0 comment · 0 complexity · 8b05ee4e03b6544f02751f3d8ce6cf0f MD5 · raw file
- <!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.7 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.7.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.7 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>
-
- <div id="docs-container">
- <div id="docs-header">
- <h1>SQLAlchemy 0.7 Documentation</h1>
- <div id="docs-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 id="docs-version-header">
- Release: <span class="version-num">0.7.8</span> | Release Date: June 16, 2012
- </div>
- </div>
- <div id="docs-top-navigation">
- <div id="docs-top-page-control" class="docs-navigation-links">
- <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="docs-navigation-banner">
- <a href="../index.html">SQLAlchemy 0.7 Documentation</a>
- ť <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
- ť
- Mapping Class Inheritance Hierarchies
-
- <h2>
-
- Mapping Class Inheritance Hierarchies
-
- </h2>
- </div>
- </div>
- <div id="docs-body-container">
- <div id="docs-sidebar">
- <h3><a href="../index.html">Table of Contents</a></h3>
- <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><ul>
- <li><a class="reference internal" href="#concrete-inheritance-with-declarative">Concrete Inheritance with Declarative</a></li>
- </ul>
- </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>
- <h4>Previous Topic</h4>
- <p>
- <a href="collections.html" title="previous chapter">Collection Configuration and Techniques</a>
- </p>
- <h4>Next Topic</h4>
- <p>
- <a href="session.html" title="next chapter">Using the Session</a>
- </p>
- <h4>Quick Search</h4>
- <p>
- <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>
- </p>
- </div>
- <div id="docs-body" class="withsidebar" >
-
- <div class="section" id="mapping-class-inheritance-hierarchies">
- <span id="inheritance-toplevel"></span><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>
- <div class="admonition note">
- <p class="first admonition-title">Note</p>
- <p class="last">This section currently uses classical mappings to illustrate inheritance
- configurations, and will soon be updated to standardize on Declarative.
- Until then, please refer to <a class="reference internal" href="extensions/declarative.html#declarative-inheritance"><em>Inheritance Configuration</em></a> for information on
- how common inheritance mappings are constructed declaratively.</p>
- </div>
- <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="p">(</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="p">)</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="p">(</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>
- <span class="p">)</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">
- <span id="with-polymorphic"></span><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="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>
- <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="nb">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 <a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> 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
- <a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> 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="nb">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="nb">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, <a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> 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="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="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>
- <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
- <a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.PropComparator.of_type" title="sqlalchemy.orm.interfaces.PropComparator.of_type"><tt class="xref py py-func docutils literal"><span class="pre">of_type()</span></tt></a> 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="nb">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="nb">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="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>
- <div class="section" id="concrete-inheritance-with-declarative">
- <h3>Concrete Inheritance with Declarative<a class="headerlink" href="#concrete-inheritance-with-declarative" title="Permalink to this headline">ś</a></h3>
- <p class="versionadded">
- <span class="versionmodified">New in version 0.7.3: </span>The <a class="reference internal" href="extensions/declarative.html"><em>Declarative</em></a> module includes helpers for concrete
- inheritance. See <a class="reference internal" href="extensions/declarative.html#declarative-concrete-helpers"><em>Using the Concrete Helpers</em></a> for more information.</p>
- </div>
- </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 id="docs-bottom-navigation" class="docs-navigation-links">
- 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 id="docs-copyright">
- © <a href="../copyright.html">Copyright</a> 2007-2012, the SQLAlchemy authors and contributors.
- Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.1.3.
- </div>
- </div>
- </div>
-
- </body>
- </html>