398
400
self-referential strategies; these include :ref:`examples_adjacencylist` and
399
401
:ref:`examples_xmlpersistence`.
403
Composite Adjacency Lists
404
~~~~~~~~~~~~~~~~~~~~~~~~~
406
A sub-category of the adjacency list relationship is the rare
407
case where a particular column is present on both the "local" and
408
"remote" side of the join condition. An example is the ``Folder``
409
class below; using a composite primary key, the ``account_id``
410
column refers to itself, to indicate sub folders which are within
411
the same account as that of the parent; while ``folder_id`` refers
412
to a specific folder within that account::
415
__tablename__ = 'folder'
417
ForeignKeyConstraint(
418
['account_id', 'parent_id'],
419
['folder.account_id', 'folder.folder_id']),
422
account_id = Column(Integer, primary_key=True)
423
folder_id = Column(Integer, primary_key=True)
424
parent_id = Column(Integer)
425
name = Column(String)
427
parent_folder = relationship("Folder",
428
backref="child_folders",
429
remote_side=[account_id, folder_id]
432
Above, we pass ``account_id`` into the ``remote_side`` list.
433
:func:`.relationship` recognizes that the ``account_id`` column here
434
is on both sides, and aligns the "remote" column along with the
435
``folder_id`` column, which it recognizes as uniquely present on
438
.. versionadded:: 0.8
439
Support for self-referential composite keys in :func:`.relationship`
440
where a column points to itself.
401
442
Self-Referential Query Strategies
402
443
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
796
837
itself is only a slight transgression of this philosophy - SQLAlchemy tries to keep
797
838
these to a minimum overall.
799
.. _relationship_primaryjoin:
801
Setting the primaryjoin and secondaryjoin
802
-----------------------------------------
804
A common scenario arises when we attempt to relate two
805
classes together, where there exist multiple ways to join the
840
.. _relationship_configure_joins:
842
Configuring how Relationship Joins
843
------------------------------------
845
:func:`.relationship` will normally create a join between two tables
846
by examining the foreign key relationship between the two tables
847
to determine which columns should be compared. There are a variety
848
of situations where this behavior needs to be customized.
850
.. _relationship_foreign_keys:
852
Handling Multiple Join Paths
853
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
855
One of the most common situations to deal with is when
856
there are more than one foreign key path between two tables.
808
858
Consider a ``Customer`` class that contains two foreign keys to an ``Address``
836
886
The above mapping, when we attempt to use it, will produce the error::
838
sqlalchemy.exc.ArgumentError: Could not determine join condition between
839
parent/child tables on relationship Customer.billing_address. Specify a
840
'primaryjoin' expression. If 'secondary' is present, 'secondaryjoin' is
843
What this error means is that if you have a ``Customer`` object, and wish
844
to load in an associated ``Address``, there is the choice of retrieving
845
the ``Address`` referred to by the ``billing_address_id`` column or the one
846
referred to by the ``shipping_address_id`` column. The :func:`.relationship`,
847
as it is, cannot determine its full configuration. The examples at
848
:ref:`relationship_patterns` didn't have this issue, because in each of those examples
849
there was only **one** way to refer to the related table.
851
To resolve this issue, :func:`.relationship` accepts an argument named
852
``primaryjoin`` which accepts a Python-based SQL expression, using the system described
853
at :ref:`sqlexpression_toplevel`, that describes how the two tables should be joined
854
together. When using the declarative system, we often will specify this Python
855
expression within a string, which is late-evaluated by the mapping configuration
856
system so that it has access to the full namespace of available classes::
888
sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
889
condition between parent/child tables on relationship
890
Customer.billing_address - there are multiple foreign key
891
paths linking the tables. Specify the 'foreign_keys' argument,
892
providing a list of those columns which should be
893
counted as containing a foreign key reference to the parent table.
895
The above message is pretty long. There are many potential messages
896
that :func:`.relationship` can return, which have been carefully tailored
897
to detect a variety of common configurational issues; most will suggest
898
the additional configuration that's needed to resolve the ambiguity
899
or other missing information.
901
In this case, the message wants us to qualify each :func:`.relationship`
902
by instructing for each one which foreign key column should be considered, and
903
the appropriate form is as follows::
858
905
class Customer(Base):
859
906
__tablename__ = 'customer'
863
910
billing_address_id = Column(Integer, ForeignKey("address.id"))
864
911
shipping_address_id = Column(Integer, ForeignKey("address.id"))
866
billing_address = relationship("Address",
867
primaryjoin="Address.id==Customer.billing_address_id")
868
shipping_address = relationship("Address",
869
primaryjoin="Address.id==Customer.shipping_address_id")
913
billing_address = relationship("Address", foreign_keys=[billing_address_id])
914
shipping_address = relationship("Address", foreign_keys=[shipping_address_id])
871
Above, loading the ``Customer.billing_address`` relationship from a ``Customer``
916
Above, we specify the ``foreign_keys`` argument, which is a :class:`.Column` or list
917
of :class:`.Column` objects which indicate those columns to be considered "foreign",
918
or in other words, the columns that contain a value referring to a parent table.
919
Loading the ``Customer.billing_address`` relationship from a ``Customer``
872
920
object will use the value present in ``billing_address_id`` in order to
873
921
identify the row in ``Address`` to be loaded; similarly, ``shipping_address_id``
874
922
is used for the ``shipping_address`` relationship. The linkage of the two
876
924
of a just-inserted ``Address`` object will be copied into the appropriate
877
925
foreign key column of an associated ``Customer`` object during a flush.
927
When specifying ``foreign_keys`` with Declarative, we can also use string
928
names to specify, however it is important that if using a list, the **list
929
is part of the string**::
931
billing_address = relationship("Address", foreign_keys="[Customer.billing_address_id]")
933
In this specific example, the list is not necessary in any case as there's only
934
one :class:`.Column` we need::
936
billing_address = relationship("Address", foreign_keys="Customer.billing_address_id")
938
.. versionchanged:: 0.8
939
:func:`.relationship` can resolve ambiguity between foreign key targets on the
940
basis of the ``foreign_keys`` argument alone; the ``primaryjoin`` argument is no
941
longer needed in this situation.
943
.. _relationship_primaryjoin:
879
945
Specifying Alternate Join Conditions
880
946
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
882
The open-ended nature of ``primaryjoin`` also allows us to customize how
883
related items are loaded. In the example below, using the ``User`` class
948
The default behavior of :func:`.relationship` when constructing a join
949
is that it equates the value of primary key columns
950
on one side to that of foreign-key-referring columns on the other.
951
We can change this criterion to be anything we'd like using the ``primaryjoin``
952
argument, as well as the ``secondaryjoin`` argument in the case when
953
a "secondary" table is used.
955
In the example below, using the ``User`` class
884
956
as well as an ``Address`` class which stores a street address, we
885
957
create a relationship ``boston_addresses`` which will only
886
958
load those ``Address`` objects which specify a city of "Boston"::
916
988
:func:`.and_` are automatically available in the evaluated namespace of a string
917
989
:func:`.relationship` argument.
919
When using classical mappings, we have the advantage of the :class:`.Table` objects
920
already being present when the mapping is defined, so that the SQL expression
921
can be created immediately::
923
from sqlalchemy.orm import relationship, mapper
927
class Address(object):
930
mapper(Address, addresses_table)
931
mapper(User, users_table, properties={
932
'boston_addresses': relationship(Address, primaryjoin=
933
and_(users_table.c.id==addresses_table.c.user_id,
934
addresses_table.c.city=='Boston'))
937
Note that the custom criteria we use in a ``primaryjoin`` is generally only significant
991
The custom criteria we use in a ``primaryjoin`` is generally only significant
938
992
when SQLAlchemy is rendering SQL in order to load or represent this relationship.
939
993
That is, it's used
940
994
in the SQL statement that's emitted in order to perform a per-attribute lazy load, or when a join is
948
1002
for each row. The ``city`` criteria has no effect here, as the flush process only cares about synchronizing primary
949
1003
key values into referencing foreign key values.
1005
.. _relationship_custom_foreign:
1007
Creating Custom Foreign Conditions
1008
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1010
Another element of the primary join condition is how those columns
1011
considered "foreign" are determined. Usually, some subset
1012
of :class:`.Column` objects will specify :class:`.ForeignKey`, or otherwise
1013
be part of a :class:`.ForeignKeyConstraint` that's relevant to the join condition.
1014
:func:`.relationship` looks to this foreign key status as it decides
1015
how it should load and persist data for this relationship. However, the
1016
``primaryjoin`` argument can be used to create a join condition that
1017
doesn't involve any "schema" level foreign keys. We can combine ``primaryjoin``
1018
along with ``foreign_keys`` and ``remote_side`` explicitly in order to
1019
establish such a join.
1021
Below, a class ``HostEntry`` joins to itself, equating the string ``content``
1022
column to the ``ip_address`` column, which is a Postgresql type called ``INET``.
1023
We need to use :func:`.cast` in order to cast one side of the join to the
1026
from sqlalchemy import cast, String, Column, Integer
1027
from sqlalchemy.orm import relationship
1028
from sqlalchemy.dialects.postgresql import INET
1030
from sqlalchemy.ext.declarative import declarative_base
1032
Base = declarative_base()
1034
class HostEntry(Base):
1035
__tablename__ = 'host_entry'
1037
id = Column(Integer, primary_key=True)
1038
ip_address = Column(INET)
1039
content = Column(String(50))
1041
# relationship() using explicit foreign_keys, remote_side
1042
parent_host = relationship("HostEntry",
1043
primaryjoin=ip_address == cast(content, INET),
1044
foreign_keys=content,
1045
remote_side=ip_address
1048
The above relationship will produce a join like::
1050
SELECT host_entry.id, host_entry.ip_address, host_entry.content
1051
FROM host_entry JOIN host_entry AS host_entry_1
1052
ON host_entry_1.ip_address = CAST(host_entry.content AS INET)
1054
An alternative syntax to the above is to use the :func:`.foreign` and
1055
:func:`.remote` :term:`annotations`, inline within the ``primaryjoin`` expression.
1056
This syntax represents the annotations that :func:`.relationship` normally
1057
applies by itself to the join condition given the ``foreign_keys`` and
1058
``remote_side`` arguments; the functions are provided in the API in the
1059
rare case that :func:`.relationship` can't determine the exact location
1060
of these features on its own::
1062
from sqlalchemy.orm import foreign, remote
1064
class HostEntry(Base):
1065
__tablename__ = 'host_entry'
1067
id = Column(Integer, primary_key=True)
1068
ip_address = Column(INET)
1069
content = Column(String(50))
1071
# relationship() using explicit foreign() and remote() annotations
1072
# in lieu of separate arguments
1073
parent_host = relationship("HostEntry",
1074
primaryjoin=remote(ip_address) == \
1075
cast(foreign(content), INET),
951
1079
.. _self_referential_many_to_many:
953
1081
Self-Referential Many-to-Many Relationship
1021
1149
backref - when :func:`.relationship` creates the second relationship in the reverse
1022
1150
direction, it's smart enough to reverse the ``primaryjoin`` and ``secondaryjoin`` arguments.
1024
Specifying Foreign Keys
1025
~~~~~~~~~~~~~~~~~~~~~~~~
1027
When using ``primaryjoin`` and ``secondaryjoin``, SQLAlchemy also needs to be
1028
aware of which columns in the relationship reference the other. In most cases,
1029
a :class:`~sqlalchemy.schema.Table` construct will have
1030
:class:`~sqlalchemy.schema.ForeignKey` constructs which take care of this;
1031
however, in the case of reflected tables on a database that does not report
1032
FKs (like MySQL ISAM) or when using join conditions on columns that don't have
1033
foreign keys, the :func:`~sqlalchemy.orm.relationship` needs to be told
1034
specifically which columns are "foreign" using the ``foreign_keys``
1037
.. sourcecode:: python+sql
1039
class Address(Base):
1040
__table__ = addresses_table
1043
__table__ = users_table
1044
addresses = relationship(Address,
1046
users_table.c.user_id==addresses_table.c.user_id,
1047
foreign_keys=[addresses_table.c.user_id])
1049
1153
Building Query-Enabled Properties
1050
1154
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
added
removed
doc/build/orm/relationships.rst
