PageRenderTime 396ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 0ms

/SQLAlchemy-0.7.8/doc/orm/inheritance.html

#
HTML | 752 lines | 675 code | 77 blank | 0 comment | 0 complexity | 8b05ee4e03b6544f02751f3d8ce6cf0f MD5 | raw file

Large files files are truncated, but you can click here to view the full file

  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  2. "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  3. <html>
  4. <head>
  5. <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  6. <title>
  7. Mapping Class Inheritance Hierarchies
  8. &mdash;
  9. SQLAlchemy 0.7 Documentation
  10. </title>
  11. <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
  12. <link rel="stylesheet" href="../_static/docs.css" type="text/css" />
  13. <script type="text/javascript">
  14. var DOCUMENTATION_OPTIONS = {
  15. URL_ROOT: '../',
  16. VERSION: '0.7.8',
  17. COLLAPSE_MODINDEX: false,
  18. FILE_SUFFIX: '.html'
  19. };
  20. </script>
  21. <script type="text/javascript" src="../_static/jquery.js"></script>
  22. <script type="text/javascript" src="../_static/underscore.js"></script>
  23. <script type="text/javascript" src="../_static/doctools.js"></script>
  24. <script type="text/javascript" src="../_static/init.js"></script>
  25. <link rel="index" title="Index" href="../genindex.html" />
  26. <link rel="search" title="Search" href="../search.html" />
  27. <link rel="copyright" title="Copyright" href="../copyright.html" />
  28. <link rel="top" title="SQLAlchemy 0.7 Documentation" href="../index.html" />
  29. <link rel="up" title="SQLAlchemy ORM" href="index.html" />
  30. <link rel="next" title="Using the Session" href="session.html" />
  31. <link rel="prev" title="Collection Configuration and Techniques" href="collections.html" />
  32. </head>
  33. <body>
  34. <div id="docs-container">
  35. <div id="docs-header">
  36. <h1>SQLAlchemy 0.7 Documentation</h1>
  37. <div id="docs-search">
  38. Search:
  39. <form class="search" action="../search.html" method="get">
  40. <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
  41. <input type="hidden" name="check_keywords" value="yes" />
  42. <input type="hidden" name="area" value="default" />
  43. </form>
  44. </div>
  45. <div id="docs-version-header">
  46. Release: <span class="version-num">0.7.8</span> | Release Date: June 16, 2012
  47. </div>
  48. </div>
  49. <div id="docs-top-navigation">
  50. <div id="docs-top-page-control" class="docs-navigation-links">
  51. <ul>
  52. <li>Prev:
  53. <a href="collections.html" title="previous chapter">Collection Configuration and Techniques</a>
  54. </li>
  55. <li>Next:
  56. <a href="session.html" title="next chapter">Using the Session</a>
  57. </li>
  58. <li>
  59. <a href="../contents.html">Table of Contents</a> |
  60. <a href="../genindex.html">Index</a>
  61. | <a href="../_sources/orm/inheritance.txt">view source
  62. </li>
  63. </ul>
  64. </div>
  65. <div id="docs-navigation-banner">
  66. <a href="../index.html">SQLAlchemy 0.7 Documentation</a>
  67. ť <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
  68. ť
  69. Mapping Class Inheritance Hierarchies
  70. <h2>
  71. Mapping Class Inheritance Hierarchies
  72. </h2>
  73. </div>
  74. </div>
  75. <div id="docs-body-container">
  76. <div id="docs-sidebar">
  77. <h3><a href="../index.html">Table of Contents</a></h3>
  78. <ul>
  79. <li><a class="reference internal" href="#">Mapping Class Inheritance Hierarchies</a><ul>
  80. <li><a class="reference internal" href="#joined-table-inheritance">Joined Table Inheritance</a><ul>
  81. <li><a class="reference internal" href="#basic-control-of-which-tables-are-queried">Basic Control of Which Tables are Queried</a></li>
  82. <li><a class="reference internal" href="#advanced-control-of-which-tables-are-queried">Advanced Control of Which Tables are Queried</a></li>
  83. <li><a class="reference internal" href="#creating-joins-to-specific-subtypes">Creating Joins to Specific Subtypes</a></li>
  84. </ul>
  85. </li>
  86. <li><a class="reference internal" href="#single-table-inheritance">Single Table Inheritance</a></li>
  87. <li><a class="reference internal" href="#concrete-table-inheritance">Concrete Table Inheritance</a><ul>
  88. <li><a class="reference internal" href="#concrete-inheritance-with-declarative">Concrete Inheritance with Declarative</a></li>
  89. </ul>
  90. </li>
  91. <li><a class="reference internal" href="#using-relationships-with-inheritance">Using Relationships with Inheritance</a><ul>
  92. <li><a class="reference internal" href="#relationships-with-concrete-inheritance">Relationships with Concrete Inheritance</a></li>
  93. </ul>
  94. </li>
  95. <li><a class="reference internal" href="#using-inheritance-with-declarative">Using Inheritance with Declarative</a></li>
  96. </ul>
  97. </li>
  98. </ul>
  99. <h4>Previous Topic</h4>
  100. <p>
  101. <a href="collections.html" title="previous chapter">Collection Configuration and Techniques</a>
  102. </p>
  103. <h4>Next Topic</h4>
  104. <p>
  105. <a href="session.html" title="next chapter">Using the Session</a>
  106. </p>
  107. <h4>Quick Search</h4>
  108. <p>
  109. <form class="search" action="../search.html" method="get">
  110. <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
  111. <input type="hidden" name="check_keywords" value="yes" />
  112. <input type="hidden" name="area" value="default" />
  113. </form>
  114. </p>
  115. </div>
  116. <div id="docs-body" class="withsidebar" >
  117. <div class="section" id="mapping-class-inheritance-hierarchies">
  118. <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>
  119. <p>SQLAlchemy supports three forms of inheritance: <em>single table inheritance</em>,
  120. where several types of classes are stored in one table, <em>concrete table
  121. inheritance</em>, where each type of class is stored in its own table, and <em>joined
  122. table inheritance</em>, where the parent/child classes are stored in their own
  123. tables that are joined together in a select. Whereas support for single and
  124. joined table inheritance is strong, concrete table inheritance is a less
  125. common scenario with some particular problems so is not quite as flexible.</p>
  126. <p>When mappers are configured in an inheritance relationship, SQLAlchemy has the
  127. ability to load elements &#8220;polymorphically&#8221;, meaning that a single query can
  128. return objects of multiple types.</p>
  129. <div class="admonition note">
  130. <p class="first admonition-title">Note</p>
  131. <p class="last">This section currently uses classical mappings to illustrate inheritance
  132. configurations, and will soon be updated to standardize on Declarative.
  133. Until then, please refer to <a class="reference internal" href="extensions/declarative.html#declarative-inheritance"><em>Inheritance Configuration</em></a> for information on
  134. how common inheritance mappings are constructed declaratively.</p>
  135. </div>
  136. <p>For the following sections, assume this class relationship:</p>
  137. <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>
  138. <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>
  139. <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
  140. <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
  141. <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">&quot; &quot;</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">name</span>
  142. <span class="k">class</span> <span class="nc">Manager</span><span class="p">(</span><span class="n">Employee</span><span class="p">):</span>
  143. <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>
  144. <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
  145. <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>
  146. <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
  147. <span class="k">return</span> <span class="p">(</span>
  148. <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">&quot; &quot;</span> <span class="o">+</span>
  149. <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">&quot; &quot;</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">manager_data</span>
  150. <span class="p">)</span>
  151. <span class="k">class</span> <span class="nc">Engineer</span><span class="p">(</span><span class="n">Employee</span><span class="p">):</span>
  152. <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>
  153. <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">=</span> <span class="n">name</span>
  154. <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>
  155. <span class="k">def</span> <span class="nf">__repr__</span><span class="p">(</span><span class="bp">self</span><span class="p">):</span>
  156. <span class="k">return</span> <span class="p">(</span>
  157. <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">&quot; &quot;</span> <span class="o">+</span>
  158. <span class="bp">self</span><span class="o">.</span><span class="n">name</span> <span class="o">+</span> <span class="s">&quot; &quot;</span> <span class="o">+</span> <span class="bp">self</span><span class="o">.</span><span class="n">engineer_info</span>
  159. <span class="p">)</span></pre></div>
  160. </div>
  161. <div class="section" id="joined-table-inheritance">
  162. <h2>Joined Table Inheritance<a class="headerlink" href="#joined-table-inheritance" title="Permalink to this headline">ś</a></h2>
  163. <p>In joined table inheritance, each class along a particular classes&#8217; list of
  164. parents is represented by a unique table. The total set of attributes for a
  165. particular instance is represented as a join along all tables in its
  166. inheritance path. Here, we first define a table to represent the <tt class="docutils literal"><span class="pre">Employee</span></tt>
  167. class. This table will contain a primary key column (or columns), and a column
  168. for each attribute that&#8217;s represented by <tt class="docutils literal"><span class="pre">Employee</span></tt>. In this case it&#8217;s just
  169. <tt class="docutils literal"><span class="pre">name</span></tt>:</p>
  170. <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">&#39;employees&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
  171. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;employee_id&#39;</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>
  172. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;name&#39;</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
  173. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;type&#39;</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>
  174. <span class="p">)</span></pre></div>
  175. </div>
  176. <p>The table also has a column called <tt class="docutils literal"><span class="pre">type</span></tt>. It is strongly advised in both
  177. single- and joined- table inheritance scenarios that the root table contains a
  178. column whose sole purpose is that of the <strong>discriminator</strong>; it stores a value
  179. which indicates the type of object represented within the row. The column may
  180. be of any desired datatype. While there are some &#8220;tricks&#8221; to work around the
  181. requirement that there be a discriminator column, they are more complicated to
  182. configure when one wishes to load polymorphically.</p>
  183. <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>,
  184. which contain columns that represent the attributes unique to the subclass
  185. they represent. Each table also must contain a primary key column (or
  186. columns), and in most cases a foreign key reference to the parent table. It is
  187. standard practice that the same column is used for both of these roles, and
  188. that the column is also named the same as that of the parent table. However
  189. this is optional in SQLAlchemy; separate columns may be used for primary key
  190. and parent-relationship, the column may be named differently than that of the
  191. parent, and even a custom join condition can be specified between parent and
  192. child tables instead of using a foreign key:</p>
  193. <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">&#39;engineers&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
  194. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;employee_id&#39;</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span>
  195. <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;employees.employee_id&#39;</span><span class="p">),</span>
  196. <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
  197. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;engineer_info&#39;</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
  198. <span class="p">)</span>
  199. <span class="n">managers</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&#39;managers&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
  200. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;employee_id&#39;</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span>
  201. <span class="n">ForeignKey</span><span class="p">(</span><span class="s">&#39;employees.employee_id&#39;</span><span class="p">),</span>
  202. <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">),</span>
  203. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;manager_data&#39;</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
  204. <span class="p">)</span></pre></div>
  205. </div>
  206. <p>One natural effect of the joined table inheritance configuration is that the
  207. identity of any mapped object can be determined entirely from the base table.
  208. This has obvious advantages, so SQLAlchemy always considers the primary key
  209. columns of a joined inheritance class to be those of the base table only,
  210. unless otherwise manually configured. In other words, the <tt class="docutils literal"><span class="pre">employee_id</span></tt>
  211. 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
  212. 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
  213. <tt class="docutils literal"><span class="pre">employees.employee_id</span></tt> is considered, and the primary key in this case is
  214. 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
  215. still of course critical to the proper operation of the pattern overall as
  216. they are used to locate the joined row, once the parent row has been
  217. determined, either through a distinct SELECT statement or all at once within a
  218. JOIN.</p>
  219. <p>We then configure mappers as usual, except we use some additional arguments to
  220. indicate the inheritance relationship, the polymorphic discriminator column,
  221. and the <strong>polymorphic identity</strong> of each class; this is the value that will be
  222. stored in the polymorphic discriminator column.</p>
  223. <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>
  224. <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">&#39;employee&#39;</span><span class="p">)</span>
  225. <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>
  226. <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">&#39;engineer&#39;</span><span class="p">)</span>
  227. <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>
  228. <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">&#39;manager&#39;</span><span class="p">)</span></pre></div>
  229. </div>
  230. <p>And that&#8217;s it. Querying against <tt class="docutils literal"><span class="pre">Employee</span></tt> will return a combination of
  231. <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>,
  232. <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
  233. <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
  234. appropriate.</p>
  235. <div class="section" id="basic-control-of-which-tables-are-queried">
  236. <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>
  237. <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
  238. <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
  239. which the Query selects from. Normally, a query such as this:</p>
  240. <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>
  241. </div>
  242. <p>...selects only from the <tt class="docutils literal"><span class="pre">employees</span></tt> table. When loading fresh from the
  243. database, our joined-table setup will query from the parent table only, using
  244. SQL such as this:</p>
  245. <div class="highlight-python+sql"><div class="highlight"><pre><div class='show_sql'>
  246. SELECT employees.employee_id AS employees_employee_id,
  247. employees.name AS employees_name, employees.type AS employees_type
  248. FROM employees
  249. []</div></pre></div>
  250. </div>
  251. <p>As attributes are requested from those <tt class="docutils literal"><span class="pre">Employee</span></tt> objects which are
  252. 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
  253. load is issued for the columns in that related row, if the data was not
  254. already loaded. So above, after accessing the objects you&#8217;d see further SQL
  255. issued along the lines of:</p>
  256. <div class="highlight-python+sql"><div class="highlight"><pre><div class='show_sql'>
  257. SELECT managers.employee_id AS managers_employee_id,
  258. managers.manager_data AS managers_manager_data
  259. FROM managers
  260. WHERE ? = managers.employee_id
  261. [5]
  262. SELECT engineers.employee_id AS engineers_employee_id,
  263. engineers.engineer_info AS engineers_engineer_info
  264. FROM engineers
  265. WHERE ? = engineers.employee_id
  266. [2]</div></pre></div>
  267. </div>
  268. <p>This behavior works well when issuing searches for small numbers of items,
  269. 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
  270. pulled in to the SQL statement unnecessarily. But when querying a larger span
  271. of rows which are known to be of many types, you may want to actively join to
  272. some or all of the joined tables. The <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> feature of
  273. <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>
  274. <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>
  275. objects:</p>
  276. <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>
  277. </div>
  278. <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>
  279. <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>
  280. <div class='show_sql'>
  281. SELECT employees.employee_id AS employees_employee_id,
  282. engineers.employee_id AS engineers_employee_id,
  283. managers.employee_id AS managers_employee_id,
  284. employees.name AS employees_name,
  285. employees.type AS employees_type,
  286. engineers.engineer_info AS engineers_engineer_info,
  287. managers.manager_data AS managers_manager_data
  288. FROM employees
  289. LEFT OUTER JOIN engineers
  290. ON employees.employee_id = engineers.employee_id
  291. LEFT OUTER JOIN managers
  292. ON employees.employee_id = managers.employee_id
  293. []</div></pre></div>
  294. </div>
  295. <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
  296. mapper, a list of classes/mappers, or the string <tt class="docutils literal"><span class="pre">'*'</span></tt> to indicate all
  297. subclasses:</p>
  298. <div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># join to the engineers table</span>
  299. <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>
  300. <span class="c"># join to the engineers and managers tables</span>
  301. <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>
  302. <span class="c"># join to all subclass tables</span>
  303. <span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">(</span><span class="s">&#39;*&#39;</span><span class="p">)</span></pre></div>
  304. </div>
  305. <p>It also accepts a second argument <tt class="docutils literal"><span class="pre">selectable</span></tt> which replaces the automatic
  306. join creation and instead selects directly from the selectable given. This
  307. feature is normally used with &#8220;concrete&#8221; inheritance, described later, but can
  308. be used with any kind of inheritance setup in the case that specialized SQL
  309. should be used to load polymorphically:</p>
  310. <div class="highlight-python+sql"><div class="highlight"><pre><span class="c"># custom selectable</span>
  311. <span class="n">query</span><span class="o">.</span><span class="n">with_polymorphic</span><span class="p">(</span>
  312. <span class="p">[</span><span class="n">Engineer</span><span class="p">,</span> <span class="n">Manager</span><span class="p">],</span>
  313. <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>
  314. <span class="p">)</span></pre></div>
  315. </div>
  316. <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
  317. when you wish to add filter criteria that are specific to one or more
  318. subclasses; it makes the subclasses&#8217; columns available to the WHERE clause:</p>
  319. <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>\
  320. <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">&#39;w&#39;</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">&#39;q&#39;</span><span class="p">))</span></pre></div>
  321. </div>
  322. <p>Note that if you only need to load a single subtype, such as just the
  323. <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
  324. not needed since you would query against the <tt class="docutils literal"><span class="pre">Engineer</span></tt> class directly.</p>
  325. <p>The mapper also accepts <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> as a configurational argument so
  326. that the joined-style load will be issued automatically. This argument may be
  327. the string <tt class="docutils literal"><span class="pre">'*'</span></tt>, a list of classes, or a tuple consisting of either,
  328. followed by a selectable.</p>
  329. <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>
  330. <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">&#39;employee&#39;</span><span class="p">,</span>
  331. <span class="n">with_polymorphic</span><span class="o">=</span><span class="s">&#39;*&#39;</span><span class="p">)</span>
  332. <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>
  333. <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">&#39;engineer&#39;</span><span class="p">)</span>
  334. <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>
  335. <span class="n">polymorphic_identity</span><span class="o">=</span><span class="s">&#39;manager&#39;</span><span class="p">)</span></pre></div>
  336. </div>
  337. <p>The above mapping will produce a query similar to that of
  338. <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>
  339. <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
  340. <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
  341. <tt class="docutils literal"><span class="pre">with_polymorphic</span></tt> setting.</p>
  342. </div>
  343. <div class="section" id="advanced-control-of-which-tables-are-queried">
  344. <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>
  345. <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
  346. simplistic scenarios. However, it currently does not work with any
  347. <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
  348. classes - it also has to be called at the outset of a query.</p>
  349. <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,
  350. 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
  351. query the name of employees with particular criterion:</p>
  352. <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>\
  353. <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>\
  354. <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>\
  355. <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">&#39;w&#39;</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">&#39;q&#39;</span><span class="p">))</span></pre></div>
  356. </div>
  357. <p>The base table, in this case the &#8220;employees&#8221; table, isn&#8217;t always necessary. A
  358. SQL query is always more efficient with fewer joins. Here, if we wanted to
  359. just load information specific to managers or engineers, we can instruct
  360. <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
  361. what&#8217;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
  362. <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>
  363. <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>
  364. <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>\
  365. <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>
  366. </div>
  367. </div>
  368. <div class="section" id="creating-joins-to-specific-subtypes">
  369. <h3>Creating Joins to Specific Subtypes<a class="headerlink" href="#creating-joins-to-specific-subtypes" title="Permalink to this headline">ś</a></h3>
  370. <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
  371. helper which allows the construction of joins along
  372. <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
  373. specific subclasses. Suppose the <tt class="docutils literal"><span class="pre">employees</span></tt> table represents a collection
  374. of employees which are associated with a <tt class="docutils literal"><span class="pre">Company</span></tt> object. We&#8217;ll add a
  375. <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
  376. <tt class="docutils literal"><span class="pre">companies</span></tt>:</p>
  377. <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">&#39;companies&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
  378. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;company_id&#39;</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>
  379. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;name&#39;</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
  380. <span class="p">)</span>
  381. <span class="n">employees</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&#39;employees&#39;</span><span class="p">,</span> <span class="n">metadata</span><span class="p">,</span>
  382. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;employee_id&#39;</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>
  383. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;name&#39;</span><span class="p">,</span> <span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">)),</span>
  384. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;type&#39;</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>
  385. <span class="n">Column</span><span class="p">(</span><span class="s">&#39;company_id&#39;</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">&#39;companies.company_id&#39;</span><span class="p">))</span>
  386. <span class="p">)</span>
  387. <span class="k">class</span> <span class="nc">Company</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
  388. <span class="k">pass</span>
  389. <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>
  390. <span class="s">&#39;employees&#39;</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Employee</span><span class="p">)</span>
  391. <span class="p">})</span></pre></div>
  392. </div>
  393. <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
  394. <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
  395. 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
  396. <tt class="docutils literal"><span class="pre">managers</span></tt> in the mix. If we wish to have criterion which is specifically
  397. against the <tt class="docutils literal"><span class="pre">Engineer</span></tt> class, we can tell those methods to join or subquery
  398. against the joined table representing the subclass using the
  399. <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>
  400. <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>\
  401. <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>\
  402. <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">&#39;someinfo&#39;</span><span class="p">)</span></pre></div>
  403. </div>
  404. <p>A longhand version of this would involve spelling out the full target
  405. selectable within a 2-tuple:</p>
  406. <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>\
  407. <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>\
  408. <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">&#39;someinfo&#39;</span><span class="p">)</span></pre></div>
  409. </div>
  410. <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
  411. single class argument. It may be expanded later on to accept multiple classes.
  412. For now, to join to any group of subclasses, the longhand notation allows this
  413. flexibility:</p>
  414. <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>\
  415. <span class="n">join</span><span class="p">(</span>
  416. <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>
  417. <span class="n">Company</span><span class="o">.</span><span class="n">employees</span><span class="p">)</span>
  418. <span class="p">)</span><span class="o">.</span>\
  419. <span class="nb">filter</span><span class="p">(</span>
  420. <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">&#39;someinfo&#39;</span><span class="p">,</span>
  421. <span class="n">Manager</span><span class="o">.</span><span class="n">manager_data</span><span class="o">==</span><span class="s">&#39;somedata&#39;</span><span class="p">)</span>
  422. <span class="p">)</span></pre></div>
  423. </div>
  424. <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
  425. <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
  426. criterion is in terms of a subclass:</p>
  427. <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>\
  428. <span class="nb">filter</span><span class="p">(</span>
  429. <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>
  430. <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">&#39;someinfo&#39;</span><span class="p">)</span>
  431. <span class="p">)</span><span class="o">.</span><span class="n">all</span><span class="p">()</span></pre></div>
  432. </div>
  433. <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
  434. EXISTS query. To build one by hand looks like:</p>
  435. <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>
  436. <span class="n">exists</span><span class="p">([</span><span class="mi">1</span><span class="p">],</span>
  437. <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">&#39;someinfo&#39;</span><span class="p">,</span>
  438. <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>
  439. <span class="n">from_obj</span><span class="o">=</span><span class="n">em

Large files files are truncated, but you can click here to view the full file