Repository: kayak/pypika
Pull request #100 (2018-03-06 14:13:18)

Bugfixing
- Fixed a bug where joined queries would have their alias overwritten
- Fixed ordering in union queries
- Added alias support to ValueWrapper
- Fixed the subquery in FROM clause to not overwrite the alias
- Fixed union queries to work with multiple queries
- Fixed UnionQuery to support limit and offset


Patch:
diff --git a/pypika/functions.py b/pypika/functions.py
index f7a7ca9..d55ad08 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -191,13 +191,15 @@ class SplitPart(Function):
 
 
 class RegexpMatches(Function):
-    def __init__(self, term, pattern, modifiers, alias=None):
+    def __init__(self, term, pattern, modifiers=None, alias=None):
         super(RegexpMatches, self).__init__('REGEXP_MATCHES', term, pattern, modifiers, alias=alias)
 
+
 class RegexpLike(Function):
-    def __init__(self, term, pattern, modifiers, alias=None):
+    def __init__(self, term, pattern, modifiers=None, alias=None):
         super(RegexpLike, self).__init__('REGEXP_LIKE', term, pattern, modifiers, alias=alias)
 
+
 # Date Functions
 class Now(Function):
     def __init__(self, alias=None):
diff --git a/pypika/queries.py b/pypika/queries.py
index 3ce9b02..1e31fe3 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -19,7 +19,8 @@ from .terms import (
     Star,
     Term,
     Tuple,
-    ValueWrapper)
+    ValueWrapper,
+)
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
@@ -52,15 +53,15 @@ class Table(Selectable):
 
         if self.schema:
             table_sql = "{quote}{schema}{quote}.{quote}{name}{quote}".format(
-                schema=self.schema,
-                name=self.table_name,
-                quote=quote_char or ''
+                  schema=self.schema,
+                  name=self.table_name,
+                  quote=quote_char or ''
             )
 
         else:
             table_sql = "{quote}{name}{quote}".format(
-                name=self.table_name,
-                quote=quote_char or ''
+                  name=self.table_name,
+                  quote=quote_char or ''
             )
 
         if self.alias is None:
@@ -98,7 +99,8 @@ def make_tables(*names, **kwargs):
 
 class Query(object):
     """
-    Query is the primary class and entry point in pypika. It is used to build queries iteratively using the builder design
+    Query is the primary class and entry point in pypika. It is used to build queries iteratively using the builder
+    design
     pattern.
 
     This class is immutable.
@@ -170,6 +172,112 @@ class Query(object):
         return cls._builder().update(table)
 
 
+class _UnionQuery(Selectable, Term):
+    """
+    A Query class wrapper for a Union query, whether DISTINCT or ALL.
+
+    Created via the functionds `Query.union` or `Query.union_all`, this class should not be instantiated directly.
+    """
+    def __init__(self, base_query, union_query, union_type, alias=None):
+        super(_UnionQuery, self).__init__(alias)
+        self.base_query = base_query
+        self._unions = [(union_type, union_query)]
+        self._orderbys = []
+
+        self._limit = None
+        self._offset = None
+
+    @builder
+    def orderby(self, *fields, **kwargs):
+        for field in fields:
+            field = (Field(field, table=self.base_query._from[0])
+                     if isinstance(field, str)
+                     else self.base_query._wrap(field))
+
+            self._orderbys.append((field, kwargs.get('order')))
+
+    @builder
+    def limit(self, limit):
+        self._limit = limit
+
+    @builder
+    def offset(self, offset):
+        self._offset = offset
+
+    @builder
+    def union(self, other):
+        self._unions.append((UnionType.distinct, other))
+
+    @builder
+    def union_all(self, other):
+        self._unions.append((UnionType.all, other))
+
+    def __str__(self):
+        return self.get_sql()
+
+    def get_sql(self, with_alias=False, subquery=False, **kwargs):
+        union_template = ' UNION{type} {union}'
+
+        kwargs = {'quote_char': self.base_query.quote_char, 'dialect': self.base_query.dialect}
+        base_querystring = self.base_query.get_sql(subquery=self.base_query.wrap_union_queries, **kwargs)
+
+        querystring = base_querystring
+        for union_type, union_query in self._unions:
+            union_querystring = union_query.get_sql(subquery=self.base_query.wrap_union_queries, **kwargs)
+
+            if len(self.base_query._selects) != len(union_query._selects):
+                raise UnionException("Queries must have an equal number of select statements in a union."
+                                     "\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}"
+                                     .format(query1=base_querystring, query2=union_querystring))
+
+            querystring += union_template.format(type=union_type.value,
+                                                 union=union_querystring)
+
+        if self._orderbys:
+            querystring += self._orderby_sql(**kwargs)
+
+        if self._limit:
+            querystring += self._limit_sql()
+
+        if self._offset:
+            querystring += self._offset_sql()
+
+        if subquery:
+            querystring = '({query})'.format(query=querystring)
+
+        if with_alias:
+            return alias_sql(querystring, self.alias or self.table_name, kwargs.get('quote_char'))
+
+        return querystring
+
+    def _orderby_sql(self, quote_char=None, **kwargs):
+        """
+        Produces the ORDER BY part of the query.  This is a list of fields and possibly their directionality, ASC or
+        DESC. The clauses are stored in the query under self._orderbys as a list of tuples containing the field and
+        directionality (which can be None).
+
+        If an order by field is used in the select clause, determined by a matching , then the ORDER BY clause will use
+        the alias, otherwise the field will be rendered as SQL.
+        """
+        clauses = []
+        selected_aliases = {s.alias for s in self.base_query._selects}
+        for field, directionality in self._orderbys:
+            term = "{quote}{alias}{quote}".format(alias=field.alias, quote=quote_char or '') \
+                if field.alias and field.alias in selected_aliases \
+                else field.get_sql(quote_char=quote_char, **kwargs)
+
+            clauses.append('{term} {orient}'.format(term=term, orient=directionality.value)
+                           if directionality is not None else term)
+
+        return ' ORDER BY {orderby}'.format(orderby=','.join(clauses))
+
+    def _offset_sql(self):
+        return " OFFSET {offset}".format(offset=self._offset)
+
+    def _limit_sql(self):
+        return " LIMIT {limit}".format(limit=self._limit)
+
+
 class QueryBuilder(Selectable, Term):
     """
     Query Builder is the main class in pypika which stores the state of a query and offers functions which allow the
@@ -232,8 +340,8 @@ class QueryBuilder(Selectable, Term):
 
         self._from.append(Table(selectable) if isinstance(selectable, str) else selectable)
 
-        if isinstance(selectable, QueryBuilder):
-            selectable.table_name = 'sq%d' % self._subquery_count
+        if isinstance(selectable, (QueryBuilder, _UnionQuery)) and selectable.alias is None:
+            selectable.alias = 'sq%d' % self._subquery_count
             self._subquery_count += 1
 
     @builder
@@ -403,11 +511,10 @@ class QueryBuilder(Selectable, Term):
 
     @builder
     def union(self, other):
-        self._unions.append((UnionType.distinct, other))
+        return _UnionQuery(self, other, UnionType.distinct)
 
-    @builder
     def union_all(self, other):
-        self._unions.append((UnionType.all, other))
+        return _UnionQuery(self, other, UnionType.all)
 
     @builder
     def set(self, field, value):
@@ -470,7 +577,7 @@ class QueryBuilder(Selectable, Term):
     def do_join(self, join):
         join.validate(self._from, self._joins)
 
-        if isinstance(join.item, QueryBuilder):
+        if isinstance(join.item, QueryBuilder) and join.item.alias is None:
             self._tag_subquery(join.item)
 
         table_in_from = any(isinstance(clause, Table)
@@ -488,9 +595,9 @@ class QueryBuilder(Selectable, Term):
             table_in_froms = field.table in self._from
             table_in_joins = field.table in [join.item for join in self._joins]
             if field.table is not None and \
-                    not table_in_froms and \
-                    not table_in_joins and \
-                    field.table != self._update_table:
+                  not table_in_froms and \
+                  not table_in_joins and \
+                        field.table != self._update_table:
                 raise JoinException('Table [%s] missing from query.  '
                                     'Table must be first joined before any of '
                                     'its fields can be used' % field.table)
@@ -500,7 +607,10 @@ class QueryBuilder(Selectable, Term):
         self._subquery_count += 1
 
     def __str__(self):
-        return self.get_sql(with_unions=True, quote_char=self.quote_char, dialect=self.dialect)
+        return self.get_sql(quote_char=self.quote_char, dialect=self.dialect)
+
+    def __repr__(self):
+        return self.__str__()
 
     def __eq__(self, other):
         if not isinstance(other, QueryBuilder):
@@ -517,7 +627,7 @@ class QueryBuilder(Selectable, Term):
     def __hash__(self):
         return hash(self.alias) + sum(hash(clause) for clause in self._from)
 
-    def get_sql(self, with_alias=False, subquery=False, with_unions=False, **kwargs):
+    def get_sql(self, with_alias=False, subquery=False, **kwargs):
         if not (self._selects or self._insert_table or self._delete_from or self._update_table):
             return ''
         if self._insert_table and not (self._selects or self._values):
@@ -528,6 +638,7 @@ class QueryBuilder(Selectable, Term):
         has_joins = bool(self._joins)
         has_multiple_from_clauses = 1 < len(self._from)
         has_subquery_from_clause = 0 < len(self._from) and isinstance(self._from[0], QueryBuilder)
+
         kwargs['with_namespace'] = any((has_joins, has_multiple_from_clauses, has_subquery_from_clause))
 
         if self._update_table:
@@ -585,9 +696,6 @@ class QueryBuilder(Selectable, Term):
         if self._offset:
             querystring += self._offset_sql()
 
-        if with_unions:
-            querystring = self._union_sql(querystring, **kwargs)
-
         if subquery:
             querystring = '({query})'.format(query=querystring)
 
@@ -598,15 +706,15 @@ class QueryBuilder(Selectable, Term):
 
     def _select_sql(self, **kwargs):
         return 'SELECT {distinct}{select}'.format(
-            distinct='DISTINCT ' if self._distinct else '',
-            select=','.join(term.get_sql(with_alias=True, **kwargs)
-                            for term in self._selects),
+              distinct='DISTINCT ' if self._distinct else '',
+              select=','.join(term.get_sql(with_alias=True, **kwargs)
+                              for term in self._selects),
         )
 
     def _insert_sql(self, **kwargs):
         return 'INSERT {ignore}INTO {table}'.format(
-            table=self._insert_table.get_sql(**kwargs),
-            ignore='IGNORE ' if self._ignore else ''
+              table=self._insert_table.get_sql(**kwargs),
+              ignore='IGNORE ' if self._ignore else ''
         )
 
     @staticmethod
@@ -615,7 +723,7 @@ class QueryBuilder(Selectable, Term):
 
     def _update_sql(self, **kwargs):
         return 'UPDATE {table}'.format(
-            table=self._update_table.get_sql(**kwargs)
+              table=self._update_table.get_sql(**kwargs)
         )
 
     def _columns_sql(self, with_namespace=False, **kwargs):
@@ -625,32 +733,32 @@ class QueryBuilder(Selectable, Term):
             Remove from kwargs, never format the column terms with namespaces since only one table can be inserted into
         """
         return ' ({columns})'.format(
-            columns=','.join(term.get_sql(with_namespace=False, **kwargs)
-                             for term in self._columns)
+              columns=','.join(term.get_sql(with_namespace=False, **kwargs)
+                               for term in self._columns)
         )
 
     def _values_sql(self, **kwargs):
         return ' VALUES ({values})'.format(
-            values='),('.join(','.join(term.get_sql(with_alias=True, **kwargs)
-                                       for term in row)
-                              for row in self._values)
+              values='),('.join(','.join(term.get_sql(with_alias=True, **kwargs)
+                                         for term in row)
+                                for row in self._values)
 
         )
 
     def _into_sql(self, **kwargs):
         return ' INTO {table}'.format(
-            table=self._insert_table.get_sql(with_alias=False, **kwargs),
+              table=self._insert_table.get_sql(with_alias=False, **kwargs),
         )
 
-    def _from_sql(self, subquery=None, with_alias=None, with_unions=None, **kwargs):
+    def _from_sql(self, with_namespace=False, **kwargs):
         return ' FROM {selectable}'.format(selectable=','.join(
-            clause.get_sql(subquery=True, with_alias=kwargs['with_namespace'], with_unions=True, **kwargs)
-            for clause in self._from
+              clause.get_sql(subquery=True, with_alias=True, **kwargs)
+              for clause in self._from
         ))
 
     def _prewhere_sql(self, quote_char=None, **kwargs):
         return ' PREWHERE {prewhere}'.format(
-            prewhere=self._prewheres.get_sql(quote_char=quote_char, subquery=True, **kwargs))
+              prewhere=self._prewheres.get_sql(quote_char=quote_char, subquery=True, **kwargs))
 
     def _where_sql(self, quote_char=None, **kwargs):
         return ' WHERE {where}'.format(where=self._wheres.get_sql(quote_char=quote_char, subquery=True, **kwargs))
@@ -668,8 +776,8 @@ class QueryBuilder(Selectable, Term):
         for field in self._groupbys:
             if field.alias and field.alias in selected_aliases:
                 clauses.append("{quote}{alias}{quote}".format(
-                    alias=field.alias,
-                    quote=quote_char or '',
+                      alias=field.alias,
+                      quote=quote_char or '',
                 ))
             else:
                 clauses.append(field.get_sql(quote_char=quote_char, **kwargs))
@@ -706,25 +814,6 @@ class QueryBuilder(Selectable, Term):
     def _having_sql(self, quote_char=None, **kwargs):
         return ' HAVING {having}'.format(having=self._havings.get_sql(quote_char=quote_char, **kwargs))
 
-    def _union_sql(self, querystring, quote_char=None, **kwargs):
-        if not self._unions:
-            return querystring
-
-        template = '({query}) UNION{type} ({union})' \
-            if self.wrap_union_queries \
-            else '{query} UNION{type} {union}'
-
-        for (union_type, other) in self._unions:
-            other_querystring = other.get_sql(quote_char=quote_char)
-            if len(self._selects) != len(other._selects):
-                raise UnionException("Queries must have an equal number of select statements in a union."
-                                     "\n\nMain Query:\n{query1}\n\nUnion Query:\n{query2}"
-                                     .format(query1=querystring, query2=other_querystring))
-
-            querystring = template.format(query=querystring, type=union_type.value, union=other_querystring)
-
-        return querystring
-
     def _offset_sql(self):
         return " OFFSET {offset}".format(offset=self._offset)
 
@@ -733,11 +822,11 @@ class QueryBuilder(Selectable, Term):
 
     def _set_sql(self, **kwargs):
         return ' SET {set}'.format(
-            set=','.join(
-                '{field}={value}'.format(
-                    field=field.get_sql(**kwargs),
-                    value=value.get_sql(**kwargs)) for field, value in self._updates
-            )
+              set=','.join(
+                    '{field}={value}'.format(
+                          field=field.get_sql(**kwargs),
+                          value=value.get_sql(**kwargs)) for field, value in self._updates
+              )
         )
 
 
@@ -785,7 +874,7 @@ class Join(object):
 
     def get_sql(self, **kwargs):
         sql = 'JOIN {table}'.format(
-            table=self.item.get_sql(subquery=True, with_alias=True, **kwargs),
+              table=self.item.get_sql(subquery=True, with_alias=True, **kwargs),
         )
 
         if self.how.value:
@@ -801,8 +890,8 @@ class JoinOn(Join):
     def get_sql(self, **kwargs):
         join_sql = super(JoinOn, self).get_sql(**kwargs)
         return '{join} ON {criterion}'.format(
-            join=join_sql,
-            criterion=self.criterion.get_sql(**kwargs),
+              join=join_sql,
+              criterion=self.criterion.get_sql(**kwargs),
         )
 
     def validate(self, _from, _joins):
@@ -812,7 +901,7 @@ class JoinOn(Join):
         if missing_tables:
             raise JoinException('Invalid join criterion. One field is required from the joined item and '
                                 'another from the selected table or an existing join.  Found [{tables}]'.format(
-                tables=', '.join(map(str, missing_tables))
+                  tables=', '.join(map(str, missing_tables))
             ))
 
 
@@ -824,8 +913,8 @@ class JoinUsing(Join):
     def get_sql(self, **kwargs):
         join_sql = super(JoinUsing, self).get_sql(**kwargs)
         return '{join} USING ({fields})'.format(
-            join=join_sql,
-            fields=','.join(str(field) for field in self.fields)
+              join=join_sql,
+              fields=','.join(str(field) for field in self.fields)
         )
 
     def validate(self, _from, _joins):
diff --git a/pypika/terms.py b/pypika/terms.py
index 7588534..795caaf 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -20,7 +20,6 @@ from pypika.utils import (
     resolve_is_aggregate,
 )
 
-
 try:
   basestring
 except NameError:
@@ -194,14 +193,20 @@ class Term(object):
 class ValueWrapper(Term):
     is_aggregate = None
 
-    def __init__(self, value):
-        super(ValueWrapper, self).__init__()
+    def __init__(self, value, alias=None):
+        super(ValueWrapper, self).__init__(alias)
         self.value = value
 
     def fields(self):
         return []
 
-    def get_sql(self, **kwargs):
+    def get_sql(self, quote_char=None, **kwargs):
+        sql = self._get_value_sql()
+        if self.alias is None:
+            return sql
+        return alias_sql(sql, self.alias, quote_char)
+
+    def _get_value_sql(self):
         # FIXME escape values
         if isinstance(self.value, Enum):
             return self.value.value
@@ -213,7 +218,6 @@ class ValueWrapper(Term):
             return str.lower(str(self.value))
         if self.value is None:
             return 'null'
-
         return str(self.value)
 
 
@@ -221,8 +225,11 @@ class NullValue(Term):
     def fields(self):
         return []
 
-    def get_sql(self, **kwargs):
-        return 'null'
+    def get_sql(self, quote_char=None, **kwargs):
+        sql = 'NULL'
+        if self.alias is None:
+            return sql
+        return alias_sql(sql, self.alias, quote_char)
 
 
 class Field(Term):


Test patch:
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index a3761ae..9391561 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -85,7 +85,7 @@ class InsertIntoTests(unittest.TestCase):
     def test_insert_null(self):
         query = Query.into(self.table_abc).insert(None)
 
-        self.assertEqual('INSERT INTO "abc" VALUES (null)', str(query))
+        self.assertEqual('INSERT INTO "abc" VALUES (NULL)', str(query))
 
 
 class InsertSelectFromTests(unittest.TestCase):
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index 6cb41d5..9cde63c 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -313,6 +313,37 @@ class JoinBehaviorTests(unittest.TestCase):
                          'JOIN "efg" ON "abc"."efg_id"="efg"."id" '
                          'JOIN "hij" ON "efg"."hij_id"="hij"."id"', str(test_query))
 
+    def test_join_query_without_alias(self):
+        subquery = Query.from_(self.table_efg) \
+            .select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz)
+
+        test_query = Query.from_(self.table_abc) \
+            .join(subquery) \
+            .on(subquery.x == self.table_abc.id) \
+            .select(self.table_abc.foo, subquery.fizz, subquery.buzz)
+
+        self.assertEqual('SELECT "abc"."foo","sq0"."fizz","sq0"."buzz" '
+                         'FROM "abc" JOIN ('
+                         'SELECT "base_id" "x","fizz","buzz" FROM "efg"'
+                         ') "sq0" '
+                         'ON "sq0"."x"="abc"."id"', str(test_query))
+
+    def test_join_query_with_alias(self):
+        subquery = Query.from_(self.table_efg) \
+            .select(self.table_efg.base_id.as_('x'), self.table_efg.fizz, self.table_efg.buzz)\
+.as_('subq')
+
+        test_query = Query.from_(self.table_abc) \
+            .join(subquery) \
+            .on(subquery.x == self.table_abc.id) \
+            .select(self.table_abc.foo, subquery.fizz, subquery.buzz)
+
+        self.assertEqual('SELECT "abc"."foo","subq"."fizz","subq"."buzz" '
+                         'FROM "abc" JOIN ('
+                         'SELECT "base_id" "x","fizz","buzz" FROM "efg"'
+                         ') "subq" '
+                         'ON "subq"."x"="abc"."id"', str(test_query))
+
 
 class UnionTests(unittest.TestCase):
     table1, table2 = Tables('abc', 'efg')
@@ -331,6 +362,52 @@ class UnionTests(unittest.TestCase):
         self.assertEqual('(SELECT "foo" FROM "abc") UNION ALL (SELECT "bar" FROM "efg")', str(query1 * query2))
         self.assertEqual('(SELECT "foo" FROM "abc") UNION ALL (SELECT "bar" FROM "efg")', str(query1.union_all(query2)))
 
+    def test_union_with_order_by(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_('a'))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_('a'))
+
+        union_query = (query1 + query2).orderby(query1.field('a'))
+
+        self.assertEqual('(SELECT "foo" "a" FROM "abc") '
+                         'UNION ALL '
+                         '(SELECT "bar" "a" FROM "efg") '
+                         'ORDER BY "a"', union_query)
+
+    def test_union_with_order_by_use_union_query_field(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_('a'))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_('a'))
+
+        union_query = (query1 + query2)
+        union_query = union_query.orderby(union_query.field('a'))
+
+        self.assertEqual('(SELECT "foo" "a" FROM "abc") '
+                         'UNION ALL '
+                         '(SELECT "bar" "a" FROM "efg") '
+                         'ORDER BY "a"', union_query)
+
+    def test_union_with_order_by_with_aliases(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_('a')).as_('a')
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_('a')).as_('b')
+
+        union_query = (query1 + query2).orderby(query1.field('a'))
+
+        self.assertEqual('(SELECT "foo" "a" FROM "abc") "a" '
+                         'UNION ALL '
+                         '(SELECT "bar" "a" FROM "efg") "b" '
+                         'ORDER BY "a"."a"', union_query)
+
+    def test_union_with_order_by_use_union_query_field_with_aliases(self):
+        query1 = Query.from_(self.table1).select(self.table1.foo.as_('a'))
+        query2 = Query.from_(self.table2).select(self.table2.bar.as_('a'))
+
+        union_query = (query1 + query2).as_('x')
+        union_query = union_query.orderby(union_query.field('a'))
+
+        self.assertEqual('(SELECT "foo" "a" FROM "abc") '
+                         'UNION ALL '
+                         '(SELECT "bar" "a" FROM "efg") '
+                         'ORDER BY "a"', union_query)
+
     def test_require_equal_number_of_fields(self):
         query1 = Query.from_(self.table1).select(self.table1.foo)
         query2 = Query.from_(self.table2).select(self.table2.fiz, self.table2.buz)