diff options
| -rw-r--r-- | doc/build/changelog/unreleased_14/5858.rst | 14 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 15 | ||||
| -rw-r--r-- | test/sql/test_select.py | 56 |
3 files changed, 84 insertions, 1 deletions
diff --git a/doc/build/changelog/unreleased_14/5858.rst b/doc/build/changelog/unreleased_14/5858.rst new file mode 100644 index 000000000..62cd4a751 --- /dev/null +++ b/doc/build/changelog/unreleased_14/5858.rst @@ -0,0 +1,14 @@ +.. change:: + :tags: bug, sql + :tickets: 5858 + + Fixed issue in new :meth:`_sql.Select.join` method where chaining from the + current JOIN wasn't looking at the right state, causing an expression like + "FROM a JOIN b <onclause>, b JOIN c <onclause>" rather than + "FROM a JOIN b <onclause> JOIN c <onclause>". + +.. change:: + :tags: usecase, sql + + Added :meth:`_sql.Select.outerjoin_from` method to complement + :meth:`_sql.Select.join_from`.
\ No newline at end of file diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index b4f6c8bc9..8e478583f 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -3944,7 +3944,7 @@ class SelectState(util.MemoizedSlots, CompileState): replace_from_obj_index = None - from_clauses = self.statement._from_obj + from_clauses = self.from_clauses if from_clauses: @@ -4644,6 +4644,19 @@ class Select( (target, onclause, None, {"isouter": isouter, "full": full}), ) + def outerjoin_from(self, from_, target, onclause=None, full=False): + r"""Create a SQL LEFT OUTER JOIN against this :class:`_expression.Select` + object's criterion + and apply generatively, returning the newly resulting + :class:`_expression.Select`. + + Usage is the same as that of :meth:`_selectable.Select.join_from`. + + """ + return self.join_from( + from_, target, onclause=onclause, isouter=True, full=full + ) + @_generative def join_from( self, from_, target, onclause=None, isouter=False, full=False diff --git a/test/sql/test_select.py b/test/sql/test_select.py index 2a2381427..96c6abd07 100644 --- a/test/sql/test_select.py +++ b/test/sql/test_select.py @@ -41,6 +41,13 @@ child = Table( Column("data", String(50)), ) +grandchild = Table( + "grandchild", + metadata, + Column("id", Integer, primary_key=True), + Column("child_id", ForeignKey("child.id")), +) + class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = "default" @@ -96,6 +103,20 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL): "ON mytable.myid = myothertable.otherid", ) + def test_join_nofrom_implicit_left_side_explicit_onclause_3level(self): + stmt = ( + select(parent) + .join(child, child.c.parent_id == parent.c.id) + .join(grandchild, grandchild.c.child_id == child.c.id) + ) + + self.assert_compile( + stmt, + "SELECT parent.id, parent.data FROM parent JOIN child " + "ON child.parent_id = parent.id " + "JOIN grandchild ON grandchild.child_id = child.id", + ) + def test_join_nofrom_explicit_left_side_explicit_onclause(self): stmt = select(table1).join_from( table1, table2, table1.c.myid == table2.c.otherid @@ -108,6 +129,18 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL): "ON mytable.myid = myothertable.otherid", ) + def test_outerjoin_nofrom_explicit_left_side_explicit_onclause(self): + stmt = select(table1).outerjoin_from( + table1, table2, table1.c.myid == table2.c.otherid + ) + + self.assert_compile( + stmt, + "SELECT mytable.myid, mytable.name, mytable.description " + "FROM mytable LEFT OUTER JOIN myothertable " + "ON mytable.myid = myothertable.otherid", + ) + def test_join_nofrom_implicit_left_side_implicit_onclause(self): stmt = select(parent).join(child) @@ -117,6 +150,16 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL): "ON parent.id = child.parent_id", ) + def test_join_nofrom_implicit_left_side_implicit_onclause_3level(self): + stmt = select(parent).join(child).join(grandchild) + + self.assert_compile( + stmt, + "SELECT parent.id, parent.data FROM parent JOIN child " + "ON parent.id = child.parent_id " + "JOIN grandchild ON child.id = grandchild.child_id", + ) + def test_join_nofrom_explicit_left_side_implicit_onclause(self): stmt = select(parent).join_from(parent, child) @@ -172,6 +215,19 @@ class FutureSelectTest(fixtures.TestBase, AssertsCompiledSQL): "ON parent.id = child.parent_id", ) + def test_right_nested_inner_join(self): + inner = child.join(grandchild) + + stmt = select(parent).outerjoin_from(parent, inner) + + self.assert_compile( + stmt, + "SELECT parent.id, parent.data FROM parent " + "LEFT OUTER JOIN " + "(child JOIN grandchild ON child.id = grandchild.child_id) " + "ON parent.id = child.parent_id", + ) + def test_joins_w_filter_by(self): stmt = ( select(parent) |