diff options
| -rw-r--r-- | lib/sqlalchemy/dialects/mysql/base.py | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 29 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 11 |
4 files changed, 45 insertions, 12 deletions
diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index 2c78de2fc..dc6340d32 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1850,9 +1850,16 @@ class MySQLCompiler(compiler.SQLCompiler): return "" def visit_join(self, join, asfrom=False, **kwargs): + if join.full: + join_type = " FULL OUTER JOIN " + elif join.isouter: + join_type = " LEFT OUTER JOIN " + else: + join_type = " INNER JOIN " + return ''.join( (self.process(join.left, asfrom=True, **kwargs), - (join.isouter and " LEFT OUTER JOIN " or " INNER JOIN "), + join_type, self.process(join.right, asfrom=True, **kwargs), " ON ", self.process(join.onclause, **kwargs))) diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index f1220ce31..43f289ee7 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1814,9 +1814,15 @@ class SQLCompiler(Compiled): return "" def visit_join(self, join, asfrom=False, **kwargs): + if join.full: + join_type = " FULL OUTER JOIN " + elif join.isouter: + join_type = " LEFT OUTER JOIN " + else: + join_type = " JOIN " return ( join.left._compiler_dispatch(self, asfrom=True, **kwargs) + - (join.isouter and " LEFT OUTER JOIN " or " JOIN ") + + join_type + join.right._compiler_dispatch(self, asfrom=True, **kwargs) + " ON " + join.onclause._compiler_dispatch(self, **kwargs) diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 73341053d..818612932 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -313,7 +313,7 @@ class FromClause(Selectable): return Select([self], whereclause, **params) - def join(self, right, onclause=None, isouter=False): + def join(self, right, onclause=None, isouter=False, full=False): """Return a :class:`.Join` from this :class:`.FromClause` to another :class:`FromClause`. @@ -341,6 +341,9 @@ class FromClause(Selectable): :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN. + :param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER + JOIN. + .. seealso:: :func:`.join` - standalone function @@ -349,9 +352,9 @@ class FromClause(Selectable): """ - return Join(self, right, onclause, isouter) + return Join(self, right, onclause, isouter, full) - def outerjoin(self, right, onclause=None): + def outerjoin(self, right, onclause=None, full=False): """Return a :class:`.Join` from this :class:`.FromClause` to another :class:`FromClause`, with the "isouter" flag set to True. @@ -379,6 +382,8 @@ class FromClause(Selectable): join. If left at ``None``, :meth:`.FromClause.join` will attempt to join the two tables based on a foreign key relationship. + :param full: if True, render a FULL OUTER JOIN, instead of JOIN. + .. seealso:: :meth:`.FromClause.join` @@ -387,7 +392,7 @@ class FromClause(Selectable): """ - return Join(self, right, onclause, True) + return Join(self, right, onclause, True, full) def alias(self, name=None, flat=False): """return an alias of this :class:`.FromClause`. @@ -648,7 +653,7 @@ class Join(FromClause): _is_join = True - def __init__(self, left, right, onclause=None, isouter=False): + def __init__(self, left, right, onclause=None, isouter=False, full=False): """Construct a new :class:`.Join`. The usual entrypoint here is the :func:`~.expression.join` @@ -665,9 +670,10 @@ class Join(FromClause): self.onclause = onclause self.isouter = isouter + self.full = full @classmethod - def _create_outerjoin(cls, left, right, onclause=None): + def _create_outerjoin(cls, left, right, onclause=None, full=False): """Return an ``OUTER JOIN`` clause element. The returned object is an instance of :class:`.Join`. @@ -689,10 +695,11 @@ class Join(FromClause): :class:`.Join` object. """ - return cls(left, right, onclause, isouter=True) + return cls(left, right, onclause, isouter=True, full=full) @classmethod - def _create_join(cls, left, right, onclause=None, isouter=False): + def _create_join(cls, left, right, onclause=None, isouter=False, + full=False): """Produce a :class:`.Join` object, given two :class:`.FromClause` expressions. @@ -724,6 +731,8 @@ class Join(FromClause): :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN. + :param full: if True, render a FULL OUTER JOIN, instead of JOIN. + .. seealso:: :meth:`.FromClause.join` - method form, based on a given left side @@ -732,7 +741,7 @@ class Join(FromClause): """ - return cls(left, right, onclause, isouter) + return cls(left, right, onclause, isouter, full) @property def description(self): @@ -1050,7 +1059,7 @@ class Join(FromClause): chain(sqlutil.ClauseAdapter(right_a)) return left_a.join(right_a, adapter.traverse(self.onclause), - isouter=self.isouter) + isouter=self.isouter, full=self.full) else: return self.select(use_labels=True, correlate=False).alias(name) diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index f6f2ec740..924cc3cdd 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -1537,6 +1537,17 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "mytable.myid = :myid_1 OR myothertable.othername != :othername_1 " "OR EXISTS (select yay from foo where boo = lar)", ) + def test_full_outer_join(self): + self.assert_compile( + select( + [table1], + from_obj=[join(table1, table2, table1.c.myid + == table2.c.otherid, full=True)] + ), + "SELECT mytable.myid, mytable.name, mytable.description FROM " + "mytable FULL OUTER JOIN myothertable ON mytable.myid = myothertable.otherid") + + def test_compound_selects(self): assert_raises_message( exc.ArgumentError, |