1 files changed, 108 insertions, 0 deletions
diff --git a/examples/join_conditions/threeway.py b/examples/join_conditions/threeway.py
new file mode 100644
index 000000000..13df0f349
--- /dev/null
+++ b/examples/join_conditions/threeway.py
@@ -0,0 +1,108 @@
+"""Illustrate a "three way join" - where a primary table joins to a remote
+table via an association table, but then the primary table also needs
+to refer to some columns in the remote table directly.
+
+E.g.::
+
+    first.first_id      -> second.first_id
+                           second.other_id --> partitioned.other_id
+    first.partition_key ---------------------> partitioned.partition_key
+
+For a relationship like this, "second" is a lot like a "secondary" table,
+but the mechanics aren't present within the "secondary" feature to allow
+for the join directly between first and partitioned.  Instead, we
+will derive a selectable from partitioned and second combined together, then
+link first to that derived selectable.
+
+If we define the derived selectable as::
+
+    second JOIN partitioned ON second.other_id = partitioned.other_id
+
+A JOIN from first to this derived selectable is then::
+
+    first JOIN (second JOIN partitioned
+                ON second.other_id = partitioned.other_id)
+          ON first.first_id = second.first_id AND
+             first.partition_key = partitioned.partition_key
+
+We will use the "non primary mapper" feature in order to produce this.
+A non primary mapper is essentially an "extra" :func:`.mapper` that we can
+use to associate a particular class with some selectable that is
+not its usual mapped table.   It is used only when called upon within
+a Query (or a :func:`.relationship`).
+
+
+"""
+from sqlalchemy import *
+from sqlalchemy.orm import *
+from sqlalchemy.ext.declarative import declarative_base
+
+Base = declarative_base()
+
+class First(Base):
+    __tablename__ = 'first'
+
+    first_id = Column(Integer, primary_key=True)
+    partition_key = Column(String)
+
+    def __repr__(self):
+        return ("First(%s, %s)" % (self.first_id, self.partition_key))
+
+class Second(Base):
+    __tablename__ = 'second'
+
+    first_id = Column(Integer, primary_key=True)
+    other_id = Column(Integer, primary_key=True)
+
+class Partitioned(Base):
+    __tablename__ = 'partitioned'
+
+    other_id = Column(Integer, primary_key=True)
+    partition_key = Column(String, primary_key=True)
+
+    def __repr__(self):
+        return ("Partitioned(%s, %s)" % (self.other_id, self.partition_key))
+
+
+j = join(Partitioned, Second, Partitioned.other_id == Second.other_id)
+
+partitioned_second = mapper(Partitioned, j, non_primary=True, properties={
+        # note we need to disambiguate columns here - the join()
+        # will provide them as j.c.<tablename>_<colname> for access,
+        # but they retain their real names in the mapping
+        "other_id": [j.c.partitioned_other_id, j.c.second_other_id],
+    })
+
+First.partitioned = relationship(
+                            partitioned_second,
+                            primaryjoin=and_(
+                                First.partition_key == partitioned_second.c.partition_key,
+                                First.first_id == foreign(partitioned_second.c.first_id)
+                            ), innerjoin=True)
+
+# when using any database other than SQLite, we will get a nested
+# join, e.g. "first JOIN (partitioned JOIN second ON ..) ON ..".
+# On SQLite, SQLAlchemy needs to render a full subquery.
+e = create_engine("sqlite://", echo=True)
+
+Base.metadata.create_all(e)
+s = Session(e)
+s.add_all([
+    First(first_id=1, partition_key='p1'),
+    First(first_id=2, partition_key='p1'),
+    First(first_id=3, partition_key='p2'),
+    Second(first_id=1, other_id=1),
+    Second(first_id=2, other_id=1),
+    Second(first_id=3, other_id=2),
+    Partitioned(partition_key='p1', other_id=1),
+    Partitioned(partition_key='p1', other_id=2),
+    Partitioned(partition_key='p2', other_id=2),
+])
+s.commit()
+
+for row in s.query(First, Partitioned).join(First.partitioned):
+    print(row)
+
+for f in s.query(First):
+    for p in f.partitioned:
+        print(f.partition_key, p.partition_key)