diff options
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 100 | ||||
| -rw-r--r-- | test/sql/test_lateral.py | 123 |
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)) |