- attempt number 2. just add a "correlate_from()" helper. the moreticket_2857

implicit magic we do here, the harder it will be to get the ORM
to also work as expected and the more confusing it will be for everyone.
I've considered making the true() in the join() implicit based on
being passed a Lateral, this could be doable however again in the ORM
this expectation would need to be coded all over the place.

2 files changed, 20 insertions, 203 deletions

diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 9d8ea8a8e..a27ff8d53 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -427,96 +427,9 @@ class FromClause(Selectable):
 
         .. versionadded:: 1.1
 
-        .. seealso::
-
-            :meth:`.FromClause.join_lateral`
-
         """
         return Lateral(self, name)
 
-    def join_lateral(self, right, onclause=None, name=None):
-        """Return a JOIN of this FromClause to a :class:`.Table`
-        or :class:`.Select`, using a correlated SELECT in conjunction with
-        LATERAL.
-
-        E.g.::
-
-
-            subq = select([addresses.c.id])
-
-            stmt = select([users]).select_from(
-                users.join_lateral(subq)
-            )
-
-        Would produce the statement::
-
-            SELECT users.id, users.name, users.fullname
-            FROM users JOIN LATERAL (SELECT addresses.id AS id
-            FROM addresses
-            WHERE users.id = addresses.user_id) AS anon_1 ON true
-
-        If ``right`` is an existing :class:`.Select` object, this
-        call is equivalent to::
-
-            left.join(
-                right.where(onclause).correlate(self).lateral(name),
-                true()
-            )
-
-        If ``right`` is a :class:`.Table`, the call is equivalent to::
-
-            left.join(
-                select([right]).where(onclause).correlate(self).lateral(name),
-                true()
-            )
-
-        If ``right`` is a :class:`.Alias`, the "element" of the :class:`.Alias`
-        is extracted and used as the right side; since we are generating
-        a subquery, there is never a need for the "right" side to itself
-        be an "alias"; the :func:`.lateral` construct provides this.
-
-        The "onclause", if not specified, will be automatically
-        generated from foreign key constraints in the same way as
-        :meth:`.FromClause.join` if omitted; the difference between
-        this an a plain JOIN is that this criteria is used as the
-        WHERE clause of the correlated select.   To use this feature,
-        the right side must refer to an unambiguous FROM clause to
-        join to; if the right side contains multiple FROM clauses besides
-        this one, an exception is raised and the "onclause" should be
-        passed explicitly.
-
-
-        .. versionadded:: 1.1
-
-        .. seealso::
-
-            :meth:`.FromClause.lateral`
-
-        """
-
-        if isinstance(right, Alias):
-            right = right.original
-
-        if isinstance(right, Select):
-            to_select = right
-        else:
-            to_select = Select([right])
-
-        if onclause is None:
-            froms = [
-                f for f in to_select._froms if not f.is_derived_from(self)]
-            if len(froms) != 1:
-                raise exc.ArgumentError(
-                    "select() passed to join_lateral has mulitple "
-                    "FROM clauses; specify the ON clause of this "
-                    "JOIN LATERAL explicitly.")
-            to_onclause = froms[0]
-            onclause = self.join(to_onclause).onclause
-
-        return self.join(to_select.
-                         where(onclause).correlate(self).lateral(name),
-                         True_())
-
     def is_derived_from(self, fromclause):
         """Return True if this FromClause is 'derived' from the given
         FromClause.
@@ -3099,6 +3012,19 @@ class Select(HasPrefixes, HasSuffixes, GenerativeSelect):
         """
         self.append_from(fromclause)
 
+    def correlate_from(self, destination, onclause=None):
+        if onclause is None:
+            froms = [
+                f for f in self._froms if f is not destination]
+            if len(froms) != 1:
+                raise exc.ArgumentError(
+                    "select() has mulitple "
+                    "FROM clauses; specify the ON clause of this "
+                    "correlation explicitly.")
+            to_onclause = froms[0]
+            onclause = destination.join(to_onclause).onclause
+        return self.where(onclause).correlate(destination)
+
     @_generative
     def correlate(self, *fromclauses):
         """return a new :class:`.Select` which will correlate the given FROM
diff --git a/test/sql/test_lateral.py b/test/sql/test_lateral.py
index f101b915b..97ef3d79b 100644
--- a/test/sql/test_lateral.py
+++ b/test/sql/test_lateral.py
@@ -99,52 +99,13 @@ class LateralTest(fixtures.TablesTest, AssertsCompiledSQL):
             "AS alias ON true"
         )
 
-    def test_join_lateral_auto_onclause_table(self):
-        table1 = self.tables.people
-        table2 = self.tables.books
-
-        stmt = select([table1]).select_from(
-            table1.join_lateral(table2)
-        )
-
-        self.assert_compile(
-            stmt,
-            "SELECT people.people_id, people.age, people.name "
-            "FROM people JOIN LATERAL (SELECT books.book_id AS book_id, "
-            "books.bookcase_id AS bookcase_id, books.book_owner_id "
-            "AS book_owner_id, books.book_weight AS book_weight "
-            "FROM books "
-            "WHERE people.people_id = books.book_owner_id) AS anon_1 "
-            "ON true"
-        )
-
-    def test_join_lateral_explicit_onclause_table(self):
-        table1 = self.tables.people
-        table2 = self.tables.books
-
-        stmt = select([table1]).select_from(
-            table1.join_lateral(
-                table2, table1.c.people_id == table2.c.book_owner_id)
-        )
-
-        self.assert_compile(
-            stmt,
-            "SELECT people.people_id, people.age, people.name "
-            "FROM people JOIN LATERAL (SELECT books.book_id AS book_id, "
-            "books.bookcase_id AS bookcase_id, books.book_owner_id "
-            "AS book_owner_id, books.book_weight AS book_weight "
-            "FROM books "
-            "WHERE people.people_id = books.book_owner_id) AS anon_1 "
-            "ON true"
-        )
-
     def test_join_lateral_auto_onclause_subquery(self):
         table1 = self.tables.people
         table2 = self.tables.books
 
-        subq = select([table2.c.book_id])
+        subq = select([table2.c.book_id]).correlate_from(table1).lateral()
         stmt = select([table1]).select_from(
-            table1.join_lateral(subq)
+            table1.join(subq, true())
         )
 
         self.assert_compile(
@@ -155,92 +116,22 @@ class LateralTest(fixtures.TablesTest, AssertsCompiledSQL):
             "WHERE people.people_id = books.book_owner_id) AS anon_1 ON true"
         )
 
-    def test_join_lateral_auto_onclause_aliased_subquery(self):
+    def test_join_lateral_w_select_auto_onclause_subquery(self):
         table1 = self.tables.people
         table2 = self.tables.books
 
-        subq = select([table2.c.book_id]).alias('foo')
-        stmt = select([table1]).select_from(
-            table1.join_lateral(subq)
-        )
+        subq = select([table2.c.book_id]).correlate_from(table1).lateral()
+        stmt = select([table1, subq.c.book_id]).\
+            select_from(table1.join(subq, true()))
 
-        # alias is unwrapped (and anonymized; 'foo' might be used elsewhere)
         self.assert_compile(
             stmt,
-            "SELECT people.people_id, people.age, people.name "
+            "SELECT people.people_id, people.age, people.name, anon_1.book_id "
             "FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
             "FROM books "
             "WHERE people.people_id = books.book_owner_id) AS anon_1 ON true"
         )
 
-    def test_join_lateral_auto_onclause_aliased_subquery_plus_name(self):
-        table1 = self.tables.people
-        table2 = self.tables.books
-
-        subq = select([table2.c.book_id]).alias('foo')
-        stmt = select([table1]).select_from(
-            table1.join_lateral(subq, name='bar')
-        )
-
-        self.assert_compile(
-            stmt,
-            "SELECT people.people_id, people.age, people.name "
-            "FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
-            "FROM books "
-            "WHERE people.people_id = books.book_owner_id) AS bar ON true"
-        )
-
-    def test_join_lateral_auto_onclause_aliased_table(self):
-        table1 = self.tables.people
-        table2 = self.tables.books
-
-        alias_ = table2.alias('foo')
-        stmt = select([table1]).select_from(
-            table1.join_lateral(alias_)
-        )
-
-        # alias is unwrapped (and anonymized; 'foo' might be used elsewhere)
-        self.assert_compile(
-            stmt,
-            "SELECT people.people_id, people.age, people.name "
-            "FROM people JOIN LATERAL (SELECT books.book_id AS book_id, "
-            "books.bookcase_id AS bookcase_id, books.book_owner_id "
-            "AS book_owner_id, books.book_weight AS book_weight "
-            "FROM books "
-            "WHERE people.people_id = books.book_owner_id) AS anon_1 "
-            "ON true"
-        )
-
-    def test_join_lateral_explicit_onclause_subquery(self):
-        table1 = self.tables.people
-        table2 = self.tables.books
-
-        subq = select([table2.c.book_id])
-        stmt = select([table1]).select_from(
-            table1.join_lateral(
-                subq, table1.c.people_id == table2.c.book_owner_id)
-        )
-
-        self.assert_compile(
-            stmt,
-            "SELECT people.people_id, people.age, people.name "
-            "FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
-            "FROM books "
-            "WHERE people.people_id = books.book_owner_id) AS anon_1 ON true"
-        )
-
-    def test_join_lateral_auto_onclause_ambiguous(self):
-        table1 = self.tables.people
-        table2 = self.tables.books
-        table3 = self.tables.bookcases
-
-        subq = select([table2.c.book_id, table3.c.bookcase_width])
-        assert_raises_message(
-            exc.ArgumentError,
-            "passed to join_lateral has mulitple FROM clauses",
-            table1.join_lateral, subq
-        )
-
     def test_from_function(self):
         bookcases = self.tables.bookcases
         srf = lateral(func.generate_series(1, bookcases.c.bookcase_shelves))