From 96bd8fe1a53cb47dcd41ea11469eb863b4fed73a Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Mon, 14 Jun 2021 10:26:28 -0400
Subject: [PATCH] clarify relationship to aliased class criteria

the AC itself has to be used in additional WHERE/ORDER BY
etc, not the original class.

Change-Id: I66a0086349257cb281940e743c0f45e6c2e9e282
---
 doc/build/orm/join_conditions.rst | 47 +++++++++++++++++++++++++++++++
 1 file changed, 47 insertions(+)

diff --git a/doc/build/orm/join_conditions.rst b/doc/build/orm/join_conditions.rst
index 21fa5e7310..e3e5965f22 100644
--- a/doc/build/orm/join_conditions.rst
+++ b/doc/build/orm/join_conditions.rst
@@ -777,6 +777,8 @@ the rows in both ``A`` and ``B`` simultaneously::
         id = Column(Integer, primary_key=True)
         a_id = Column(ForeignKey('a.id'))
 
+        some_c_value = Column(String)
+
     class D(Base):
         __tablename__ = 'd'
 
@@ -784,6 +786,8 @@ the rows in both ``A`` and ``B`` simultaneously::
         c_id = Column(ForeignKey('c.id'))
         b_id = Column(ForeignKey('b.id'))
 
+        some_d_value = Column(String)
+
     # 1. set up the join() as a variable, so we can refer
     # to it in the mapping multiple times.
     j = join(B, D, D.b_id == B.id).join(C, C.id == D.c_id)
@@ -802,6 +806,49 @@ With the above mapping, a simple join looks like:
     {opensql}SELECT a.id AS a_id, a.b_id AS a_b_id
     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
 
+Using the AliasedClass target in Queries
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+In the previous example, the ``A.b`` relationship refers to the ``B_viacd``
+entity as the target, and **not** the ``B`` class directly. To add additional
+criteria involving the ``A.b`` relationship, it's typically necessary to
+reference the ``B_viacd`` directly rather than using ``B``, especially in a
+case where the target entity of ``A.b`` is to be transformed into an alias or a
+subquery. Below illustrates the same relationship using a subquery, rather than
+a join::
+
+    subq = select(B).join(D, D.b_id == B.id).join(C, C.id == D.c_id).subquery()
+
+    B_viacd_subquery = aliased(B, subq)
+
+    A.b = relationship(B_viacd_subquery, primaryjoin=A.b_id == subq.c.id)
+
+A query using the above ``A.b`` relationship will render a subquery:
+
+.. sourcecode:: python+sql
+
+    sess.query(A).join(A.b).all()
+
+    {opensql}SELECT a.id AS a_id, a.b_id AS a_b_id
+    FROM a JOIN (SELECT b.id AS id, b.some_b_column AS some_b_column
+    FROM b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) AS anon_1 ON a.b_id = anon_1.id
+
+If we want to add additional criteria based on the ``A.b`` join, we must do
+so in terms of ``B_viacd_subquery`` rather than ``B`` directly:
+
+.. sourcecode:: python+sql
+
+    (
+      sess.query(A).join(A.b).
+      filter(B_viacd_subquery.some_b_column == "some b").
+      order_by(B_viacd_subquery.id)
+    ).all()
+
+    {opensql}SELECT a.id AS a_id, a.b_id AS a_b_id
+    FROM a JOIN (SELECT b.id AS id, b.some_b_column AS some_b_column
+    FROM b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) AS anon_1 ON a.b_id = anon_1.id
+    WHERE anon_1.some_b_column = ? ORDER BY anon_1.id
+
 .. _relationship_to_window_function:
 
 Row-Limited Relationships with Window Functions
-- 
2.47.2