Linking Relationships with Backref

    The above configuration establishes a collection of Address objects on User called User.addresses. It also establishes a .user attribute on Address which will refer to the parent User object.

    In fact, the relationship.backref keyword is only a common shortcut for placing a second onto the Address mapping, including the establishment of an event listener on both sides which will mirror attribute operations in both directions. The above configuration is equivalent to:

    1. from sqlalchemy import Integer, ForeignKey, String, Column
    2. from sqlalchemy.ext.declarative import declarative_base
    3. from sqlalchemy.orm import relationship
    5. Base = declarative_base()
    7. class User(Base):
    8.     __tablename__ = 'user'
    9.     id = Column(Integer, primary_key=True)
    10.     name = Column(String)
    12.     addresses = relationship("Address", back_populates="user")
    14. class Address(Base):
    15.     __tablename__ = 'address'
    16.     id = Column(Integer, primary_key=True)
    17.     email = Column(String)
    18.     user_id = Column(Integer, ForeignKey('user.id'))
    20.     user = relationship("User", back_populates="addresses")

    Above, we add a .user relationship to Address explicitly. On both relationships, the relationship.back_populates directive tells each relationship about the other one, indicating that they should establish “bidirectional” behavior between each other. The primary effect of this configuration is that the relationship adds event handlers to both attributes which have the behavior of “when an append or set event occurs here, set ourselves onto the incoming attribute using this particular attribute name”. The behavior is illustrated as follows. Start with a User and an Address instance. The .addresses collection is empty, and the .user attribute is None:

    1. >>> u1 = User()
    2. >>> a1 = Address()
    3. >>> u1.addresses
    4. []
    5. >>> print(a1.user)
    6. None

    However, once the Address is appended to the u1.addresses collection, both the collection and the scalar attribute have been populated:

    1. >>> u1.addresses.append(a1)
    2. [<__main__.Address object at 0x12a6ed0>]
    3. >>> a1.user
    4. <__main__.User object at 0x12a6590>

    This behavior of course works in reverse for removal operations as well, as well as for equivalent operations on both sides. Such as when .user is set again to None, the Address object is removed from the reverse collection:

    1. >>> a1.user = None
    2. >>> u1.addresses
    3. []

    The manipulation of the .addresses collection and the .user attribute occurs entirely in Python without any interaction with the SQL database. Without this behavior, the proper state would be apparent on both sides once the data has been flushed to the database, and later reloaded after a commit or expiration operation occurs. The / behavior has the advantage that common bidirectional operations can reflect the correct state without requiring a database round trip.

    Remember, when the keyword is used on a single relationship, it’s exactly the same as if the above two relationships were created individually using relationship.back_populates on each.

    We’ve established that the keyword is merely a shortcut for building two individual relationship() constructs that refer to each other. Part of the behavior of this shortcut is that certain configurational arguments applied to the will also be applied to the other direction - namely those arguments that describe the relationship at a schema level, and are unlikely to be different in the reverse direction. The usual case here is a many-to-many relationship() that has a argument, or a one-to-many or many-to-one which has a relationship.primaryjoin argument (the argument is discussed in Specifying Alternate Join Conditions). Such as if we limited the list of Address objects to those which start with “tony”:

    1. >>> print(User.addresses.property.primaryjoin)
    2. "user".id = address.user_id AND address.email LIKE :email_1 || '%%'
    3. >>>
    4. >>> print(Address.user.property.primaryjoin)
    5. "user".id = address.user_id AND address.email LIKE :email_1 || '%%'
    6. >>>

    This reuse of arguments should pretty much do the “right thing” - it uses only arguments that are applicable, and in the case of a many-to- many relationship, will reverse the usage of and relationship.secondaryjoin to correspond to the other direction (see the example in for this).

    It’s very often the case however that we’d like to specify arguments that are specific to just the side where we happened to place the “backref”. This includes relationship() arguments like , relationship.remote_side, and relationship.cascade_backrefs. For this case we use the function in place of a string:

    1. # <other imports>
    2. from sqlalchemy.orm import backref
    4. class User(Base):
    5.     __tablename__ = 'user'
    6.     id = Column(Integer, primary_key=True)
    7.     name = Column(String)
    9.     addresses = relationship("Address",
    10.                     backref=backref("user", lazy="joined"))

    Where above, we placed a lazy="joined" directive only on the Address.user side, indicating that when a query against Address is made, a join to the User entity should be made automatically which will populate the .user attribute of each returned Address. The backref() function formatted the arguments we gave it into a form that is interpreted by the receiving as additional arguments to be applied to the new relationship it creates.

    A key behavior that occurs in the 1.x series of SQLAlchemy regarding backrefs is that cascades will occur bidirectionally by default. This basically means, if one starts with an User object that’s been persisted in the :

    1. user = session.query(User).filter(User.id == 1).first()

    The above User is persistent in the . It usually is intuitive that if we create an Address object and append to the User.addresses collection, it is automatically added to the Session as in the example below:

    1. user = session.query(User).filter(User.id == 1).first()
    2. address = Address(email_address='foo')
    3. user.addresses.append(address)

    The above behavior is known as the “save update cascade” and is described in the section .

    However, if we instead created a new Address object, and associated the User object with the Address as follows:

    In the above example, it is not as intuitive that the Address would automatically be added to the Session. However, the backref behavior of Address.user indicates that the Address object is also appended to the User.addresses collection. This in turn initiates a cascade operation which indicates that this should be placed into the as a pending object.

    1. class User(Base):
    2.     # ...
    4.     addresses = relationship("Address", back_populates="user", cascade_backrefs=False)

    See the example in for further information.

    See also

    Controlling Cascade on Backrefs.

    An unusual case is that of the “one way backref”. This is where the “back-populating” behavior of the backref is only desirable in one direction. An example of this is a collection which contains a filtering condition. We’d like to append items to this collection as needed, and have them populate the “parent” object on the incoming object. However, we’d also like to have items that are not part of the collection, but still have the same “parent” association - these items should never be in the collection.

    Taking our previous example, where we established a relationship.primaryjoin that limited the collection only to Address objects whose email address started with the word tony, the usual backref behavior is that all items populate in both directions. We wouldn’t want this behavior for a case like the following:

    1. >>> a1 = Address(email='mary')
    2. >>> a1.user = u1
    3. >>> u1.addresses
    4. [<__main__.Address object at 0x1411910>]

    Above, the Address object that doesn’t match the criterion of “starts with ‘tony’” is present in the addresses collection of u1. After these objects are flushed, the transaction committed and their attributes expired for a re-load, the addresses collection will hit the database on next access and no longer have this Address object present, due to the filtering condition. But we can do away with this unwanted side of the “backref” behavior on the Python side by using two separate constructs, placing relationship.back_populates only on one side:

    1. from sqlalchemy import Integer, ForeignKey, String, Column
    2. from sqlalchemy.ext.declarative import declarative_base
    3. from sqlalchemy.orm import relationship
    5. Base = declarative_base()
    7. class User(Base):
    8.     __tablename__ = 'user'
    9.     id = Column(Integer, primary_key=True)
    10.     name = Column(String)
    11.     addresses = relationship("Address",
    12.                     primaryjoin="and_(User.id==Address.user_id, "
    13.                         "Address.email.startswith('tony'))",
    14.                     back_populates="user")
    16. class Address(Base):
    17.     __tablename__ = 'address'
    18.     id = Column(Integer, primary_key=True)
    19.     email = Column(String)
    20.     user_id = Column(Integer, ForeignKey('user.id'))
    21.     user = relationship("User")

    With the above scenario, appending an Address object to the .addresses collection of a User will always establish the .user attribute on that Address:

    1. >>> u1 = User()
    2. >>> a1 = Address(email='tony')
    3. >>> u1.addresses.append(a1)
    4. >>> a1.user
    5. <__main__.User object at 0x1411850>

    However, applying a User to the .user attribute of an Address, will not append the Address object to the collection: