530
532
<p>There are several examples included with SQLAlchemy illustrating
531
533
self-referential strategies; these include <a class="reference internal" href="examples.html#examples-adjacencylist"><em>Adjacency List</em></a> and
532
534
<a class="reference internal" href="examples.html#examples-xmlpersistence"><em>XML Persistence</em></a>.</p>
535
<div class="section" id="composite-adjacency-lists">
536
<h3>Composite Adjacency Lists<a class="headerlink" href="#composite-adjacency-lists" title="Permalink to this headline">¶</a></h3>
537
<p>A sub-category of the adjacency list relationship is the rare
538
case where a particular column is present on both the “local” and
539
“remote” side of the join condition. An example is the <tt class="docutils literal"><span class="pre">Folder</span></tt>
540
class below; using a composite primary key, the <tt class="docutils literal"><span class="pre">account_id</span></tt>
541
column refers to itself, to indicate sub folders which are within
542
the same account as that of the parent; while <tt class="docutils literal"><span class="pre">folder_id</span></tt> refers
543
to a specific folder within that account:</p>
544
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Folder</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
545
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'folder'</span>
546
<span class="n">__table_args__</span> <span class="o">=</span> <span class="p">(</span>
547
<span class="n">ForeignKeyConstraint</span><span class="p">(</span>
548
<span class="p">[</span><span class="s">'account_id'</span><span class="p">,</span> <span class="s">'parent_id'</span><span class="p">],</span>
549
<span class="p">[</span><span class="s">'folder.account_id'</span><span class="p">,</span> <span class="s">'folder.folder_id'</span><span class="p">]),</span>
550
<span class="p">)</span>
552
<span class="n">account_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>
553
<span class="n">folder_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>
554
<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>
555
<span class="n">name</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span>
557
<span class="n">parent_folder</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Folder"</span><span class="p">,</span>
558
<span class="n">backref</span><span class="o">=</span><span class="s">"child_folders"</span><span class="p">,</span>
559
<span class="n">remote_side</span><span class="o">=</span><span class="p">[</span><span class="n">account_id</span><span class="p">,</span> <span class="n">folder_id</span><span class="p">]</span>
560
<span class="p">)</span></pre></div>
562
<p>Above, we pass <tt class="docutils literal"><span class="pre">account_id</span></tt> into the <tt class="docutils literal"><span class="pre">remote_side</span></tt> list.
563
<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> recognizes that the <tt class="docutils literal"><span class="pre">account_id</span></tt> column here
564
is on both sides, and aligns the “remote” column along with the
565
<tt class="docutils literal"><span class="pre">folder_id</span></tt> column, which it recognizes as uniquely present on
566
the “remote” side.</p>
567
<p class="versionadded">
568
<span class="versionmodified">New in version 0.8: </span>Support for self-referential composite keys in <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>
569
where a column points to itself.</p>
533
571
<div class="section" id="self-referential-query-strategies">
534
572
<h3>Self-Referential Query Strategies<a class="headerlink" href="#self-referential-query-strategies" title="Permalink to this headline">¶</a></h3>
535
573
<p>Querying of self-referential structures works like any other query:</p>
892
930
these to a minimum overall.</p>
895
<div class="section" id="setting-the-primaryjoin-and-secondaryjoin">
896
<span id="relationship-primaryjoin"></span><h2>Setting the primaryjoin and secondaryjoin<a class="headerlink" href="#setting-the-primaryjoin-and-secondaryjoin" title="Permalink to this headline">¶</a></h2>
897
<p>A common scenario arises when we attempt to relate two
898
classes together, where there exist multiple ways to join the
933
<div class="section" id="configuring-how-relationship-joins">
934
<span id="relationship-configure-joins"></span><h2>Configuring how Relationship Joins<a class="headerlink" href="#configuring-how-relationship-joins" title="Permalink to this headline">¶</a></h2>
935
<p><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> will normally create a join between two tables
936
by examining the foreign key relationship between the two tables
937
to determine which columns should be compared. There are a variety
938
of situations where this behavior needs to be customized.</p>
939
<div class="section" id="handling-multiple-join-paths">
940
<span id="relationship-foreign-keys"></span><h3>Handling Multiple Join Paths<a class="headerlink" href="#handling-multiple-join-paths" title="Permalink to this headline">¶</a></h3>
941
<p>One of the most common situations to deal with is when
942
there are more than one foreign key path between two tables.</p>
900
943
<p>Consider a <tt class="docutils literal"><span class="pre">Customer</span></tt> class that contains two foreign keys to an <tt class="docutils literal"><span class="pre">Address</span></tt>
902
945
<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">Integer</span><span class="p">,</span> <span class="n">ForeignKey</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span>
925
968
<span class="nb">zip</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">String</span><span class="p">)</span></pre></div>
927
970
<p>The above mapping, when we attempt to use it, will produce the error:</p>
928
<div class="highlight-python"><pre>sqlalchemy.exc.ArgumentError: Could not determine join condition between
929
parent/child tables on relationship Customer.billing_address. Specify a
930
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
931
needed as well.</pre>
971
<div class="highlight-python"><pre>sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
972
condition between parent/child tables on relationship
973
Customer.billing_address - there are multiple foreign key
974
paths linking the tables. Specify the 'foreign_keys' argument,
975
providing a list of those columns which should be
976
counted as containing a foreign key reference to the parent table.</pre>
933
<p>What this error means is that if you have a <tt class="docutils literal"><span class="pre">Customer</span></tt> object, and wish
934
to load in an associated <tt class="docutils literal"><span class="pre">Address</span></tt>, there is the choice of retrieving
935
the <tt class="docutils literal"><span class="pre">Address</span></tt> referred to by the <tt class="docutils literal"><span class="pre">billing_address_id</span></tt> column or the one
936
referred to by the <tt class="docutils literal"><span class="pre">shipping_address_id</span></tt> column. 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>,
937
as it is, cannot determine its full configuration. The examples at
938
<a class="reference internal" href="#relationship-patterns"><em>Basic Relational Patterns</em></a> didn’t have this issue, because in each of those examples
939
there was only <strong>one</strong> way to refer to the related table.</p>
940
<p>To resolve this issue, <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> accepts an argument named
941
<tt class="docutils literal"><span class="pre">primaryjoin</span></tt> which accepts a Python-based SQL expression, using the system described
942
at <a class="reference internal" href="../core/tutorial.html"><em>SQL Expression Language Tutorial</em></a>, that describes how the two tables should be joined
943
together. When using the declarative system, we often will specify this Python
944
expression within a string, which is late-evaluated by the mapping configuration
945
system so that it has access to the full namespace of available classes:</p>
978
<p>The above message is pretty long. There are many potential messages
979
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> can return, which have been carefully tailored
980
to detect a variety of common configurational issues; most will suggest
981
the additional configuration that’s needed to resolve the ambiguity
982
or other missing information.</p>
983
<p>In this case, the message wants us to qualify each <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>
984
by instructing for each one which foreign key column should be considered, and
985
the appropriate form is as follows:</p>
946
986
<div class="highlight-python"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Customer</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
947
987
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'customer'</span>
948
988
<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>
951
991
<span class="n">billing_address_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">"address.id"</span><span class="p">))</span>
952
992
<span class="n">shipping_address_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">"address.id"</span><span class="p">))</span>
954
<span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span>
955
<span class="n">primaryjoin</span><span class="o">=</span><span class="s">"Address.id==Customer.billing_address_id"</span><span class="p">)</span>
956
<span class="n">shipping_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span>
957
<span class="n">primaryjoin</span><span class="o">=</span><span class="s">"Address.id==Customer.shipping_address_id"</span><span class="p">)</span></pre></div>
994
<span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">billing_address_id</span><span class="p">])</span>
995
<span class="n">shipping_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">shipping_address_id</span><span class="p">])</span></pre></div>
959
<p>Above, loading the <tt class="docutils literal"><span class="pre">Customer.billing_address</span></tt> relationship from a <tt class="docutils literal"><span class="pre">Customer</span></tt>
997
<p>Above, we specify the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> argument, which 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 list
998
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 which indicate those columns to be considered “foreign”,
999
or in other words, the columns that contain a value referring to a parent table.
1000
Loading the <tt class="docutils literal"><span class="pre">Customer.billing_address</span></tt> relationship from a <tt class="docutils literal"><span class="pre">Customer</span></tt>
960
1001
object will use the value present in <tt class="docutils literal"><span class="pre">billing_address_id</span></tt> in order to
961
1002
identify the row in <tt class="docutils literal"><span class="pre">Address</span></tt> to be loaded; similarly, <tt class="docutils literal"><span class="pre">shipping_address_id</span></tt>
962
1003
is used for the <tt class="docutils literal"><span class="pre">shipping_address</span></tt> relationship. The linkage of the two
963
1004
columns also plays a role during persistence; the newly generated primary key
964
1005
of a just-inserted <tt class="docutils literal"><span class="pre">Address</span></tt> object will be copied into the appropriate
965
1006
foreign key column of an associated <tt class="docutils literal"><span class="pre">Customer</span></tt> object during a flush.</p>
1007
<p>When specifying <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> with Declarative, we can also use string
1008
names to specify, however it is important that if using a list, the <strong>list
1009
is part of the string</strong>:</p>
1010
<div class="highlight-python"><div class="highlight"><pre><span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="s">"[Customer.billing_address_id]"</span><span class="p">)</span></pre></div>
1012
<p>In this specific example, the list is not necessary in any case as there’s only
1013
one <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> we need:</p>
1014
<div class="highlight-python"><div class="highlight"><pre><span class="n">billing_address</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"Address"</span><span class="p">,</span> <span class="n">foreign_keys</span><span class="o">=</span><span class="s">"Customer.billing_address_id"</span><span class="p">)</span></pre></div>
1016
<p class="versionchanged">
1017
<span class="versionmodified">Changed in version 0.8: </span><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> can resolve ambiguity between foreign key targets on the
1018
basis of the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> argument alone; the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> argument is no
1019
longer needed in this situation.</p>
966
1021
<div class="section" id="specifying-alternate-join-conditions">
967
<h3>Specifying Alternate Join Conditions<a class="headerlink" href="#specifying-alternate-join-conditions" title="Permalink to this headline">¶</a></h3>
968
<p>The open-ended nature of <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> also allows us to customize how
969
related items are loaded. In the example below, using the <tt class="docutils literal"><span class="pre">User</span></tt> class
1022
<span id="relationship-primaryjoin"></span><h3>Specifying Alternate Join Conditions<a class="headerlink" href="#specifying-alternate-join-conditions" title="Permalink to this headline">¶</a></h3>
1023
<p>The default behavior 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> when constructing a join
1024
is that it equates the value of primary key columns
1025
on one side to that of foreign-key-referring columns on the other.
1026
We can change this criterion to be anything we’d like using the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt>
1027
argument, as well as the <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> argument in the case when
1028
a “secondary” table is used.</p>
1029
<p>In the example below, using the <tt class="docutils literal"><span class="pre">User</span></tt> class
970
1030
as well as an <tt class="docutils literal"><span class="pre">Address</span></tt> class which stores a street address, we
971
1031
create a relationship <tt class="docutils literal"><span class="pre">boston_addresses</span></tt> which will only
972
1032
load those <tt class="docutils literal"><span class="pre">Address</span></tt> objects which specify a city of “Boston”:</p>
1000
1060
to just <tt class="docutils literal"><span class="pre">city='Boston'</span></tt>. When using Declarative, rudimentary SQL functions like
1001
1061
<a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression.and_" title="sqlalchemy.sql.expression.and_"><tt class="xref py py-func docutils literal"><span class="pre">and_()</span></tt></a> are automatically available in the evaluated namespace of a string
1002
1062
<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> argument.</p>
1003
<p>When using classical mappings, we have the advantage of 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
1004
already being present when the mapping is defined, so that the SQL expression
1005
can be created immediately:</p>
1006
<div class="highlight-python"><div class="highlight"><pre><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">mapper</span>
1008
<span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
1009
<span class="k">pass</span>
1010
<span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="nb">object</span><span class="p">):</span>
1011
<span class="k">pass</span>
1013
<span class="n">mapper</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">addresses_table</span><span class="p">)</span>
1014
<span class="n">mapper</span><span class="p">(</span><span class="n">User</span><span class="p">,</span> <span class="n">users_table</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
1015
<span class="s">'boston_addresses'</span><span class="p">:</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span> <span class="n">primaryjoin</span><span class="o">=</span>
1016
<span class="n">and_</span><span class="p">(</span><span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
1017
<span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">city</span><span class="o">==</span><span class="s">'Boston'</span><span class="p">))</span>
1018
<span class="p">})</span></pre></div>
1020
<p>Note that the custom criteria we use in a <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> is generally only significant
1063
<p>The custom criteria we use in a <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> is generally only significant
1021
1064
when SQLAlchemy is rendering SQL in order to load or represent this relationship.
1022
1065
That is, it’s used
1023
1066
in the SQL statement that’s emitted in order to perform a per-attribute lazy load, or when a join is
1031
1074
for each row. The <tt class="docutils literal"><span class="pre">city</span></tt> criteria has no effect here, as the flush process only cares about synchronizing primary
1032
1075
key values into referencing foreign key values.</p>
1077
<div class="section" id="creating-custom-foreign-conditions">
1078
<span id="relationship-custom-foreign"></span><h3>Creating Custom Foreign Conditions<a class="headerlink" href="#creating-custom-foreign-conditions" title="Permalink to this headline">¶</a></h3>
1079
<p>Another element of the primary join condition is how those columns
1080
considered “foreign” are determined. Usually, some subset
1081
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 will specify <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>, or otherwise
1082
be part of a <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a> that’s relevant to the join condition.
1083
<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> looks to this foreign key status as it decides
1084
how it should load and persist data for this relationship. However, the
1085
<tt class="docutils literal"><span class="pre">primaryjoin</span></tt> argument can be used to create a join condition that
1086
doesn’t involve any “schema” level foreign keys. We can combine <tt class="docutils literal"><span class="pre">primaryjoin</span></tt>
1087
along with <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> and <tt class="docutils literal"><span class="pre">remote_side</span></tt> explicitly in order to
1088
establish such a join.</p>
1089
<p>Below, a class <tt class="docutils literal"><span class="pre">HostEntry</span></tt> joins to itself, equating the string <tt class="docutils literal"><span class="pre">content</span></tt>
1090
column to the <tt class="docutils literal"><span class="pre">ip_address</span></tt> column, which is a Postgresql type called <tt class="docutils literal"><span class="pre">INET</span></tt>.
1091
We need to use <a class="reference internal" href="../core/expression_api.html#sqlalchemy.sql.expression.cast" title="sqlalchemy.sql.expression.cast"><tt class="xref py py-func docutils literal"><span class="pre">cast()</span></tt></a> in order to cast one side of the join to the
1092
type of the other:</p>
1093
<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">cast</span><span class="p">,</span> <span class="n">String</span><span class="p">,</span> <span class="n">Column</span><span class="p">,</span> <span class="n">Integer</span>
1094
<span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">relationship</span>
1095
<span class="kn">from</span> <span class="nn">sqlalchemy.dialects.postgresql</span> <span class="kn">import</span> <span class="n">INET</span>
1097
<span class="kn">from</span> <span class="nn">sqlalchemy.ext.declarative</span> <span class="kn">import</span> <span class="n">declarative_base</span>
1099
<span class="n">Base</span> <span class="o">=</span> <span class="n">declarative_base</span><span class="p">()</span>
1101
<span class="k">class</span> <span class="nc">HostEntry</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
1102
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'host_entry'</span>
1104
<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>
1105
<span class="n">ip_address</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">INET</span><span class="p">)</span>
1106
<span class="n">content</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>
1108
<span class="c"># relationship() using explicit foreign_keys, remote_side</span>
1109
<span class="n">parent_host</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"HostEntry"</span><span class="p">,</span>
1110
<span class="n">primaryjoin</span><span class="o">=</span><span class="n">ip_address</span> <span class="o">==</span> <span class="n">cast</span><span class="p">(</span><span class="n">content</span><span class="p">,</span> <span class="n">INET</span><span class="p">),</span>
1111
<span class="n">foreign_keys</span><span class="o">=</span><span class="n">content</span><span class="p">,</span>
1112
<span class="n">remote_side</span><span class="o">=</span><span class="n">ip_address</span>
1113
<span class="p">)</span></pre></div>
1115
<p>The above relationship will produce a join like:</p>
1116
<div class="highlight-python"><pre>SELECT host_entry.id, host_entry.ip_address, host_entry.content
1117
FROM host_entry JOIN host_entry AS host_entry_1
1118
ON host_entry_1.ip_address = CAST(host_entry.content AS INET)</pre>
1120
<p>An alternative syntax to the above is to use the <a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a> and
1121
<a class="reference internal" href="#sqlalchemy.orm.remote" title="sqlalchemy.orm.remote"><tt class="xref py py-func docutils literal"><span class="pre">remote()</span></tt></a> <a class="reference internal" href="../glossary.html#term-annotations"><em class="xref std std-term">annotations</em></a>, inline within the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> expression.
1122
This syntax represents the annotations 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> normally
1123
applies by itself to the join condition given the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> and
1124
<tt class="docutils literal"><span class="pre">remote_side</span></tt> arguments; the functions are provided in the API in the
1125
rare case 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> can’t determine the exact location
1126
of these features on its own:</p>
1127
<div class="highlight-python"><div class="highlight"><pre><span class="kn">from</span> <span class="nn">sqlalchemy.orm</span> <span class="kn">import</span> <span class="n">foreign</span><span class="p">,</span> <span class="n">remote</span>
1129
<span class="k">class</span> <span class="nc">HostEntry</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
1130
<span class="n">__tablename__</span> <span class="o">=</span> <span class="s">'host_entry'</span>
1132
<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>
1133
<span class="n">ip_address</span> <span class="o">=</span> <span class="n">Column</span><span class="p">(</span><span class="n">INET</span><span class="p">)</span>
1134
<span class="n">content</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>
1136
<span class="c"># relationship() using explicit foreign() and remote() annotations</span>
1137
<span class="c"># in lieu of separate arguments</span>
1138
<span class="n">parent_host</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="s">"HostEntry"</span><span class="p">,</span>
1139
<span class="n">primaryjoin</span><span class="o">=</span><span class="n">remote</span><span class="p">(</span><span class="n">ip_address</span><span class="p">)</span> <span class="o">==</span> \
1140
<span class="n">cast</span><span class="p">(</span><span class="n">foreign</span><span class="p">(</span><span class="n">content</span><span class="p">),</span> <span class="n">INET</span><span class="p">),</span>
1141
<span class="p">)</span></pre></div>
1034
1144
<div class="section" id="self-referential-many-to-many-relationship">
1035
1145
<span id="self-referential-many-to-many"></span><h3>Self-Referential Many-to-Many Relationship<a class="headerlink" href="#self-referential-many-to-many-relationship" title="Permalink to this headline">¶</a></h3>
1036
1146
<p>Many to many relationships can be customized by one or both of <tt class="docutils literal"><span class="pre">primaryjoin</span></tt>
1097
1207
backref - when <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> creates the second relationship in the reverse
1098
1208
direction, it’s smart enough to reverse the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt> arguments.</p>
1100
<div class="section" id="specifying-foreign-keys">
1101
<h3>Specifying Foreign Keys<a class="headerlink" href="#specifying-foreign-keys" title="Permalink to this headline">¶</a></h3>
1102
<p>When using <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> and <tt class="docutils literal"><span class="pre">secondaryjoin</span></tt>, SQLAlchemy also needs to be
1103
aware of which columns in the relationship reference the other. In most cases,
1104
a <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> construct will have
1105
<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> constructs which take care of this;
1106
however, in the case of reflected tables on a database that does not report
1107
FKs (like MySQL ISAM) or when using join conditions on columns that don’t have
1108
foreign keys, 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> needs to be told
1109
specifically which columns are “foreign” using the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt>
1111
<div class="highlight-python+sql"><div class="highlight"><pre><span class="k">class</span> <span class="nc">Address</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
1112
<span class="n">__table__</span> <span class="o">=</span> <span class="n">addresses_table</span>
1114
<span class="k">class</span> <span class="nc">User</span><span class="p">(</span><span class="n">Base</span><span class="p">):</span>
1115
<span class="n">__table__</span> <span class="o">=</span> <span class="n">users_table</span>
1116
<span class="n">addresses</span> <span class="o">=</span> <span class="n">relationship</span><span class="p">(</span><span class="n">Address</span><span class="p">,</span>
1117
<span class="n">primaryjoin</span><span class="o">=</span>
1118
<span class="n">users_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="o">==</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">,</span>
1119
<span class="n">foreign_keys</span><span class="o">=</span><span class="p">[</span><span class="n">addresses_table</span><span class="o">.</span><span class="n">c</span><span class="o">.</span><span class="n">user_id</span><span class="p">])</span></pre></div>
1122
1210
<div class="section" id="building-query-enabled-properties">
1123
1211
<h3>Building Query-Enabled Properties<a class="headerlink" href="#building-query-enabled-properties" title="Permalink to this headline">¶</a></h3>
1124
1212
<p>Very ambitious custom join conditions may fail to be directly persistable, and
1364
1450
<p>Some arguments accepted by <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> optionally accept a
1365
1451
callable function, which when called produces the desired value.
1366
The callable is invoked by the parent <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> at “mapper initialization”
1367
time, which happens only when mappers are first used, and is assumed
1368
to be after all mappings have been constructed. This can be used
1369
to resolve order-of-declaration and other dependency issues, such as
1452
The callable is invoked by the parent <a class="reference internal" href="mapper_config.html#sqlalchemy.orm.mapper.Mapper" title="sqlalchemy.orm.mapper.Mapper"><tt class="xref py py-class docutils literal"><span class="pre">Mapper</span></tt></a> at “mapper
1453
initialization” time, which happens only when mappers are first used, and
1454
is assumed to be after all mappings have been constructed. This can be
1455
used to resolve order-of-declaration and other dependency issues, such as
1370
1456
if <tt class="docutils literal"><span class="pre">Child</span></tt> is declared below <tt class="docutils literal"><span class="pre">Parent</span></tt> in the same file:</p>
1371
1457
<div class="highlight-python"><div class="highlight"><pre><span class="n">mapper</span><span class="p">(</span><span class="n">Parent</span><span class="p">,</span> <span class="n">properties</span><span class="o">=</span><span class="p">{</span>
1372
1458
<span class="s">"children"</span><span class="p">:</span><span class="n">relationship</span><span class="p">(</span><span class="k">lambda</span><span class="p">:</span> <span class="n">Child</span><span class="p">,</span>
1503
1589
which will be prepended to the list of attribute listeners for
1504
1590
the resulting descriptor placed on the class.
1505
1591
<strong>Deprecated.</strong> Please see <a class="reference internal" href="events.html#sqlalchemy.orm.events.AttributeEvents" title="sqlalchemy.orm.events.AttributeEvents"><tt class="xref py py-class docutils literal"><span class="pre">AttributeEvents</span></tt></a>.</li>
1506
<li><strong>foreign_keys</strong> – <p>a list of columns which are to be used as “foreign key” columns.
1507
Normally, <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> uses 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>
1508
and <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a> objects present within the
1509
mapped or secondary <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> to determine the “foreign” side of
1510
the join condition. This is used to construct SQL clauses in order
1511
to load objects, as well as to “synchronize” values from
1512
primary key columns to referencing foreign key columns.
1513
The <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> parameter overrides the notion of what’s
1514
“foreign” in the table metadata, allowing the specification
1515
of a list 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 that should be considered
1516
part of the foreign key.</p>
1517
<p>There are only two use cases for <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> - one, when it is not
1518
convenient for <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> metadata to contain its own foreign key
1519
metadata (which should be almost never, unless reflecting a large amount of
1520
tables from a MySQL MyISAM schema, or a schema that doesn’t actually
1521
have foreign keys on it). The other is for extremely
1522
rare and exotic composite foreign key setups where some columns
1523
should artificially not be considered as foreign.</p>
1592
<li><strong>foreign_keys</strong> – <p>a list of columns which are to be used as “foreign key” columns,
1593
or columns which refer to the value in a remote column, within the
1594
context of this <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> object’s <tt class="docutils literal"><span class="pre">primaryjoin</span></tt>
1595
condition. That is, if the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> condition of this
1596
<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 <tt class="docutils literal"><span class="pre">a.id</span> <span class="pre">==</span> <span class="pre">b.a_id</span></tt>, and the values in <tt class="docutils literal"><span class="pre">b.a_id</span></tt>
1597
are required to be present in <tt class="docutils literal"><span class="pre">a.id</span></tt>, then the “foreign key” column
1598
of this <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 <tt class="docutils literal"><span class="pre">b.a_id</span></tt>.</p>
1599
<p>In normal cases, the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> parameter is <strong>not required.</strong>
1600
<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> will <strong>automatically</strong> determine which columns
1601
in the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> conditition are to be considered “foreign key”
1602
columns based on those <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 that specify
1603
<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>, or are otherwise listed as referencing columns
1604
in a <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a> construct. <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> is only
1607
<div><ol class="arabic">
1608
<li>There is more than one way to construct a join from the local
1609
table to the remote table, as there are multiple foreign key
1610
references present. Setting <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> will limit the
1611
<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> to consider just those columns specified
1612
here as “foreign”.<p class="versionchanged">
1613
<span class="versionmodified">Changed in version 0.8: </span>A multiple-foreign key join ambiguity can be resolved by
1614
setting the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> parameter alone, without the
1615
need to explicitly set <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> as well.</p>
1617
<li>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> being mapped does not actually have
1618
<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> or <a class="reference internal" href="../core/schema.html#sqlalchemy.schema.ForeignKeyConstraint" title="sqlalchemy.schema.ForeignKeyConstraint"><tt class="xref py py-class docutils literal"><span class="pre">ForeignKeyConstraint</span></tt></a>
1619
constructs present, often because the table
1620
was reflected from a database that does not support foreign key
1621
reflection (MySQL MyISAM).</li>
1622
<li>The <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> argument is used to construct a non-standard
1623
join condition, which makes use of columns or expressions that do
1624
not normally refer to their “parent” column, such as a join condition
1625
expressed by a complex comparison using a SQL function.</li>
1628
<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> construct will raise informative error messages
1629
that suggest the use of the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> parameter when presented
1630
with an ambiguous condition. In typical cases, if <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>
1631
doesn’t raise any exceptions, the <tt class="docutils literal"><span class="pre">foreign_keys</span></tt> parameter is usually
1524
1633
<p><tt class="docutils literal"><span class="pre">foreign_keys</span></tt> may also be passed as a callable function
1525
1634
which is evaluated at mapper initialization time, and may be passed as a
1526
1635
Python-evaluable string when using Declarative.</p>
1636
<div class="admonition-see-also admonition seealso">
1637
<p class="first admonition-title">See also</p>
1638
<p><a class="reference internal" href="#relationship-foreign-keys"><em>Handling Multiple Join Paths</em></a></p>
1639
<p><a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a></p>
1640
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a> - allows direct annotation of the “foreign” columns
1641
within a <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> condition.</p>
1643
<p class="versionadded">
1644
<span class="versionmodified">New in version 0.8: </span>The <a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a> annotation can also be applied
1645
directly to the <tt class="docutils literal"><span class="pre">primaryjoin</span></tt> expression, which is an alternate,
1646
more specific system of describing which columns in a particular
1647
<tt class="docutils literal"><span class="pre">primaryjoin</span></tt> should be considered “foreign”.</p>
1649
<li><strong>info</strong> – <p>Optional data dictionary which will be populated into the
1650
<a class="reference internal" href="internals.html#sqlalchemy.orm.interfaces.MapperProperty.info" title="sqlalchemy.orm.interfaces.MapperProperty.info"><tt class="xref py py-attr docutils literal"><span class="pre">MapperProperty.info</span></tt></a> attribute of this object.</p>
1651
<p class="versionadded">
1652
<span class="versionmodified">New in version 0.8.</span></p>
1528
1654
<li><strong>innerjoin=False</strong> – <p>when <tt class="docutils literal"><span class="pre">True</span></tt>, joined eager loads will use an inner join to join
1529
1655
against related tables instead of an outer join. The purpose
1578
1703
<p>Detailed discussion of loader strategies is at <a class="reference internal" href="loading.html"><em>Relationship Loading Techniques</em></a>.</p>
1580
1705
<li><strong>load_on_pending=False</strong> – <p>Indicates loading behavior for transient or pending parent objects.</p>
1706
<p class="versionchanged">
1707
<span class="versionmodified">Changed in version 0.8: </span>load_on_pending is superseded by
1708
<a class="reference internal" href="session.html#sqlalchemy.orm.session.Session.enable_relationship_loading" title="sqlalchemy.orm.session.Session.enable_relationship_loading"><tt class="xref py py-meth docutils literal"><span class="pre">Session.enable_relationship_loading()</span></tt></a>.</p>
1581
1709
<p>When set to <tt class="docutils literal"><span class="pre">True</span></tt>, causes the lazy-loader to
1582
1710
issue a query for a parent object that is not persistent, meaning it has
1583
1711
never been flushed. This may take effect for a pending object when
1584
1712
autoflush is disabled, or for a transient object that has been
1585
1713
“attached” to a <a class="reference internal" href="session.html#sqlalchemy.orm.session.Session" title="sqlalchemy.orm.session.Session"><tt class="xref py py-class docutils literal"><span class="pre">Session</span></tt></a> but is not part of its pending
1586
collection. Attachment of transient objects to the session without
1587
moving to the “pending” state is not a supported behavior at this time.</p>
1588
<p>Note that the load of related objects on a pending or transient object
1589
also does not trigger any attribute change events - no user-defined
1590
events will be emitted for these attributes, and if and when the
1591
object is ultimately flushed, only the user-specific foreign key
1592
attributes will be part of the modified state.</p>
1593
1715
<p>The load_on_pending flag does not improve behavior
1594
1716
when the ORM is used normally - object references should be constructed
1595
1717
at the object level, not at the foreign key level, so that they
1596
1718
are present in an ordinary way before flush() proceeds. This flag
1597
1719
is not not intended for general use.</p>
1598
<p>New in 0.6.5.</p>
1720
<p class="versionadded">
1721
<span class="versionmodified">New in version 0.6.5.</span></p>
1600
1723
<li><strong>order_by</strong> – <p>indicates the ordering that should be applied when loading these
1601
1724
items. <tt class="docutils literal"><span class="pre">order_by</span></tt> is expected to refer to one of the <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>
1851
<p class="versionchanged">
1852
<span class="versionmodified">Changed in version 0.6: </span><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> was renamed from its previous name
1853
<a class="reference internal" href="#sqlalchemy.orm.relation" title="sqlalchemy.orm.relation"><tt class="xref py py-func docutils literal"><span class="pre">relation()</span></tt></a>.</p>
1725
1856
<dl class="function">
1726
1857
<dt id="sqlalchemy.orm.backref">
1727
1858
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">backref</tt><big>(</big><em>name</em>, <em>**kwargs</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.backref" title="Permalink to this definition">¶</a></dt>
1728
<dd><p>Create a back reference with explicit keyword arguments, which are the same
1729
arguments one can send to <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>
1859
<dd><p>Create a back reference with explicit keyword arguments, which are the
1860
same arguments one can send to <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>
1730
1861
<p>Used with the <tt class="docutils literal"><span class="pre">backref</span></tt> keyword argument to <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> in
1731
1862
place of a string argument, e.g.:</p>
1732
1863
<div class="highlight-python"><pre>'items':relationship(SomeItem, backref=backref('parent', lazy='subquery'))</pre>
1755
1886
on dynamic loading.</p>
1889
<dl class="function">
1890
<dt id="sqlalchemy.orm.foreign">
1891
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">foreign</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.foreign" title="Permalink to this definition">¶</a></dt>
1892
<dd><p>Annotate a portion of a primaryjoin expression
1893
with a ‘foreign’ annotation.</p>
1894
<p>See the section <a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a> for a
1895
description of use.</p>
1896
<p class="versionadded">
1897
<span class="versionmodified">New in version 0.8.</span></p>
1898
<div class="admonition-see-also admonition seealso">
1899
<p class="first admonition-title">See also</p>
1900
<p><a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a></p>
1901
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.remote" title="sqlalchemy.orm.remote"><tt class="xref py py-func docutils literal"><span class="pre">remote()</span></tt></a></p>
1905
<dl class="function">
1906
<dt id="sqlalchemy.orm.remote">
1907
<tt class="descclassname">sqlalchemy.orm.</tt><tt class="descname">remote</tt><big>(</big><em>expr</em><big>)</big><a class="headerlink" href="#sqlalchemy.orm.remote" title="Permalink to this definition">¶</a></dt>
1908
<dd><p>Annotate a portion of a primaryjoin expression
1909
with a ‘remote’ annotation.</p>
1910
<p>See the section <a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a> for a
1911
description of use.</p>
1912
<p class="versionadded">
1913
<span class="versionmodified">New in version 0.8.</span></p>
1914
<div class="admonition-see-also admonition seealso">
1915
<p class="first admonition-title">See also</p>
1916
<p><a class="reference internal" href="#relationship-custom-foreign"><em>Creating Custom Foreign Conditions</em></a></p>
1917
<p class="last"><a class="reference internal" href="#sqlalchemy.orm.foreign" title="sqlalchemy.orm.foreign"><tt class="xref py py-func docutils literal"><span class="pre">foreign()</span></tt></a></p>