Configuring Relationships

See also

This section describes specifics about how the Declarative systeminteracts with SQLAlchemy ORM relationship constructs. For generalinformation about setting up relationships between mappings,see Object Relational Tutorial and Basic Relationship Patterns.

Relationships to other classes are done in the usual way, with the addedfeature that the class specified to relationship()may be a string name. The “class registry” associated with Baseis used at mapper compilation time to resolve the name into the actualclass object, which is expected to have been defined once the mapperconfiguration is used:

class User(Base):
    __tablename__ = 'users'
 
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    addresses = relationship("Address", backref="user")
 
class Address(Base):
    __tablename__ = 'addresses'
 
    id = Column(Integer, primary_key=True)
    email = Column(String(50))
    user_id = Column(Integer, ForeignKey('users.id'))

Column constructs, since they are just that, are immediately usable,as below where we define a primary join condition on the Addressclass using them:

class Address(Base):
    __tablename__ = 'addresses'
 
    id = Column(Integer, primary_key=True)
    email = Column(String(50))
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship(User, primaryjoin=user_id == User.id)

In addition to the main argument for relationship(),other arguments which depend upon the columns present on an as-yetundefined class may also be specified as strings. These strings areevaluated as Python expressions. The full namespace available withinthis evaluation includes all classes mapped for this declarative base,as well as the contents of the sqlalchemy package, includingexpression functions like desc() andfunc:

class User(Base):
    # ....
    addresses = relationship("Address",
                         order_by="desc(Address.email)",
                         primaryjoin="Address.user_id==User.id")

For the case where more than one module contains a class of the same name,string class names can also be specified as module-qualified pathswithin any of these string expressions:

class User(Base):
    # ....
    addresses = relationship("myapp.model.address.Address",
                         order_by="desc(myapp.model.address.Address.email)",
                         primaryjoin="myapp.model.address.Address.user_id=="
                                        "myapp.model.user.User.id")

The qualified path can be any partial path that removes ambiguity betweenthe names. For example, to disambiguate betweenmyapp.model.address.Address and myapp.model.lookup.Address,we can specify address.Address or lookup.Address:

class User(Base):
    # ....
    addresses = relationship("address.Address",
                         order_by="desc(address.Address.email)",
                         primaryjoin="address.Address.user_id=="
                                        "User.id")

Two alternatives also exist to using string-based attributes. A lambdacan also be used, which will be evaluated after all mappers have beenconfigured:

class User(Base):
    # ...
    addresses = relationship(lambda: Address,
                         order_by=lambda: desc(Address.email),
                         primaryjoin=lambda: Address.user_id==User.id)

Or, the relationship can be added to the class explicitly after the classesare available:

User.addresses = relationship(Address,
                          primaryjoin=Address.user_id==User.id)

Configuring Many-to-Many Relationships

Many-to-many relationships are also declared in the same waywith declarative as with traditional mappings. Thesecondary argument torelationship() is as usual passed aTable object, which is typically declared in thetraditional way. The Table usually sharesthe MetaData object used by the declarative base:

keywords = Table(
    'keywords', Base.metadata,
    Column('author_id', Integer, ForeignKey('authors.id')),
    Column('keyword_id', Integer, ForeignKey('keywords.id'))
    )
 
class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    keywords = relationship("Keyword", secondary=keywords)

Like other relationship() arguments, a string is acceptedas well, passing the string name of the table as defined in theBase.metadata.tables collection:

class Author(Base):
    __tablename__ = 'authors'
    id = Column(Integer, primary_key=True)
    keywords = relationship("Keyword", secondary="keywords")

As with traditional mapping, its generally not a good idea to usea Table as the “secondary” argument which is also mapped toa class, unless the relationship() is declared with viewonly=True.Otherwise, the unit-of-work system may attempt duplicate INSERT andDELETE statements against the underlying table.