From d080aae128b081e6870dae325cb90202329784b4 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Wed, 17 Oct 2018 10:42:50 -0400
Subject: [PATCH] Add prop.secondary to FROM for dynamic loader

Fixed bug where "dynamic" loader needs to explicitly set the "secondary"
table in the FROM clause of the query, to suit the case where the secondary
is a join object that is otherwise not pulled into the query from its
columns alone.

Fixes: #4349
Change-Id: I397f62abd5603efa4fb273586d0f772bf8c8fbbf
---
 doc/build/changelog/unreleased_12/4349.rst |  9 +++++++
 lib/sqlalchemy/orm/dynamic.py              |  5 ++++
 test/orm/test_dynamic.py                   | 30 ++++++++++++++++++++++
 3 files changed, 44 insertions(+)
 create mode 100644 doc/build/changelog/unreleased_12/4349.rst

diff --git a/doc/build/changelog/unreleased_12/4349.rst b/doc/build/changelog/unreleased_12/4349.rst
new file mode 100644
index 0000000000..a2ce828cb9
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/4349.rst
@@ -0,0 +1,9 @@
+.. change::
+   :tags: bug, orm
+   :tickets: 4349
+
+   Fixed bug where "dynamic" loader needs to explicitly set the "secondary"
+   table in the FROM clause of the query, to suit the case where the secondary
+   is a join object that is otherwise not pulled into the query from its
+   columns alone.
+
diff --git a/lib/sqlalchemy/orm/dynamic.py b/lib/sqlalchemy/orm/dynamic.py
index 73d9ef3bb7..3c59f61d70 100644
--- a/lib/sqlalchemy/orm/dynamic.py
+++ b/lib/sqlalchemy/orm/dynamic.py
@@ -219,6 +219,10 @@ class AppenderMixin(object):
 
         mapper = object_mapper(instance)
         prop = mapper._props[self.attr.key]
+
+        if prop.secondary is not None:
+            self._set_select_from([prop.secondary], False)
+
         self._criterion = prop._with_parent(
             instance,
             alias_secondary=False)
@@ -284,6 +288,7 @@ class AppenderMixin(object):
             query = sess.query(self.attr.target_mapper)
 
         query._criterion = self._criterion
+        query._from_obj = self._from_obj
         query._order_by = self._order_by
 
         return query
diff --git a/test/orm/test_dynamic.py b/test/orm/test_dynamic.py
index 8dd805f529..5c514ff1a5 100644
--- a/test/orm/test_dynamic.py
+++ b/test/orm/test_dynamic.py
@@ -262,6 +262,36 @@ class DynamicTest(_DynamicFixture, _fixtures.FixtureTest, AssertsCompiledSQL):
             [Item(id=2)]
         )
 
+    def test_secondary_as_join(self):
+        User, users = self.classes.User, self.tables.users
+        items, orders, order_items, Item = (self.tables.items,
+                                            self.tables.orders,
+                                            self.tables.order_items,
+                                            self.classes.Item)
+
+        mapper(User, users, properties={
+            'items': relationship(
+                Item,
+                secondary=order_items.join(orders),
+                lazy="dynamic"
+            )
+        })
+        mapper(Item, items)
+
+        sess = create_session()
+        u1 = sess.query(User).first()
+
+        self.assert_compile(
+            u1.items,
+            "SELECT items.id AS items_id, "
+            "items.description AS items_description "
+            "FROM items, order_items JOIN orders "
+            "ON orders.id = order_items.order_id "
+            "WHERE :param_1 = orders.user_id "
+            "AND items.id = order_items.item_id",
+            use_default_dialect=True
+        )
+
     def test_transient_count(self):
         User, Address = self._user_address_fixture()
         u1 = User()
-- 
2.47.2