Configuring how Relationship Joins

relationship() will normally create a join between two tablesby examining the foreign key relationship between the two tablesto determine which columns should be compared. There are a varietyof situations where this behavior needs to be customized.

Handling Multiple Join Paths

One of the most common situations to deal with is whenthere are more than one foreign key path between two tables.

Consider a Customer class that contains two foreign keys to an Addressclass:

  1. from sqlalchemy import Integer, ForeignKey, String, Column
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import relationship
  4.  
  5. Base = declarative_base()
  6.  
  7. class Customer(Base):
  8.     __tablename__ = 'customer'
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String)
  11.  
  12.     billing_address_id = Column(Integer, ForeignKey("address.id"))
  13.     shipping_address_id = Column(Integer, ForeignKey("address.id"))
  14.  
  15.     billing_address = relationship("Address")
  16.     shipping_address = relationship("Address")
  17.  
  18. class Address(Base):
  19.     __tablename__ = 'address'
  20.     id = Column(Integer, primary_key=True)
  21.     street = Column(String)
  22.     city = Column(String)
  23.     state = Column(String)
  24.     zip = Column(String)

The above mapping, when we attempt to use it, will produce the error:

  1. sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join
  2. condition between parent/child tables on relationship
  3. Customer.billing_address - there are multiple foreign key
  4. paths linking the tables.  Specify the 'foreign_keys' argument,
  5. providing a list of those columns which should be
  6. counted as containing a foreign key reference to the parent table.

The above message is pretty long. There are many potential messagesthat relationship() can return, which have been carefully tailoredto detect a variety of common configurational issues; most will suggestthe additional configuration that’s needed to resolve the ambiguityor other missing information.

In this case, the message wants us to qualify each relationship()by instructing for each one which foreign key column should be considered, andthe appropriate form is as follows:

  1. class Customer(Base):
  2.     __tablename__ = 'customer'
  3.     id = Column(Integer, primary_key=True)
  4.     name = Column(String)
  5.  
  6.     billing_address_id = Column(Integer, ForeignKey("address.id"))
  7.     shipping_address_id = Column(Integer, ForeignKey("address.id"))
  8.  
  9.     billing_address = relationship("Address", foreign_keys=[billing_address_id])
  10.     shipping_address = relationship("Address", foreign_keys=[shipping_address_id])

Above, we specify the foreign_keys argument, which is a Column or listof Column objects which indicate those columns to be considered “foreign”,or in other words, the columns that contain a value referring to a parent table.Loading the Customer.billing_address relationship from a Customerobject will use the value present in billing_address_id in order toidentify the row in Address to be loaded; similarly, shipping_address_idis used for the shipping_address relationship. The linkage of the twocolumns also plays a role during persistence; the newly generated primary keyof a just-inserted Address object will be copied into the appropriateforeign key column of an associated Customer object during a flush.

When specifying foreign_keys with Declarative, we can also use stringnames to specify, however it is important that if using a list, the listis part of the string:

  1. billing_address = relationship("Address", foreign_keys="[Customer.billing_address_id]")

In this specific example, the list is not necessary in any case as there’s onlyone Column we need:

  1. billing_address = relationship("Address", foreign_keys="Customer.billing_address_id")

Specifying Alternate Join Conditions

The default behavior of relationship() when constructing a joinis that it equates the value of primary key columnson one side to that of foreign-key-referring columns on the other.We can change this criterion to be anything we’d like using theprimaryjoinargument, as well as the secondaryjoinargument in the case when a “secondary” table is used.

In the example below, using the User classas well as an Address class which stores a street address, wecreate a relationship boston_addresses which will onlyload those Address objects which specify a city of “Boston”:

  1. from sqlalchemy import Integer, ForeignKey, String, Column
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import relationship
  4.  
  5. Base = declarative_base()
  6.  
  7. class User(Base):
  8.     __tablename__ = 'user'
  9.     id = Column(Integer, primary_key=True)
  10.     name = Column(String)
  11.     boston_addresses = relationship("Address",
  12.                     primaryjoin="and_(User.id==Address.user_id, "
  13.                         "Address.city=='Boston')")
  14.  
  15. class Address(Base):
  16.     __tablename__ = 'address'
  17.     id = Column(Integer, primary_key=True)
  18.     user_id = Column(Integer, ForeignKey('user.id'))
  19.  
  20.     street = Column(String)
  21.     city = Column(String)
  22.     state = Column(String)
  23.     zip = Column(String)

Within this string SQL expression, we made use of the and_() conjunction construct to establishtwo distinct predicates for the join condition - joining both the User.id andAddress.userid columns to each other, as well as limiting rows in Addressto just city='Boston'. When using Declarative, rudimentary SQL functions like[and()]($46af60d0cbd5331b.md#sqlalchemy.sql.expression.and_) are automatically available in the evaluated namespace of a stringrelationship() argument.

The custom criteria we use in a primaryjoinis generally only significant when SQLAlchemy is rendering SQL inorder to load or represent this relationship. That is, it’s used inthe SQL statement that’s emitted in order to perform a per-attributelazy load, or when a join is constructed at query time, such as viaQuery.join(), or via the eager “joined” or “subquery” styles ofloading. When in-memory objects are being manipulated, we can placeany Address object we’d like into the boston_addressescollection, regardless of what the value of the .city attributeis. The objects will remain present in the collection until theattribute is expired and re-loaded from the database where thecriterion is applied. When a flush occurs, the objects inside ofboston_addresses will be flushed unconditionally, assigning valueof the primary key user.id column onto the foreign-key-holdingaddress.user_id column for each row. The city criteria has noeffect here, as the flush process only cares about synchronizingprimary key values into referencing foreign key values.

Creating Custom Foreign Conditions

Another element of the primary join condition is how those columnsconsidered “foreign” are determined. Usually, some subsetof Column objects will specify ForeignKey, or otherwisebe part of a ForeignKeyConstraint that’s relevant to the join condition.relationship() looks to this foreign key status as it decideshow it should load and persist data for this relationship. However, theprimaryjoin argument can be used to create a join condition thatdoesn’t involve any “schema” level foreign keys. We can combine primaryjoinalong with foreign_keys and remote_side explicitly in order toestablish such a join.

Below, a class HostEntry joins to itself, equating the string contentcolumn to the ip_address column, which is a PostgreSQL type called INET.We need to use cast() in order to cast one side of the join to thetype of the other:

  1. from sqlalchemy import cast, String, Column, Integer
  2. from sqlalchemy.orm import relationship
  3. from sqlalchemy.dialects.postgresql import INET
  4.  
  5. from sqlalchemy.ext.declarative import declarative_base
  6.  
  7. Base = declarative_base()
  8.  
  9. class HostEntry(Base):
  10.     __tablename__ = 'host_entry'
  11.  
  12.     id = Column(Integer, primary_key=True)
  13.     ip_address = Column(INET)
  14.     content = Column(String(50))
  15.  
  16.     # relationship() using explicit foreign_keys, remote_side
  17.     parent_host = relationship("HostEntry",
  18.                         primaryjoin=ip_address == cast(content, INET),
  19.                         foreign_keys=content,
  20.                         remote_side=ip_address
  21.                     )

The above relationship will produce a join like:

  1. SELECT host_entry.id, host_entry.ip_address, host_entry.content
  2. FROM host_entry JOIN host_entry AS host_entry_1
  3. ON host_entry_1.ip_address = CAST(host_entry.content AS INET)

An alternative syntax to the above is to use the foreign() andremote()annotations,inline within the primaryjoin expression.This syntax represents the annotations that relationship() normallyapplies by itself to the join condition given the foreign_keys andremote_side arguments. These functions maybe more succinct when an explicit join condition is present, and additionallyserve to mark exactly the column that is “foreign” or “remote” independentof whether that column is stated multiple times or within complexSQL expressions:

  1. from sqlalchemy.orm import foreign, remote
  2.  
  3. class HostEntry(Base):
  4.     __tablename__ = 'host_entry'
  5.  
  6.     id = Column(Integer, primary_key=True)
  7.     ip_address = Column(INET)
  8.     content = Column(String(50))
  9.  
  10.     # relationship() using explicit foreign() and remote() annotations
  11.     # in lieu of separate arguments
  12.     parent_host = relationship("HostEntry",
  13.                         primaryjoin=remote(ip_address) == \
  14.                                 cast(foreign(content), INET),
  15.                     )

Using custom operators in join conditions

Another use case for relationships is the use of custom operators, suchas PostgreSQL’s “is contained within” << operator when joining withtypes such as postgresql.INET and postgresql.CIDR.For custom operators we use the Operators.op() function:

  1. inet_column.op("<<")(cidr_column)

However, if we construct a primaryjoin using thisoperator, relationship() will still need more information. This is becausewhen it examines our primaryjoin condition, it specifically looks for operatorsused for comparisons, and this is typically a fixed list containing knowncomparison operators such as ==, <, etc. So for our custom operatorto participate in this system, we need it to register as a comparison operatorusing the is_comparison parameter:

  1. inet_column.op("<<", is_comparison=True)(cidr_column)

A complete example:

  1. class IPA(Base):
  2.     __tablename__ = 'ip_address'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     v4address = Column(INET)
  6.  
  7.     network = relationship("Network",
  8.                         primaryjoin="IPA.v4address.op('<<', is_comparison=True)"
  9.                             "(foreign(Network.v4representation))",
  10.                         viewonly=True
  11.                     )
  12. class Network(Base):
  13.     __tablename__ = 'network'
  14.  
  15.     id = Column(Integer, primary_key=True)
  16.     v4representation = Column(CIDR)

Above, a query such as:

  1. session.query(IPA).join(IPA.network)

Will render as:

  1. SELECT ip_address.id AS ip_address_id, ip_address.v4address AS ip_address_v4address
  2. FROM ip_address JOIN network ON ip_address.v4address << network.v4representation

New in version 0.9.2: - Added the Operators.op.is_comparisonflag to assist in the creation of relationship() constructs usingcustom operators.

Custom operators based on SQL functions

A variant to the use case for is_comparison iswhen we aren’t using an operator, but a SQL function. The typical exampleof this use case is the PostgreSQL PostGIS functions however any SQLfunction on any database that resolves to a binary condition may apply.To suit this use case, the FunctionElement.as_comparison() methodcan modify any SQL function, such as those invoked from the funcnamespace, to indicate to the ORM that the function produces a comparison oftwo expressions. The below example illustrates this with theGeoalchemy2 library:

  1. from geoalchemy2 import Geometry
  2. from sqlalchemy import Column, Integer, func
  3. from sqlalchemy.orm import relationship, foreign
  4.  
  5. class Polygon(Base):
  6.     __tablename__ = "polygon"
  7.     id = Column(Integer, primary_key=True)
  8.     geom = Column(Geometry("POLYGON", srid=4326))
  9.     points = relationship(
  10.         "Point",
  11.         primaryjoin="func.ST_Contains(foreign(Polygon.geom), Point.geom).as_comparison(1, 2)",
  12.         viewonly=True,
  13.     )
  14.  
  15. class Point(Base):
  16.     __tablename__ = "point"
  17.     id = Column(Integer, primary_key=True)
  18.     geom = Column(Geometry("POINT", srid=4326))

Above, the FunctionElement.as_comparison() indicates that thefunc.ST_Contains() SQL function is comparing the Polygon.geom andPoint.geom expressions. The foreign() annotation additionally noteswhich column takes on the “foreign key” role in this particular relationship.

New in version 1.3: Added FunctionElement.as_comparison().

Overlapping Foreign Keys

A rare scenario can arise when composite foreign keys are used, such thata single column may be the subject of more than one columnreferred to via foreign key constraint.

Consider an (admittedly complex) mapping such as the Magazine object,referred to both by the Writer object and the Article objectusing a composite primary key scheme that includes magazine_idfor both; then to make Article refer to Writer as well,Article.magazine_id is involved in two separate relationships;Article.magazine and Article.writer:

  1. class Magazine(Base):
  2.     __tablename__ = 'magazine'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.  
  6.  
  7. class Article(Base):
  8.     __tablename__ = 'article'
  9.  
  10.     article_id = Column(Integer)
  11.     magazine_id = Column(ForeignKey('magazine.id'))
  12.     writer_id = Column()
  13.  
  14.     magazine = relationship("Magazine")
  15.     writer = relationship("Writer")
  16.  
  17.     __table_args__ = (
  18.         PrimaryKeyConstraint('article_id', 'magazine_id'),
  19.         ForeignKeyConstraint(
  20.             ['writer_id', 'magazine_id'],
  21.             ['writer.id', 'writer.magazine_id']
  22.         ),
  23.     )
  24.  
  25.  
  26. class Writer(Base):
  27.     __tablename__ = 'writer'
  28.  
  29.     id = Column(Integer, primary_key=True)
  30.     magazine_id = Column(ForeignKey('magazine.id'), primary_key=True)
  31.     magazine = relationship("Magazine")

When the above mapping is configured, we will see this warning emitted:

  1. SAWarning: relationship 'Article.writer' will copy column
  2. writer.magazine_id to column article.magazine_id,
  3. which conflicts with relationship(s): 'Article.magazine'
  4. (copies magazine.id to article.magazine_id). Consider applying
  5. viewonly=True to read-only relationships, or provide a primaryjoin
  6. condition marking writable columns with the foreign() annotation.

What this refers to originates from the fact that Article.magazineid isthe subject of two different foreign key constraints; it refers toMagazine.id directly as a source column, but also refers toWriter.magazine_id as a source column in the context of thecomposite key to Writer. If we associate an Article with aparticular Magazine, but then associate the Article with aWriter that’s associated with a _different Magazine, the ORMwill overwrite Article.magazine_id non-deterministically, silentlychanging which magazine we refer towards; it mayalso attempt to place NULL into this column if we de-associate aWriter from an Article. The warning lets us know this is the case.

To solve this, we need to break out the behavior of Article to includeall three of the following features:

  • Article first and foremost writes toArticle.magazine_id based on data persisted in the Article.magazinerelationship only, that is a value copied from Magazine.id.

  • Article can write to Article.writer_id on behalf of datapersisted in the Article.writer relationship, but only theWriter.id column; the Writer.magazine_id column should notbe written into Article.magazine_id as it ultimately is sourcedfrom Magazine.id.

  • Article takes Article.magazineid into account when loadingArticle.writer, even though it _doesn’t write to it on behalfof this relationship.

To get just #1 and #2, we could specify only Article.writer_id as the“foreign keys” for Article.writer:

  1. class Article(Base):
  2.     # ...
  3.  
  4.     writer = relationship("Writer", foreign_keys='Article.writer_id')

However, this has the effect of Article.writer not takingArticle.magazine_id into account when querying against Writer:

  1. SELECT article.article_id AS article_article_id,
  2.     article.magazine_id AS article_magazine_id,
  3.     article.writer_id AS article_writer_id
  4. FROM article
  5. JOIN writer ON writer.id = article.writer_id

Therefore, to get at all of #1, #2, and #3, we express the join conditionas well as which columns to be written by combiningprimaryjoin fully, along with either theforeign_keys argument, or more succinctly byannotating with foreign():

  1. class Article(Base):
  2.     # ...
  3.  
  4.     writer = relationship(
  5.         "Writer",
  6.         primaryjoin="and_(Writer.id == foreign(Article.writer_id), "
  7.                     "Writer.magazine_id == Article.magazine_id)")

Changed in version 1.0.0: the ORM will attempt to warn when a column is usedas the synchronization target from more than one relationshipsimultaneously.

Non-relational Comparisons / Materialized Path

Warning

this section details an experimental feature.

Using custom expressions means we can produce unorthodox join conditions thatdon’t obey the usual primary/foreign key model. One such example is thematerialized path pattern, where we compare strings for overlapping path tokensin order to produce a tree structure.

Through careful use of foreign() and remote(), we can builda relationship that effectively produces a rudimentary materialized pathsystem. Essentially, when foreign() and remote() areon the same side of the comparison expression, the relationship is consideredto be “one to many”; when they are on different sides, the relationshipis considered to be “many to one”. For the comparison we’ll use here,we’ll be dealing with collections so we keep things configured as “one to many”:

  1. class Element(Base):
  2.     __tablename__ = 'element'
  3.  
  4.     path = Column(String, primary_key=True)
  5.  
  6.     descendants = relationship('Element',
  7.                            primaryjoin=
  8.                                 remote(foreign(path)).like(
  9.                                         path.concat('/%')),
  10.                            viewonly=True,
  11.                            order_by=path)

Above, if given an Element object with a path attribute of "/foo/bar2",we seek for a load of Element.descendants to look like:

  1. SELECT element.path AS element_path
  2. FROM element
  3. WHERE element.path LIKE ('/foo/bar2' || '/%') ORDER BY element.path

New in version 0.9.5: Support has been added to allow a single-columncomparison to itself within a primaryjoin condition, as well as forprimaryjoin conditions that use ColumnOperators.like() as the comparisonoperator.

Self-Referential Many-to-Many Relationship

Many to many relationships can be customized by one or both of primaryjoinand secondaryjoin - the latter is significant for a relationship thatspecifies a many-to-many reference using the secondary argument.A common situation which involves the usage of primaryjoin and secondaryjoinis when establishing a many-to-many relationship from a class to itself, as shown below:

  1. from sqlalchemy import Integer, ForeignKey, String, Column, Table
  2. from sqlalchemy.ext.declarative import declarative_base
  3. from sqlalchemy.orm import relationship
  4.  
  5. Base = declarative_base()
  6.  
  7. node_to_node = Table("node_to_node", Base.metadata,
  8.     Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
  9.     Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)
  10. )
  11.  
  12. class Node(Base):
  13.     __tablename__ = 'node'
  14.     id = Column(Integer, primary_key=True)
  15.     label = Column(String)
  16.     right_nodes = relationship("Node",
  17.                         secondary=node_to_node,
  18.                         primaryjoin=id==node_to_node.c.left_node_id,
  19.                         secondaryjoin=id==node_to_node.c.right_node_id,
  20.                         backref="left_nodes"
  21.     )

Where above, SQLAlchemy can’t know automatically which columns should connectto which for the right_nodes and left_nodes relationships. The primaryjoinand secondaryjoin arguments establish how we’d like to join to the association table.In the Declarative form above, as we are declaring these conditions within the Pythonblock that corresponds to the Node class, the id variable is available directlyas the Column object we wish to join with.

Alternatively, we can define the primaryjoinand secondaryjoin arguments using strings, which is suitablein the case that our configuration does not have either the Node.id columnobject available yet or the node_to_node table perhaps isn’t yet available.When referring to a plain Table object in a declarative string, weuse the string name of the table as it is present in the MetaData:

  1. class Node(Base):
  2.     __tablename__ = 'node'
  3.     id = Column(Integer, primary_key=True)
  4.     label = Column(String)
  5.     right_nodes = relationship("Node",
  6.                         secondary="node_to_node",
  7.                         primaryjoin="Node.id==node_to_node.c.left_node_id",
  8.                         secondaryjoin="Node.id==node_to_node.c.right_node_id",
  9.                         backref="left_nodes"
  10.     )

A classical mapping situation here is similar, where node_to_node can be joinedto node.c.id:

  1. from sqlalchemy import Integer, ForeignKey, String, Column, Table, MetaData
  2. from sqlalchemy.orm import relationship, mapper
  3.  
  4. metadata = MetaData()
  5.  
  6. node_to_node = Table("node_to_node", metadata,
  7.     Column("left_node_id", Integer, ForeignKey("node.id"), primary_key=True),
  8.     Column("right_node_id", Integer, ForeignKey("node.id"), primary_key=True)
  9. )
  10.  
  11. node = Table("node", metadata,
  12.     Column('id', Integer, primary_key=True),
  13.     Column('label', String)
  14. )
  15. class Node(object):
  16.     pass
  17.  
  18. mapper(Node, node, properties={
  19.     'right_nodes':relationship(Node,
  20.                         secondary=node_to_node,
  21.                         primaryjoin=node.c.id==node_to_node.c.left_node_id,
  22.                         secondaryjoin=node.c.id==node_to_node.c.right_node_id,
  23.                         backref="left_nodes"
  24.                     )})

Note that in both examples, the backrefkeyword specifies a left_nodes backref - whenrelationship() creates the second relationship in the reversedirection, it’s smart enough to reverse theprimaryjoin andsecondaryjoin arguments.

Composite “Secondary” Joins

Note

This section features some new and experimental features of SQLAlchemy.

Sometimes, when one seeks to build a relationship() between two tablesthere is a need for more than just two or three tables to be involved inorder to join them. This is an area of relationship() where one seeksto push the boundaries of what’s possible, and often the ultimate solution tomany of these exotic use cases needs to be hammered out on the SQLAlchemy mailinglist.

In more recent versions of SQLAlchemy, the secondaryparameter can be used in some of these cases in order to provide a compositetarget consisting of multiple tables. Below is an example of such ajoin condition (requires version 0.9.2 at least to function as is):

  1. class A(Base):
  2.     __tablename__ = 'a'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     b_id = Column(ForeignKey('b.id'))
  6.  
  7.     d = relationship("D",
  8.                 secondary="join(B, D, B.d_id == D.id)."
  9.                             "join(C, C.d_id == D.id)",
  10.                 primaryjoin="and_(A.b_id == B.id, A.id == C.a_id)",
  11.                 secondaryjoin="D.id == B.d_id",
  12.                 uselist=False
  13.                 )
  14.  
  15. class B(Base):
  16.     __tablename__ = 'b'
  17.  
  18.     id = Column(Integer, primary_key=True)
  19.     d_id = Column(ForeignKey('d.id'))
  20.  
  21. class C(Base):
  22.     __tablename__ = 'c'
  23.  
  24.     id = Column(Integer, primary_key=True)
  25.     a_id = Column(ForeignKey('a.id'))
  26.     d_id = Column(ForeignKey('d.id'))
  27.  
  28. class D(Base):
  29.     __tablename__ = 'd'
  30.  
  31.     id = Column(Integer, primary_key=True)

In the above example, we provide all three of secondary,primaryjoin, and secondaryjoin,in the declarative style referring to the named tables a, b, c, ddirectly. A query from A to D looks like:

  1. sess.query(A).join(A.d).all()
  2.  
  3. SELECT a.id AS a_id, a.b_id AS a_b_id
  4. FROM a JOIN (
  5.     b AS b_1 JOIN d AS d_1 ON b_1.d_id = d_1.id
  6.         JOIN c AS c_1 ON c_1.d_id = d_1.id)
  7.     ON a.b_id = b_1.id AND a.id = c_1.a_id JOIN d ON d.id = b_1.d_id

In the above example, we take advantage of being able to stuff multipletables into a “secondary” container, so that we can join across manytables while still keeping things “simple” for relationship(), in thatthere’s just “one” table on both the “left” and the “right” side; thecomplexity is kept within the middle.

New in version 0.9.2: Support is improved for allowing a join()construct to be used directly as the target of the secondaryargument, including support for joins, eager joins and lazy loading,as well as support within declarative to specify complex conditions suchas joins involving class names as targets.

Relationship to Aliased Class

New in version 1.3: The AliasedClass construct can now be specified as thetarget of a relationship(), replacing the previous approachof using non-primary mappers, which had limitations such that they didnot inherit sub-relationships of the mapped entity as well as that theyrequired complex configuration against an alternate selectable. Therecipes in this section are now updated to use AliasedClass.

In the previous section, we illustrated a technique where we usedsecondary in order to place additionaltables within a join condition. There is one complex join case whereeven this technique is not sufficient; when we seek to join from Ato B, making use of any number of C, D, etc. in between,however there are also join conditions between A and Bdirectly. In this case, the join from A to B may bedifficult to express with just a complexprimaryjoin condition, as the intermediarytables may need special handling, and it is also not expressible witha secondary object, since theA->secondary->B pattern does not support any references betweenA and B directly. When this extremely advanced casearises, we can resort to creating a second mapping as a target for therelationship. This is where we use AliasedClass in order to make amapping to a class that includes all the additional tables we need forthis join. In order to produce this mapper as an “alternative” mappingfor our class, we use the aliased() function to produce the newconstruct, then use relationship() against the object as though itwere a plain mapped class.

Below illustrates a relationship() with a simple join from A toB, however the primaryjoin condition is augmented with two additionalentities C and D, which also must have rows that line up withthe rows in both A and B simultaneously:

  1. class A(Base):
  2.     __tablename__ = 'a'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.     b_id = Column(ForeignKey('b.id'))
  6.  
  7. class B(Base):
  8.     __tablename__ = 'b'
  9.  
  10.     id = Column(Integer, primary_key=True)
  11.  
  12. class C(Base):
  13.     __tablename__ = 'c'
  14.  
  15.     id = Column(Integer, primary_key=True)
  16.     a_id = Column(ForeignKey('a.id'))
  17.  
  18. class D(Base):
  19.     __tablename__ = 'd'
  20.  
  21.     id = Column(Integer, primary_key=True)
  22.     c_id = Column(ForeignKey('c.id'))
  23.     b_id = Column(ForeignKey('b.id'))
  24.  
  25. # 1. set up the join() as a variable, so we can refer
  26. # to it in the mapping multiple times.
  27. j = join(B, D, D.b_id == B.id).join(C, C.id == D.c_id)
  28.  
  29. # 2. Create an AliasedClass to B
  30. B_viacd = aliased(B, j, flat=True)
  31.  
  32. A.b = relationship(B_viacd, primaryjoin=A.b_id == j.c.b_id)

With the above mapping, a simple join looks like:

  1. sess.query(A).join(A.b).all()
  2.  
  3. SELECT a.id AS a_id, a.b_id AS a_b_id
  4. FROM a JOIN (b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) ON a.b_id = b.id

Row-Limited Relationships with Window Functions

Another interesting use case for relationships to AliasedClassobjects are situations wherethe relationship needs to join to a specialized SELECT of any form. Onescenario is when the use of a window function is desired, such as to limithow many rows should be returned for a relationship. The example belowillustrates a non-primary mapper relationship that will load the firstten items for each collection:

  1. class A(Base):
  2.     __tablename__ = 'a'
  3.  
  4.     id = Column(Integer, primary_key=True)
  5.  
  6.  
  7. class B(Base):
  8.     __tablename__ = 'b'
  9.     id = Column(Integer, primary_key=True)
  10.     a_id = Column(ForeignKey("a.id"))
  11.  
  12. partition = select([
  13.     B,
  14.     func.row_number().over(
  15.         order_by=B.id, partition_by=B.a_id
  16.     ).label('index')
  17. ]).alias()
  18.  
  19. partitioned_b = aliased(B, partition)
  20.  
  21. A.partitioned_bs = relationship(
  22.     partitioned_b,
  23.     primaryjoin=and_(partitioned_b.a_id == A.id, partition.c.index < 10)
  24. )

We can use the above partitioned_bs relationship with most of the loaderstrategies, such as selectinload():

  1. for a1 in s.query(A).options(selectinload(A.partitioned_bs)):
  2.     print(a1.partitioned_bs)   # <-- will be no more than ten objects

Where above, the “selectinload” query looks like:

  1. SELECT
  2.     a_1.id AS a_1_id, anon_1.id AS anon_1_id, anon_1.a_id AS anon_1_a_id,
  3.     anon_1.data AS anon_1_data, anon_1.index AS anon_1_index
  4. FROM a AS a_1
  5. JOIN (
  6.     SELECT b.id AS id, b.a_id AS a_id, b.data AS data,
  7.     row_number() OVER (PARTITION BY b.a_id ORDER BY b.id) AS index
  8.     FROM b) AS anon_1
  9. ON anon_1.a_id = a_1.id AND anon_1.index < %(index_1)s
  10. WHERE a_1.id IN ( ... primary key collection ...)
  11. ORDER BY a_1.id

Above, for each matching primary key in “a”, we will get the first ten“bs” as ordered by “b.id”. By partitioning on “a_id” we ensure that each“row number” is local to the parent “a_id”.

Such a mapping would ordinarily also include a “plain” relationshipfrom “A” to “B”, for persistence operations as well as when the fullset of “B” objects per “A” is desired.

Building Query-Enabled Properties

Very ambitious custom join conditions may fail to be directly persistable, andin some cases may not even load correctly. To remove the persistence part ofthe equation, use the flag viewonly on therelationship(), which establishes it as a read-onlyattribute (data written to the collection will be ignored on flush()).However, in extreme cases, consider using a regular Python property inconjunction with Query as follows:

  1. class User(Base):
  2.     __tablename__ = 'user'
  3.     id = Column(Integer, primary_key=True)
  4.  
  5.     def _get_addresses(self):
  6.         return object_session(self).query(Address).with_parent(self).filter(...).all()
  7.     addresses = property(_get_addresses)