535
535
FROM nodes LEFT OUTER JOIN nodes AS nodes_2 ON nodes.id = nodes_2.parent_id LEFT OUTER JOIN nodes AS nodes_1 ON nodes_2.id = nodes_1.parent_id
538
Linking relationships with Backref
539
----------------------------------
541
The ``backref`` keyword argument was first introduced in :ref:`ormtutorial_toplevel`, and has been
542
mentioned throughout many of the examples here. What does it actually do ? Let's start
543
with the canonical ``User`` and ``Address`` scenario::
545
from sqlalchemy import Integer, ForeignKey, String, Column
546
from sqlalchemy.ext.declarative import declarative_base
547
from sqlalchemy.orm import relationship
549
Base = declarative_base()
552
__tablename__ = 'user'
553
id = Column(Integer, primary_key=True)
554
name = Column(String)
556
addresses = relationship("Address", backref="user")
559
__tablename__ = 'address'
560
id = Column(Integer, primary_key=True)
561
email = Column(String)
562
user_id = Column(Integer, ForeignKey('user.id'))
564
The above configuration establishes a collection of ``Address`` objects on ``User`` called
565
``User.addresses``. It also establishes a ``.user`` attribute on ``Address`` which will
566
refer to the parent ``User`` object.
568
In fact, the ``backref`` keyword is only a common shortcut for placing a second
569
``relationship`` onto the ``Address`` mapping, including the establishment
570
of an event listener on both sides which will mirror attribute operations
571
in both directions. The above configuration is equivalent to::
573
from sqlalchemy import Integer, ForeignKey, String, Column
574
from sqlalchemy.ext.declarative import declarative_base
575
from sqlalchemy.orm import relationship
577
Base = declarative_base()
580
__tablename__ = 'user'
581
id = Column(Integer, primary_key=True)
582
name = Column(String)
584
addresses = relationship("Address", back_populates="user")
587
__tablename__ = 'address'
588
id = Column(Integer, primary_key=True)
589
email = Column(String)
590
user_id = Column(Integer, ForeignKey('user.id'))
592
user = relationship("User", back_populates="addresses")
594
Above, we add a ``.user`` relationship to ``Address`` explicitly. On
595
both relationships, the ``back_populates`` directive tells each relationship
596
about the other one, indicating that they should establish "bi-directional"
597
behavior between each other. The primary effect of this configuration
598
is that the relationship adds event handlers to both attributes
599
which have the behavior of "when an append or set event occurs here, set ourselves
600
onto the incoming attribute using this particular attribute name".
601
The behavior is illustrated as follows. Start with a ``User`` and an ``Address``
602
instance. The ``.addresses`` collection is empty, and the ``.user`` attribute
612
However, once the ``Address`` is appended to the ``u1.addresses`` collection,
613
both the collection and the scalar attribute have been populated::
615
>>> u1.addresses.append(a1)
617
[<__main__.Address object at 0x12a6ed0>]
619
<__main__.User object at 0x12a6590>
621
This behavior of course works in reverse for removal operations as well, as well
622
as for equivalent operations on both sides. Such as
623
when ``.user`` is set again to ``None``, the ``Address`` object is removed
624
from the reverse collection::
630
The manipulation of the ``.addresses`` collection and the ``.user`` attribute
631
occurs entirely in Python without any interaction with the SQL database.
632
Without this behavior, the proper state would be apparent on both sides once the
633
data has been flushed to the database, and later reloaded after a commit or
634
expiration operation occurs. The ``backref``/``back_populates`` behavior has the advantage
635
that common bidirectional operations can reflect the correct state without requiring
636
a database round trip.
638
Remember, when the ``backref`` keyword is used on a single relationship, it's
639
exactly the same as if the above two relationships were created individually
640
using ``back_populates`` on each.
645
We've established that the ``backref`` keyword is merely a shortcut for building
646
two individual :func:`.relationship` constructs that refer to each other. Part of
647
the behavior of this shortcut is that certain configurational arguments applied to
648
the :func:`.relationship`
649
will also be applied to the other direction - namely those arguments that describe
650
the relationship at a schema level, and are unlikely to be different in the reverse
651
direction. The usual case
652
here is a many-to-many :func:`.relationship` that has a ``secondary`` argument,
653
or a one-to-many or many-to-one which has a ``primaryjoin`` argument (the
654
``primaryjoin`` argument is discussed in :ref:`relationship_primaryjoin`). Such
655
as if we limited the list of ``Address`` objects to those which start with "tony"::
657
from sqlalchemy import Integer, ForeignKey, String, Column
658
from sqlalchemy.ext.declarative import declarative_base
659
from sqlalchemy.orm import relationship
661
Base = declarative_base()
664
__tablename__ = 'user'
665
id = Column(Integer, primary_key=True)
666
name = Column(String)
668
addresses = relationship("Address",
669
primaryjoin="and_(User.id==Address.user_id, "
670
"Address.email.startswith('tony'))",
674
__tablename__ = 'address'
675
id = Column(Integer, primary_key=True)
676
email = Column(String)
677
user_id = Column(Integer, ForeignKey('user.id'))
679
We can observe, by inspecting the resulting property, that both sides
680
of the relationship have this join condition applied::
682
>>> print User.addresses.property.primaryjoin
683
"user".id = address.user_id AND address.email LIKE :email_1 || '%%'
685
>>> print Address.user.property.primaryjoin
686
"user".id = address.user_id AND address.email LIKE :email_1 || '%%'
689
This reuse of arguments should pretty much do the "right thing" - it uses
690
only arguments that are applicable, and in the case of a many-to-many
691
relationship, will reverse the usage of ``primaryjoin`` and ``secondaryjoin``
692
to correspond to the other direction (see the example in :ref:`self_referential_many_to_many`
695
It's very often the case however that we'd like to specify arguments that
696
are specific to just the side where we happened to place the "backref".
697
This includes :func:`.relationship` arguments like ``lazy``, ``remote_side``,
698
``cascade`` and ``cascade_backrefs``. For this case we use the :func:`.backref`
699
function in place of a string::
702
from sqlalchemy.orm import backref
705
__tablename__ = 'user'
706
id = Column(Integer, primary_key=True)
707
name = Column(String)
709
addresses = relationship("Address",
710
backref=backref("user", lazy="joined"))
712
Where above, we placed a ``lazy="joined"`` directive only on the ``Address.user``
713
side, indicating that when a query against ``Address`` is made, a join to the ``User``
714
entity should be made automatically which will populate the ``.user`` attribute of each
715
returned ``Address``. The :func:`.backref` function formatted the arguments we gave
716
it into a form that is interpreted by the receiving :func:`.relationship` as additional
717
arguments to be applied to the new relationship it creates.
722
An unusual case is that of the "one way backref". This is where the "back-populating"
723
behavior of the backref is only desirable in one direction. An example of this
724
is a collection which contains a filtering ``primaryjoin`` condition. We'd like to append
725
items to this collection as needed, and have them populate the "parent" object on the
726
incoming object. However, we'd also like to have items that are not part of the collection,
727
but still have the same "parent" association - these items should never be in the
730
Taking our previous example, where we established a ``primaryjoin`` that limited the
731
collection only to ``Address`` objects whose email address started with the word ``tony``,
732
the usual backref behavior is that all items populate in both directions. We wouldn't
733
want this behavior for a case like the following::
736
>>> a1 = Address(email='mary')
739
[<__main__.Address object at 0x1411910>]
741
Above, the ``Address`` object that doesn't match the criterion of "starts with 'tony'"
742
is present in the ``addresses`` collection of ``u1``. After these objects are flushed,
743
the transaction committed and their attributes expired for a re-load, the ``addresses``
744
collection will hit the database on next access and no longer have this ``Address`` object
745
present, due to the filtering condition. But we can do away with this unwanted side
746
of the "backref" behavior on the Python side by using two separate :func:`.relationship` constructs,
747
placing ``back_populates`` only on one side::
749
from sqlalchemy import Integer, ForeignKey, String, Column
750
from sqlalchemy.ext.declarative import declarative_base
751
from sqlalchemy.orm import relationship
753
Base = declarative_base()
756
__tablename__ = 'user'
757
id = Column(Integer, primary_key=True)
758
name = Column(String)
759
addresses = relationship("Address",
760
primaryjoin="and_(User.id==Address.user_id, "
761
"Address.email.startswith('tony'))",
762
back_populates="user")
765
__tablename__ = 'address'
766
id = Column(Integer, primary_key=True)
767
email = Column(String)
768
user_id = Column(Integer, ForeignKey('user.id'))
769
user = relationship("User")
771
With the above scenario, appending an ``Address`` object to the ``.addresses``
772
collection of a ``User`` will always establish the ``.user`` attribute on that
776
>>> a1 = Address(email='tony')
777
>>> u1.addresses.append(a1)
779
<__main__.User object at 0x1411850>
781
However, applying a ``User`` to the ``.user`` attribute of an ``Address``,
782
will not append the ``Address`` object to the collection::
784
>>> a2 = Address(email='mary')
786
>>> a2 in u1.addresses
789
Of course, we've disabled some of the usefulness of ``backref`` here, in that
790
when we do append an ``Address`` that corresponds to the criteria of ``email.startswith('tony')``,
791
it won't show up in the ``User.addresses`` collection until the session is flushed,
792
and the attributes reloaded after a commit or expire operation. While we could
793
consider an attribute event that checks this criterion in Python, this starts
794
to cross the line of duplicating too much SQL behavior in Python. The backref behavior
795
itself is only a slight transgression of this philosophy - SQLAlchemy tries to keep
796
these to a minimum overall.
798
.. _relationship_primaryjoin:
538
800
Specifying Alternate Join Conditions to relationship()
539
801
------------------------------------------------------
558
820
secondary_table.c.child_id == child_table.c.id --> child_table
561
If you are working with a :class:`~sqlalchemy.schema.Table` which has no
562
:class:`~sqlalchemy.schema.ForeignKey` objects on it (which can be the case
823
If you are working with a :class:`.Table` which has no
824
:class:`.ForeignKey` metadata established (which can be the case
563
825
when using reflected tables with MySQL), or if the join condition cannot be
564
expressed by a simple foreign key relationship, use the ``primaryjoin`` and
565
possibly ``secondaryjoin`` conditions to create the appropriate relationship.
567
In this example we create a relationship ``boston_addresses`` which will only
568
load the user addresses with a city of "Boston":
570
.. sourcecode:: python+sql
826
expressed by a simple foreign key relationship, use the ``primaryjoin``, and
827
for many-to-many relationships ``secondaryjoin``, directives
828
to create the appropriate relationship.
830
In this example, using the ``User`` class as well as an ``Address`` class
831
which stores a street address, we create a relationship ``boston_addresses`` which will only
832
load those ``Address`` objects which specify a city of "Boston"::
834
from sqlalchemy import Integer, ForeignKey, String, Column
835
from sqlalchemy.ext.declarative import declarative_base
836
from sqlalchemy.orm import relationship
838
Base = declarative_base()
841
__tablename__ = 'user'
842
id = Column(Integer, primary_key=True)
843
name = Column(String)
844
addresses = relationship("Address",
845
primaryjoin="and_(User.id==Address.user_id, "
846
"Address.city=='Boston')")
849
__tablename__ = 'address'
850
id = Column(Integer, primary_key=True)
851
user_id = Column(Integer, ForeignKey('user.id'))
853
street = Column(String)
854
city = Column(String)
855
state = Column(String)
858
Note above we specified the ``primaryjoin`` argument as a string - this feature
859
is available only when the mapping is constructed using the Declarative extension,
860
and allows us to specify a full SQL expression
861
between two entities before those entities have been fully constructed. When
862
all mappings have been defined, an automatic "mapper configuration" step interprets
863
these string arguments when first needed.
865
Within this string SQL expression, we also made usage of the :func:`.and_` conjunction construct to establish
866
two distinct predicates for the join condition - joining both the ``User.id`` and
867
``Address.user_id`` columns to each other, as well as limiting rows in ``Address``
868
to just ``city='Boston'``. When using Declarative, rudimentary SQL functions like
869
:func:`.and_` are automatically available in the evaulated namespace of a string
870
:func:`.relationship` argument.
872
When using classical mappings, we have the advantage of the :class:`.Table` objects
873
already being present when the mapping is defined, so that the SQL expression
874
can be created immediately::
876
from sqlalchemy.orm import relationship, mapper
572
878
class User(object):
577
883
mapper(Address, addresses_table)
578
884
mapper(User, users_table, properties={
579
885
'boston_addresses': relationship(Address, primaryjoin=
580
and_(users_table.c.user_id==addresses_table.c.user_id,
886
and_(users_table.c.id==addresses_table.c.user_id,
581
887
addresses_table.c.city=='Boston'))
890
Note that the custom criteria we use in a ``primaryjoin`` is generally only significant
891
when SQLAlchemy is rendering SQL in order to load or represent this relationship.
893
in the SQL statement that's emitted in order to perform a per-attribute lazy load, or when a join is
894
constructed at query time, such as via :meth:`.Query.join`, or via the eager "joined" or "subquery"
895
styles of loading. When in-memory objects are being manipulated, we can place any ``Address`` object
896
we'd like into the ``boston_addresses`` collection, regardless of what the value of the ``.city``
897
attribute is. The objects will remain present in the collection until the attribute is expired
898
and re-loaded from the database where the criterion is applied. When
899
a flush occurs, the objects inside of ``boston_addresses`` will be flushed unconditionally, assigning
900
value of the primary key ``user.id`` column onto the foreign-key-holding ``address.user_id`` column
901
for each row. The ``city`` criteria has no effect here, as the flush process only cares about synchronizing primary
902
key values into referencing foreign key values.
904
.. _self_referential_many_to_many:
906
Self-Referential Many-to-Many Relationship
907
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
584
909
Many to many relationships can be customized by one or both of ``primaryjoin``
585
and ``secondaryjoin``, shown below with just the default many-to-many
586
relationship explicitly set:
588
.. sourcecode:: python+sql
592
class Keyword(object):
594
mapper(Keyword, keywords_table)
595
mapper(User, users_table, properties={
596
'keywords': relationship(Keyword, secondary=userkeywords_table,
597
primaryjoin=users_table.c.user_id==userkeywords_table.c.user_id,
598
secondaryjoin=userkeywords_table.c.keyword_id==keywords_table.c.keyword_id
910
and ``secondaryjoin``. A common situation for custom primary and secondary joins
911
is when establishing a many-to-many relationship from a class to itself, as shown below::
913
from sqlalchemy import Integer, ForeignKey, String, Column, Table
914
from sqlalchemy.ext.declarative import declarative_base
915
from sqlalchemy.orm import relationship
917
Base = declarative_base()
919
node_to_node = Table("node_to_node", Base.metadata,
920
Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
921
Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)
925
__tablename__ = 'node'
926
id = Column(Integer, primary_key=True)
927
label = Column(String)
928
right_nodes = relationship("Node",
929
secondary=node_to_node,
930
primaryjoin=id==node_to_node.c.left_node_id,
931
secondaryjoin=id==node_to_node.c.right_node_id,
935
Where above, SQLAlchemy can't know automatically which columns should connect
936
to which for the ``right_nodes`` and ``left_nodes`` relationships. The ``primaryjoin``
937
and ``secondaryjoin`` arguments establish how we'd like to join to the association table.
938
In the Declarative form above, as we are declaring these conditions within the Python
939
block that corresponds to the ``Node`` class, the ``id`` variable is available directly
940
as the ``Column`` object we wish to join with.
942
A classical mapping situation here is similar, where ``node_to_node`` can be joined
945
from sqlalchemy import Integer, ForeignKey, String, Column, Table, MetaData
946
from sqlalchemy.orm import relationship, mapper
948
metadata = MetaData()
950
node_to_node = Table("node_to_node", metadata,
951
Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
952
Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)
955
node = Table("node", metadata,
956
Column('id', Integer, primary_key=True),
957
Column('label', String)
962
mapper(Node, node, properties={
963
'right_nodes':relationship(Node,
964
secondary=node_to_node,
965
primaryjoin=node.c.id==node_to_node.c.left_node_id,
966
secondaryjoin=node.c.id==node_to_node.c.right_node_id,
971
Note that in both examples, the ``backref`` keyword specifies a ``left_nodes``
972
backref - when :func:`.relationship` creates the second relationship in the reverse
973
direction, it's smart enough to reverse the ``primaryjoin`` and ``secondaryjoin`` arguments.
602
975
Specifying Foreign Keys
603
976
~~~~~~~~~~~~~~~~~~~~~~~~
added
removed
doc/_sources/orm/relationships.txt
