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

# · HTML · 1735 lines · 1601 code · 134 blank · 0 comment · 0 complexity · f1e1f20d4eb8b175a9244080d8c9f17c MD5 · raw file

Large files are truncated click here to view the full file

    

  1. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    2. 

  2.   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    3. 

  3. 

  4. <html>
    5. 

  5.     <head>
    6. 

  6.         <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    7. 

  7.         
    8. 

  8.         <title>
    9. 

  9.             
    10. 

  10.     
    11. 

  11.                 Relationship Configuration
    12. 

  12.              &mdash; 
    13. 

  13.     SQLAlchemy 0.7 Documentation
    14. 

  14. 

  15.         </title>
    16. 

  16.         
    17. 

  17.     <link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
    18. 

  18.     <link rel="stylesheet" href="../_static/docs.css" type="text/css" />
    19. 

  19. 

  20.     <script type="text/javascript">
    21. 

  21.       var DOCUMENTATION_OPTIONS = {
    22. 

  22.           URL_ROOT:    '../',
    23. 

  23.           VERSION:     '0.7.8',
    24. 

  24.           COLLAPSE_MODINDEX: false,
    25. 

  25.           FILE_SUFFIX: '.html'
    26. 

  26.       };
    27. 

  27.     </script>
    28. 

  28.         <script type="text/javascript" src="../_static/jquery.js"></script>
    29. 

  29.         <script type="text/javascript" src="../_static/underscore.js"></script>
    30. 

  30.         <script type="text/javascript" src="../_static/doctools.js"></script>
    31. 

  31.     <script type="text/javascript" src="../_static/init.js"></script>
    32. 

  32.     <link rel="index" title="Index" href="../genindex.html" />
    33. 

  33.     <link rel="search" title="Search" href="../search.html" />
    34. 

  34.         <link rel="copyright" title="Copyright" href="../copyright.html" />
    35. 

  35.     <link rel="top" title="SQLAlchemy 0.7 Documentation" href="../index.html" />
    36. 

  36.         <link rel="up" title="SQLAlchemy ORM" href="index.html" />
    37. 

  37.         <link rel="next" title="Collection Configuration and Techniques" href="collections.html" />
    38. 

  38.         <link rel="prev" title="Mapper Configuration" href="mapper_config.html" />
    39. 

  39. 

  40.     </head>
    41. 

  41.     <body>
    42. 

  42.         
    43. 

  43. 

  44. 

  45. 

  46. 

  47. 

  48. 

  49. 

  50. 

  51. 

  52. 

  53. <div id="docs-container">
    54. 

  54. 

  55. 

  56. 

  57. <div id="docs-header">
    58. 

  58.     <h1>SQLAlchemy 0.7 Documentation</h1>
    59. 

  59. 

  60.     <div id="docs-search">
    61. 

  61.     Search:
    62. 

  62.     <form class="search" action="../search.html" method="get">
    63. 

  63.       <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
    64. 

  64.       <input type="hidden" name="check_keywords" value="yes" />
    65. 

  65.       <input type="hidden" name="area" value="default" />
    66. 

  66.     </form>
    67. 

  67.     </div>
    68. 

  68. 

  69.     <div id="docs-version-header">
    70. 

  70.         Release: <span class="version-num">0.7.8</span> | Release Date: June 16, 2012
    71. 

  71. 

  72. 

  73.     </div>
    74. 

  74. 

  75. </div>
    76. 

  76. 

  77. <div id="docs-top-navigation">
    78. 

  78.     <div id="docs-top-page-control" class="docs-navigation-links">
    79. 

  79.         <ul>
    80. 

  80.             <li>Prev:
    81. 

  81.             <a href="mapper_config.html" title="previous chapter">Mapper Configuration</a>
    82. 

  82.             </li>
    83. 

  83.             <li>Next:
    84. 

  84.             <a href="collections.html" title="next chapter">Collection Configuration and Techniques</a>
    85. 

  85.             </li>
    86. 

  86. 

  87.         <li>
    88. 

  88.             <a href="../contents.html">Table of Contents</a> |
    89. 

  89.             <a href="../genindex.html">Index</a>
    90. 

  90.             | <a href="../_sources/orm/relationships.txt">view source
    91. 

  91.         </li>
    92. 

  92.         </ul>
    93. 

  93.     </div>
    94. 

  94. 

  95.     <div id="docs-navigation-banner">
    96. 

  96.         <a href="../index.html">SQLAlchemy 0.7 Documentation</a>
    97. 

  97.                 ť <a href="index.html" title="SQLAlchemy ORM">SQLAlchemy ORM</a>
    98. 

  98.         ť 
    99. 

  99.                 Relationship Configuration
    100. 

  100.              
    101. 

  101. 

  102.         <h2>
    103. 

  103.             
    104. 

  104.                 Relationship Configuration
    105. 

  105.             
    106. 

  106.         </h2>
    107. 

  107.     </div>
    108. 

  108. 

  109. </div>
    110. 

  110. 

  111. <div id="docs-body-container">
    112. 

  112. 

  113.     <div id="docs-sidebar">
    114. 

  114.     <h3><a href="../index.html">Table of Contents</a></h3>
    115. 

  115.     <ul>
    116. 

  116. <li><a class="reference internal" href="#">Relationship Configuration</a><ul>
    117. 

  117. <li><a class="reference internal" href="#basic-relational-patterns">Basic Relational Patterns</a><ul>
    118. 

  118. <li><a class="reference internal" href="#one-to-many">One To Many</a></li>
    119. 

  119. <li><a class="reference internal" href="#many-to-one">Many To One</a></li>
    120. 

  120. <li><a class="reference internal" href="#one-to-one">One To One</a></li>
    121. 

  121. <li><a class="reference internal" href="#many-to-many">Many To Many</a></li>
    122. 

  122. <li><a class="reference internal" href="#association-object">Association Object</a></li>
    123. 

  123. </ul>
    124. 

  124. </li>
    125. 

  125. <li><a class="reference internal" href="#adjacency-list-relationships">Adjacency List Relationships</a><ul>
    126. 

  126. <li><a class="reference internal" href="#self-referential-query-strategies">Self-Referential Query Strategies</a></li>
    127. 

  127. <li><a class="reference internal" href="#configuring-self-referential-eager-loading">Configuring Self-Referential Eager Loading</a></li>
    128. 

  128. </ul>
    129. 

  129. </li>
    130. 

  130. <li><a class="reference internal" href="#linking-relationships-with-backref">Linking Relationships with Backref</a><ul>
    131. 

  131. <li><a class="reference internal" href="#backref-arguments">Backref Arguments</a></li>
    132. 

  132. <li><a class="reference internal" href="#one-way-backrefs">One Way Backrefs</a></li>
    133. 

  133. </ul>
    134. 

  134. </li>
    135. 

  135. <li><a class="reference internal" href="#setting-the-primaryjoin-and-secondaryjoin">Setting the primaryjoin and secondaryjoin</a><ul>
    136. 

  136. <li><a class="reference internal" href="#specifying-alternate-join-conditions">Specifying Alternate Join Conditions</a></li>
    137. 

  137. <li><a class="reference internal" href="#self-referential-many-to-many-relationship">Self-Referential Many-to-Many Relationship</a></li>
    138. 

  138. <li><a class="reference internal" href="#specifying-foreign-keys">Specifying Foreign Keys</a></li>
    139. 

  139. <li><a class="reference internal" href="#building-query-enabled-properties">Building Query-Enabled Properties</a></li>
    140. 

  140. </ul>
    141. 

  141. </li>
    142. 

  142. <li><a class="reference internal" href="#rows-that-point-to-themselves-mutually-dependent-rows">Rows that point to themselves / Mutually Dependent Rows</a></li>
    143. 

  143. <li><a class="reference internal" href="#mutable-primary-keys-update-cascades">Mutable Primary Keys / Update Cascades</a></li>
    144. 

  144. <li><a class="reference internal" href="#relationships-api">Relationships API</a></li>
    145. 

  145. </ul>
    146. 

  146. </li>
    147. 

  147. </ul>
    148. 

  148. 

  149. 

  150.     <h4>Previous Topic</h4>
    151. 

  151.     <p>
    152. 

  152.     <a href="mapper_config.html" title="previous chapter">Mapper Configuration</a>
    153. 

  153.     </p>
    154. 

  154.     <h4>Next Topic</h4>
    155. 

  155.     <p>
    156. 

  156.     <a href="collections.html" title="next chapter">Collection Configuration and Techniques</a>
    157. 

  157.     </p>
    158. 

  158. 

  159. 

  160.     <h4>Quick Search</h4>
    161. 

  161.     <p>
    162. 

  162.     <form class="search" action="../search.html" method="get">
    163. 

  163.       <input type="text" name="q" size="18" /> <input type="submit" value="Search" />
    164. 

  164.       <input type="hidden" name="check_keywords" value="yes" />
    165. 

  165.       <input type="hidden" name="area" value="default" />
    166. 

  166.     </form>
    167. 

  167.     </p>
    168. 

  168. 

  169.     </div>
    170. 

  170. 

  171.     <div id="docs-body" class="withsidebar" >
    172. 

  172.         
    173. 

  173. <span class="target" id="module-sqlalchemy.orm"></span><div class="section" id="relationship-configuration">
    174. 

  174. <span id="relationship-config-toplevel"></span><h1>Relationship Configuration<a class="headerlink" href="#relationship-configuration" title="Permalink to this headline">ś</a></h1>
    175. 

  175. <p>This section describes the <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> function and in depth discussion
    176. 

  176. of its usage.   The reference material here continues into the next section,
    177. 

  177. <a class="reference internal" href="collections.html"><em>Collection Configuration and Techniques</em></a>, which has additional detail on configuration
    178. 

  178. of collections via <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.</p>
    179. 

  179. <div class="section" id="basic-relational-patterns">
    180. 

  180. <span id="relationship-patterns"></span><h2>Basic Relational Patterns<a class="headerlink" href="#basic-relational-patterns" title="Permalink to this headline">ś</a></h2>
    181. 

  181. <p>A quick walkthrough of the basic relational patterns.</p>
    182. 

  182. <p>The imports used for each of the following sections is as follows:</p>
    183. 

  183. <div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy</span> <span class="kn">import</span> <span class="n">Table</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span>
    184. 

  184. <span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span><span class="p">,</span> <span class="n">backref</span>
    185. 

  185. <span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
    186. 

  186. 

  187. <span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span></pre></div>
    188. 

  188. </div>
    189. 

  189. <div class="section" id="one-to-many">
    190. 

  190. <h3>One To Many<a class="headerlink" href="#one-to-many" title="Permalink to this headline">ś</a></h3>
    191. 

  191. <p>A one to many relationship places a foreign key on the child table referencing
    192. 

  192. the parent.  <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> is then specified on the parent, as referencing
    193. 

  193. a collection of items represented by the child:</p>
    194. 

  194. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    195. 

  195.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
    196. 

  196.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    197. 

  197.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">)</span>
    198. 

  198. 

  199. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    200. 

  200.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
    201. 

  201.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    202. 

  202.     <span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</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;parent.id&#39;</span><span class="p">))</span></pre></div>
    203. 

  203. </div>
    204. 

  204. <p>To establish a bidirectional relationship in one-to-many, where the &#8220;reverse&#8221;
    205. 

  205. side is a many to one, specify the <tt class="docutils literal"><span class="pre">backref</span></tt> option:</p>
    206. 

  206. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    207. 

  207.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
    208. 

  208.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    209. 

  209.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parent&quot;</span><span class="p">)</span>
    210. 

  210. 

  211. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    212. 

  212.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
    213. 

  213.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    214. 

  214.     <span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</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;parent.id&#39;</span><span class="p">))</span></pre></div>
    215. 

  215. </div>
    216. 

  216. <p><tt class="docutils literal"><span class="pre">Child</span></tt> will get a <tt class="docutils literal"><span class="pre">parent</span></tt> attribute with many-to-one semantics.</p>
    217. 

  217. </div>
    218. 

  218. <div class="section" id="many-to-one">
    219. 

  219. <h3>Many To One<a class="headerlink" href="#many-to-one" title="Permalink to this headline">ś</a></h3>
    220. 

  220. <p>Many to one places a foreign key in the parent table referencing the child.
    221. 

  221. <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> is declared on the parent, where a new scalar-holding
    222. 

  222. attribute will be created:</p>
    223. 

  223. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    224. 

  224.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
    225. 

  225.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    226. 

  226.     <span class="n">child_id</span> <span class="o">=</span> <span class="n">Column</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;child.id&#39;</span><span class="p">))</span>
    227. 

  227.     <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">)</span>
    228. 

  228. 

  229. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    230. 

  230.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
    231. 

  231.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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></pre></div>
    232. 

  232. </div>
    233. 

  233. <p>Bidirectional behavior is achieved by specifying <tt class="docutils literal"><span class="pre">backref=&quot;parents&quot;</span></tt>,
    234. 

  234. which will place a one-to-many collection on the <tt class="docutils literal"><span class="pre">Child</span></tt> class:</p>
    235. 

  235. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    236. 

  236.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
    237. 

  237.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    238. 

  238.     <span class="n">child_id</span> <span class="o">=</span> <span class="n">Column</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;child.id&#39;</span><span class="p">))</span>
    239. 

  239.     <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span></pre></div>
    240. 

  240. </div>
    241. 

  241. </div>
    242. 

  242. <div class="section" id="one-to-one">
    243. 

  243. <h3>One To One<a class="headerlink" href="#one-to-one" title="Permalink to this headline">ś</a></h3>
    244. 

  244. <p>One To One is essentially a bidirectional relationship with a scalar
    245. 

  245. attribute on both sides. To achieve this, the <tt class="docutils literal"><span class="pre">uselist=False</span></tt> flag indicates
    246. 

  246. the placement of a scalar attribute instead of a collection on the &#8220;many&#8221; side
    247. 

  247. of the relationship. To convert one-to-many into one-to-one:</p>
    248. 

  248. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    249. 

  249.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
    250. 

  250.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    251. 

  251.     <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="bp">False</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parent&quot;</span><span class="p">)</span>
    252. 

  252. 

  253. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    254. 

  254.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
    255. 

  255.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    256. 

  256.     <span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</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;parent.id&#39;</span><span class="p">))</span></pre></div>
    257. 

  257. </div>
    258. 

  258. <p>Or to turn a one-to-many backref into one-to-one, use the <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> function
    259. 

  259. to provide arguments for the reverse side:</p>
    260. 

  260. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    261. 

  261.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;parent&#39;</span>
    262. 

  262.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    263. 

  263.     <span class="n">child_id</span> <span class="o">=</span> <span class="n">Column</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;child.id&#39;</span><span class="p">))</span>
    264. 

  264.     <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">&quot;parent&quot;</span><span class="p">,</span> <span class="n">uselist</span><span class="o">=</span><span class="bp">False</span><span class="p">))</span>
    265. 

  265. 

  266. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    267. 

  267.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;child&#39;</span>
    268. 

  268.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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></pre></div>
    269. 

  269. </div>
    270. 

  270. </div>
    271. 

  271. <div class="section" id="many-to-many">
    272. 

  272. <span id="relationships-many-to-many"></span><h3>Many To Many<a class="headerlink" href="#many-to-many" title="Permalink to this headline">ś</a></h3>
    273. 

  273. <p>Many to Many adds an association table between two classes. The association
    274. 

  274. table is indicated by the <tt class="docutils literal"><span class="pre">secondary</span></tt> argument to
    275. 

  275. <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>.  Usually, 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> uses the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.MetaData" title="sqlalchemy.schema.MetaData"><tt class="xref py py-class docutils literal"><span class="pre">MetaData</span></tt></a>
    276. 

  276. object associated with the declarative base class, so that the <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKey" title="sqlalchemy.schema.ForeignKey"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKey</span></tt></a>
    277. 

  277. directives can locate the remote tables with which to link:</p>
    278. 

  278. <div class="highlight-python"><div class="highlight"><pre><span class="n">association_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&#39;association&#39;</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
    279. 

  279.     <span class="n">Column</span><span class="p">(</span><span class="s">&#39;left_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;left.id&#39;</span><span class="p">)),</span>
    280. 

  280.     <span class="n">Column</span><span class="p">(</span><span class="s">&#39;right_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;right.id&#39;</span><span class="p">))</span>
    281. 

  281. <span class="p">)</span>
    282. 

  282. 

  283. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    284. 

  284.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
    285. 

  285.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    286. 

  286.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
    287. 

  287.                     <span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">)</span>
    288. 

  288. 

  289. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    290. 

  290.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
    291. 

  291.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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></pre></div>
    292. 

  292. </div>
    293. 

  293. <p>For a bidirectional relationship, both sides of the relationship contain a
    294. 

  294. collection.  The <tt class="docutils literal"><span class="pre">backref</span></tt> keyword will automatically use
    295. 

  295. the same <tt class="docutils literal"><span class="pre">secondary</span></tt> argument for the reverse relationship:</p>
    296. 

  296. <div class="highlight-python"><div class="highlight"><pre><span class="n">association_table</span> <span class="o">=</span> <span class="n">Table</span><span class="p">(</span><span class="s">&#39;association&#39;</span><span class="p">,</span> <span class="n">Base</span><span class="o">.</span><span class="n">metadata</span><span class="p">,</span>
    297. 

  297.     <span class="n">Column</span><span class="p">(</span><span class="s">&#39;left_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;left.id&#39;</span><span class="p">)),</span>
    298. 

  298.     <span class="n">Column</span><span class="p">(</span><span class="s">&#39;right_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;right.id&#39;</span><span class="p">))</span>
    299. 

  299. <span class="p">)</span>
    300. 

  300. 

  301. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    302. 

  302.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
    303. 

  303.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    304. 

  304.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
    305. 

  305.                     <span class="n">secondary</span><span class="o">=</span><span class="n">association_table</span><span class="p">,</span>
    306. 

  306.                     <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span>
    307. 

  307. 

  308. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    309. 

  309.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
    310. 

  310.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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></pre></div>
    311. 

  311. </div>
    312. 

  312. <p>The <tt class="docutils literal"><span class="pre">secondary</span></tt> argument of <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> also accepts a callable
    313. 

  313. that returns the ultimate argument, which is evaluated only when mappers are
    314. 

  314. first used.   Using this, we can define the <tt class="docutils literal"><span class="pre">association_table</span></tt> at a later
    315. 

  315. point, as long as it&#8217;s available to the callable after all module initialization
    316. 

  316. is complete:</p>
    317. 

  317. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    318. 

  318.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
    319. 

  319.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    320. 

  320.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
    321. 

  321.                     <span class="n">secondary</span><span class="o">=</span><span class="k">lambda</span><span class="p">:</span> <span class="n">association_table</span><span class="p">,</span>
    322. 

  322.                     <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span></pre></div>
    323. 

  323. </div>
    324. 

  324. <p>With the declarative extension in use, the traditional &#8220;string name of the table&#8221;
    325. 

  325. is accepted as well, matching the name of the table as stored in <tt class="docutils literal"><span class="pre">Base.metadata.tables</span></tt>:</p>
    326. 

  326. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    327. 

  327.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
    328. 

  328.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    329. 

  329.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span>
    330. 

  330.                     <span class="n">secondary</span><span class="o">=</span><span class="s">&quot;association&quot;</span><span class="p">,</span>
    331. 

  331.                     <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parents&quot;</span><span class="p">)</span></pre></div>
    332. 

  332. </div>
    333. 

  333. </div>
    334. 

  334. <div class="section" id="association-object">
    335. 

  335. <span id="association-pattern"></span><h3>Association Object<a class="headerlink" href="#association-object" title="Permalink to this headline">ś</a></h3>
    336. 

  336. <p>The association object pattern is a variant on many-to-many: it&#8217;s
    337. 

  337. used when your association table contains additional columns beyond those
    338. 

  338. which are foreign keys to the left and right tables. Instead of using the
    339. 

  339. <tt class="docutils literal"><span class="pre">secondary</span></tt> argument, you map a new class directly to the association table.
    340. 

  340. The left side of the relationship references the association object via
    341. 

  341. one-to-many, and the association class references the right side via
    342. 

  342. many-to-one.  Below we illustrate an association table mapped to the
    343. 

  343. <tt class="docutils literal"><span class="pre">Association</span></tt> class which includes a column called <tt class="docutils literal"><span class="pre">extra_data</span></tt>,
    344. 

  344. which is a string value that is stored along with each association
    345. 

  345. between <tt class="docutils literal"><span class="pre">Parent</span></tt> and <tt class="docutils literal"><span class="pre">Child</span></tt>:</p>
    346. 

  346. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Association</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    347. 

  347.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;association&#39;</span>
    348. 

  348.     <span class="n">left_id</span> <span class="o">=</span> <span class="n">Column</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;left.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
    349. 

  349.     <span class="n">right_id</span> <span class="o">=</span> <span class="n">Column</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;right.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
    350. 

  350.     <span class="n">extra_data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
    351. 

  351.     <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">)</span>
    352. 

  352. 

  353. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    354. 

  354.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
    355. 

  355.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    356. 

  356.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Association&quot;</span><span class="p">)</span>
    357. 

  357. 

  358. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    359. 

  359.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
    360. 

  360.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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></pre></div>
    361. 

  361. </div>
    362. 

  362. <p>The bidirectional version adds backrefs to both relationships:</p>
    363. 

  363. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Association</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    364. 

  364.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;association&#39;</span>
    365. 

  365.     <span class="n">left_id</span> <span class="o">=</span> <span class="n">Column</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;left.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
    366. 

  366.     <span class="n">right_id</span> <span class="o">=</span> <span class="n">Column</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;right.id&#39;</span><span class="p">),</span> <span class="n">primary_key</span><span class="o">=</span><span class="bp">True</span><span class="p">)</span>
    367. 

  367.     <span class="n">extra_data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
    368. 

  368.     <span class="n">child</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Child&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parent_assocs&quot;</span><span class="p">)</span>
    369. 

  369. 

  370. <span class="k">class</span> <span class="nc">Parent</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    371. 

  371.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;left&#39;</span>
    372. 

  372.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    373. 

  373.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Association&quot;</span><span class="p">,</span> <span class="n">backref</span><span class="o">=</span><span class="s">&quot;parent&quot;</span><span class="p">)</span>
    374. 

  374. 

  375. <span class="k">class</span> <span class="nc">Child</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    376. 

  376.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;right&#39;</span>
    377. 

  377.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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></pre></div>
    378. 

  378. </div>
    379. 

  379. <p>Working with the association pattern in its direct form requires that child
    380. 

  380. objects are associated with an association instance before being appended to
    381. 

  381. the parent; similarly, access from parent to child goes through the
    382. 

  382. association object:</p>
    383. 

  383. <div class="highlight-python"><div class="highlight"><pre><span class="c"># create parent, append a child via association</span>
    384. 

  384. <span class="n">p</span> <span class="o">=</span> <span class="n">Parent</span><span class="p">()</span>
    385. 

  385. <span class="n">a</span> <span class="o">=</span> <span class="n">Association</span><span class="p">(</span><span class="n">extra_data</span><span class="o">=</span><span class="s">&quot;some data&quot;</span><span class="p">)</span>
    386. 

  386. <span class="n">a</span><span class="o">.</span><span class="n">child</span> <span class="o">=</span> <span class="n">Child</span><span class="p">()</span>
    387. 

  387. <span class="n">p</span><span class="o">.</span><span class="n">children</span><span class="o">.</span><span class="n">append</span><span class="p">(</span><span class="n">a</span><span class="p">)</span>
    388. 

  388. 

  389. <span class="c"># iterate through child objects via association, including association</span>
    390. 

  390. <span class="c"># attributes</span>
    391. 

  391. <span class="k">for</span> <span class="n">assoc</span> <span class="ow">in</span> <span class="n">p</span><span class="o">.</span><span class="n">children</span><span class="p">:</span>
    392. 

  392.     <span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">extra_data</span>
    393. 

  393.     <span class="k">print</span> <span class="n">assoc</span><span class="o">.</span><span class="n">child</span></pre></div>
    394. 

  394. </div>
    395. 

  395. <p>To enhance the association object pattern such that direct
    396. 

  396. access to the <tt class="docutils literal"><span class="pre">Association</span></tt> object is optional, SQLAlchemy
    397. 

  397. provides the <a class="reference internal" href="extensions/associationproxy.html"><em>Association Proxy</em></a> extension. This
    398. 

  398. extension allows the configuration of attributes which will
    399. 

  399. access two &#8220;hops&#8221; with a single access, one &#8220;hop&#8221; to the
    400. 

  400. associated object, and a second to a target attribute.</p>
    401. 

  401. <div class="admonition note">
    402. 

  402. <p class="first admonition-title">Note</p>
    403. 

  403. <p class="last">When using the association object pattern, it is
    404. 

  404. advisable that the association-mapped table not be used
    405. 

  405. as the <tt class="docutils literal"><span class="pre">secondary</span></tt> argument on a <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>
    406. 

  406. elsewhere, unless that <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> contains
    407. 

  407. the option <tt class="docutils literal"><span class="pre">viewonly=True</span></tt>.   SQLAlchemy otherwise
    408. 

  408. may attempt to emit redundant INSERT and DELETE
    409. 

  409. statements on the same table, if similar state is detected
    410. 

  410. on the related attribute as well as the associated
    411. 

  411. object.</p>
    412. 

  412. </div>
    413. 

  413. </div>
    414. 

  414. </div>
    415. 

  415. <div class="section" id="adjacency-list-relationships">
    416. 

  416. <h2>Adjacency List Relationships<a class="headerlink" href="#adjacency-list-relationships" title="Permalink to this headline">ś</a></h2>
    417. 

  417. <p>The <strong>adjacency list</strong> pattern is a common relational pattern whereby a table
    418. 

  418. contains a foreign key reference to itself. This is the most common
    419. 

  419. way to represent hierarchical data in flat tables.  Other methods
    420. 

  420. include <strong>nested sets</strong>, sometimes called &#8220;modified preorder&#8221;,
    421. 

  421. as well as <strong>materialized path</strong>.  Despite the appeal that modified preorder
    422. 

  422. has when evaluated for its fluency within SQL queries, the adjacency list model is
    423. 

  423. probably the most appropriate pattern for the large majority of hierarchical
    424. 

  424. storage needs, for reasons of concurrency, reduced complexity, and that
    425. 

  425. modified preorder has little advantage over an application which can fully
    426. 

  426. load subtrees into the application space.</p>
    427. 

  427. <p>In this example, we&#8217;ll work with a single mapped
    428. 

  428. class called <tt class="docutils literal"><span class="pre">Node</span></tt>, representing a tree structure:</p>
    429. 

  429. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Node</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    430. 

  430.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;node&#39;</span>
    431. 

  431.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    432. 

  432.     <span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</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;node.id&#39;</span><span class="p">))</span>
    433. 

  433.     <span class="n">data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
    434. 

  434.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Node&quot;</span><span class="p">)</span></pre></div>
    435. 

  435. </div>
    436. 

  436. <p>With this structure, a graph such as the following:</p>
    437. 

  437. <div class="highlight-python"><pre>root --+---&gt; child1
    438. 

  438.        +---&gt; child2 --+--&gt; subchild1
    439. 

  439.        |              +--&gt; subchild2
    440. 

  440.        +---&gt; child3</pre>
    441. 

  441. </div>
    442. 

  442. <p>Would be represented with data such as:</p>
    443. 

  443. <div class="highlight-python"><pre>id       parent_id     data
    444. 

  444. ---      -------       ----
    445. 

  445. 1        NULL          root
    446. 

  446. 2        1             child1
    447. 

  447. 3        1             child2
    448. 

  448. 4        3             subchild1
    449. 

  449. 5        3             subchild2
    450. 

  450. 6        1             child3</pre>
    451. 

  451. </div>
    452. 

  452. <p>The <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a> configuration here works in the
    453. 

  453. same way as a &#8220;normal&#8221; one-to-many relationship, with the
    454. 

  454. exception that the &#8220;direction&#8221;, i.e. whether the relationship
    455. 

  455. is one-to-many or many-to-one, is assumed by default to
    456. 

  456. be one-to-many.   To establish the relationship as many-to-one,
    457. 

  457. an extra directive is added known as <tt class="docutils literal"><span class="pre">remote_side</span></tt>, which
    458. 

  458. is a <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> or collection of <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.Column" title="sqlalchemy.schema.Column"><tt class="xref py py-class docutils literal"><span class="pre">Column</span></tt></a> objects
    459. 

  459. that indicate those which should be considered to be &#8220;remote&#8221;:</p>
    460. 

  460. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Node</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    461. 

  461.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;node&#39;</span>
    462. 

  462.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    463. 

  463.     <span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</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;node.id&#39;</span><span class="p">))</span>
    464. 

  464.     <span class="n">data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
    465. 

  465.     <span class="n">parent</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Node&quot;</span><span class="p">,</span> <span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="nb">id</span><span class="p">])</span></pre></div>
    466. 

  466. </div>
    467. 

  467. <p>Where above, the <tt class="docutils literal"><span class="pre">id</span></tt> column is applied as the <tt class="docutils literal"><span class="pre">remote_side</span></tt>
    468. 

  468. of the <tt class="docutils literal"><span class="pre">parent</span></tt> <a class="reference internal" href="#sqlalchemy.orm.relationship" title="sqlalchemy.orm.relationship"><tt class="xref py py-func docutils literal"><span class="pre">relationship()</span></tt></a>, thus establishing
    469. 

  469. <tt class="docutils literal"><span class="pre">parent_id</span></tt> as the &#8220;local&#8221; side, and the relationship
    470. 

  470. then behaves as a many-to-one.</p>
    471. 

  471. <p>As always, both directions can be combined into a bidirectional
    472. 

  472. relationship using the <a class="reference internal" href="#sqlalchemy.orm.backref" title="sqlalchemy.orm.backref"><tt class="xref py py-func docutils literal"><span class="pre">backref()</span></tt></a> function:</p>
    473. 

  473. <div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Node</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
    474. 

  474.     <span class="n">__tablename__</span> <span class="o">=</span> <span class="s">&#39;node&#39;</span>
    475. 

  475.     <span class="nb">id</span> <span class="o">=</span> <span class="n">Column</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>
    476. 

  476.     <span class="n">parent_id</span> <span class="o">=</span> <span class="n">Column</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;node.id&#39;</span><span class="p">))</span>
    477. 

  477.     <span class="n">data</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">(</span><span class="mi">50</span><span class="p">))</span>
    478. 

  478.     <span class="n">children</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">&quot;Node&quot;</span><span class="p">,</span>
    479. 

  479.                 <span class="n">backref</span><span class="o">=</span><span class="n">backref</span><span class="p">(</span><span class="s">&#39;parent&#39;</span><span class="p">,</span> <span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="nb">id</span><span class="p">])</span>
    480. 

  480.             <span class="p">)</span></pre></div>
    481. 

  481. </div>
    482. 

  482. <p>There are several examples included with SQLAlchemy illustrating
    483. 

  483. self-referential strategies; these include <a class="reference internal" href="examples.html#examples-adjacencylist"><em>Adjacency List</em></a> and
    484. 

  484. <a class="reference internal" href="examples.html#examples-xmlpersistence"><em>XML Persistence</em></a>.</p>
    485. 

  485. <div class="section" id="self-referential-query-strategies">
    486. 

  486. <h3>Self-Referential Query Strategies<a class="headerlink" href="#self-referential-query-strategies" title="Permalink to this headline">ś</a></h3>
    487. 

  487. <p>Querying of self-referential structures works like any other query:</p>
    488. 

  488. <div class="highlight-python"><div class="highlight"><pre><span class="c"># get all nodes named &#39;child2&#39;</span>
    489. 

  489. <span class="n">session</span><span class="o">.</span><span class="n">query</span><span class="p">(</span><span class="n">Node</span><span class="p">)</span><span class="o">.</span><span class="n">filter</span><span class="p">(</span><span class="n">Node</span><span class="o">.</span><span class="n">data</span><span class="o">==</span><span class="s">&#39;child2&#39;</span><span class="p">)</span></pre></div>
    490. 

  490. </div>
    491. 

  491. <p>However extra care is needed when attempting to join along
    492. 

  492. the foreign key from one level of the tree to the next.  In SQL,
    493. 

  493. a join from a table to itself requires that at least one side of the
    494. 

  494. expression be &#8220;aliased&#8221; so that it can be unambiguously referred to.</p>
    495. 

  495. <p>Recall from <a class="ref…
    496.