Repository: kayak/pypika
Pull request #87 (2017-12-12 17:19:25)

Fixed usage of alias in groupby clause
Fixed an issue with aliases in groupby clauses to use the term alias if it is selected in the query


Patch:
diff --git a/MANIFEST.in b/MANIFEST.in
index 5e28dc9..9561fb1 100644
--- a/MANIFEST.in
+++ b/MANIFEST.in
@@ -1,2 +1,1 @@
 include README.rst
-include requirements.txt
diff --git a/pypika/__init__.py b/pypika/__init__.py
index c388796..7ca0903 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -68,4 +68,4 @@ from .utils import (
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
-__version__ = "0.9.3"
+__version__ = "0.9.2"
diff --git a/pypika/queries.py b/pypika/queries.py
index 900c740..c49353a 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -629,10 +629,18 @@ class QueryBuilder(Selectable, Term):
         return ' WHERE {where}'.format(where=self._wheres.get_sql(quote_char=quote_char, subquery=True, **kwargs))
 
     def _group_sql(self, quote_char=None, with_alias=False, **kwargs):
-        return ' GROUP BY {groupby}'.format(
-            groupby=','.join(term.get_sql(quote_char=quote_char, with_alias=with_alias, **kwargs)
-                             for term in self._groupbys)
-        )
+        clauses = []
+        for term in self._groupbys:
+            selected_aliases = {s.alias for s in self._selects}
+            if term.alias is not None and term.alias in selected_aliases:
+                clauses.append("{quote}{alias}{quote}".format(
+                    alias=term.alias,
+                    quote=quote_char or '',
+                ))
+            else:
+                clauses.append(term.get_sql(quote_char=quote_char, with_alias=with_alias, **kwargs))
+
+        return ' GROUP BY {groupby}'.format(groupby=','.join(clauses))
 
     def _rollup_sql(self):
         return ' WITH ROLLUP'
diff --git a/pypika/terms.py b/pypika/terms.py
index f34c5a7..873ac3f 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -185,7 +185,7 @@ class Term(object):
         return self.get_sql(quote_char='"')
 
     def __hash__(self):
-        return hash(self.get_sql())
+        return hash(self.get_sql(with_alias=True))
 
     def get_sql(self):
         raise NotImplementedError()


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index b01dd18..f0d74d7 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -2,19 +2,19 @@
 import unittest
 
 from pypika import (
-    Query,
-    Table,
-    Tables,
-    Field as F,
     Case,
-    functions as fn,
-    Order,
-    MySQLQuery,
-    VerticaQuery,
+    Field as F,
     MSSQLQuery,
-    PostgreSQLQuery,
+    MySQLQuery,
     OracleQuery,
+    Order,
+    PostgreSQLQuery,
+    Query,
     RedshiftQuery,
+    Table,
+    Tables,
+    VerticaQuery,
+    functions as fn,
 )
 
 __author__ = "Timothy Heys"
@@ -231,6 +231,7 @@ class WhereTests(unittest.TestCase):
 
 class GroupByTests(unittest.TestCase):
     t = Table('abc')
+    maxDiff = None
 
     def test_groupby__single(self):
         q = Query.from_(self.t).groupby(self.t.foo).select(self.t.foo)
@@ -263,15 +264,26 @@ class GroupByTests(unittest.TestCase):
         self.assertEqual('SELECT "foo",COUNT(DISTINCT *) FROM "abc" GROUP BY "foo"', str(q))
 
     def test_groupby__alias(self):
-        q = Query.from_(self.t).select(
-            fn.Sum(self.t.foo).as_('bar'),
-        ).groupby(
-            fn.Sum(self.t.foo).as_('bar'),
-        )
-
-        self.assertEqual('SELECT SUM("foo") "bar" FROM "abc" GROUP BY SUM("foo")', str(q))
-
-    def test_groupby_with_case_does_not_show_an_alias(self):
+        bar = self.t.bar.as_('bar01')
+        q = Query.from_(self.t) \
+            .select(fn.Sum(self.t.foo), bar) \
+            .groupby(bar)
+
+        self.assertEqual('SELECT SUM("foo"),"bar" "bar01" FROM "abc" GROUP BY "bar01"', str(q))
+
+    def test_groupby__alias_with_join(self):
+        table1 = Table('table1', alias='t1')
+        bar = table1.bar.as_('bar01')
+        q = Query.from_(self.t) \
+            .join(table1).on(self.t.id == table1.t_ref) \
+            .select(fn.Sum(self.t.foo), bar) \
+            .groupby(bar)
+
+        self.assertEqual('SELECT SUM("abc"."foo"),"t1"."bar" "bar01" FROM "abc" '
+                         'JOIN "table1" "t1" ON "abc"."id"="t1"."t_ref" '
+                         'GROUP BY "bar01"', str(q))
+
+    def test_groupby_with_case_uses_the_alias(self):
         q = Query.from_(self.t).select(
             fn.Sum(self.t.foo).as_('bar'),
             Case()
@@ -287,8 +299,7 @@ class GroupByTests(unittest.TestCase):
                          "CASE WHEN \"fname\"='Tom' THEN 'It was Tom' "
                          "ELSE 'It was someone else.' END \"who_was_it\" "
                          "FROM \"abc\" "
-                         "GROUP BY "
-                         "CASE WHEN \"fname\"='Tom' THEN 'It was Tom' ELSE 'It was someone else.' END", str(q))
+                         "GROUP BY \"who_was_it\"", str(q))
 
     def test_mysql_query_uses_backtick_quote_chars(self):
         q = MySQLQuery.from_(self.t).groupby(self.t.foo).select(self.t.foo)