Repository: kayak/pypika
Pull request #169 (2018-10-23 19:32:26)

With clause added
I added the `with` clause. I am not completely happy with the result. Particularly with the new class `AliasedQuery`.
What do you think?

```python
from pypika import Table, AliasedQuery, Query

Foo = Table('Foo')
Bar = Table('Bar')
sub_query = (Query
             .from_(Foo)
             .select('*'))
test_query = (Query
             .with_(sub_query, "an_alias")
             .from_(AliasedQuery("an_alias"))
             .select('*'))

```
```
WITH an_alias AS (SELECT * FROM "Foo") SELECT * FROM an_alias
```

coveralls: 
[![Coverage Status](https://coveralls.io/builds/19678726/badge)](https://coveralls.io/builds/19678726)

Coverage decreased (-0.005%) to 97.992% when pulling **3088623ac444b9b531ab53c1b0197e01663b8c6a on lucianolorenti:with** into **246eb95116e5dc64f98dc7716b5dba2848466c2f on kayak:master**.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/19678726/badge)](https://coveralls.io/builds/19678726)

Coverage decreased (-0.005%) to 97.992% when pulling **3088623ac444b9b531ab53c1b0197e01663b8c6a on lucianolorenti:with** into **246eb95116e5dc64f98dc7716b5dba2848466c2f on kayak:master**.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/19678726/badge)](https://coveralls.io/builds/19678726)

Coverage decreased (-0.005%) to 97.992% when pulling **3088623ac444b9b531ab53c1b0197e01663b8c6a on lucianolorenti:with** into **246eb95116e5dc64f98dc7716b5dba2848466c2f on kayak:master**.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/19678726/badge)](https://coveralls.io/builds/19678726)

Coverage decreased (-0.005%) to 97.992% when pulling **3088623ac444b9b531ab53c1b0197e01663b8c6a on lucianolorenti:with** into **246eb95116e5dc64f98dc7716b5dba2848466c2f on kayak:master**.

coveralls: 
[![Coverage Status](https://coveralls.io/builds/19678726/badge)](https://coveralls.io/builds/19678726)

Coverage decreased (-0.005%) to 97.992% when pulling **3088623ac444b9b531ab53c1b0197e01663b8c6a on lucianolorenti:with** into **246eb95116e5dc64f98dc7716b5dba2848466c2f on kayak:master**.

twheys: Hi, it looks good, and I appreciate the contribution. Please just review my feedback so we can merge.

Patch:
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 8ad3256..9d3ff16 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -48,6 +48,7 @@ from .enums import (
 )
 # noinspection PyUnresolvedReferences
 from .queries import (
+    AliasedQuery,
     Query,
     Table,
     Tuple,
diff --git a/pypika/enums.py b/pypika/enums.py
index b6ed884..fa90086 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 from aenum import Enum
 
 __author__ = "Timothy Heys"
diff --git a/pypika/queries.py b/pypika/queries.py
index 8361907..3f5ce79 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 from pypika.enums import (
     JoinType,
     UnionType,
@@ -11,6 +11,7 @@ from pypika.utils import (
     builder,
     ignore_copy,
 )
+
 from .terms import (
     ArithmeticExpression,
     Field,
@@ -41,6 +42,28 @@ class Selectable(object):
     def __getattr__(self, name):
         return self.field(name)
 
+class AliasedQuery(Selectable):
+    def __init__(self, name, query=None):
+        super(AliasedQuery, self).__init__(alias=name)
+        self.name = name
+        self.query = query
+
+    def get_sql(self, **kwargs):
+        if self.query == None:
+            return self.name
+        else:
+            return self.query.get_sql(**kwargs)
+
+    def __eq__(self, other):
+        if not isinstance(other, AliasedQuery):
+            return False
+        if self.name == other.name:
+            return True
+        else:
+            return False
+
+    def __hash__(self):
+        return hash(str(self.name))
 
 class Table(Selectable):
     def __init__(self, name, schema=None, alias=None):
@@ -144,6 +167,10 @@ class Query(object):
         """
         return cls._builder().into(table)
 
+    @classmethod
+    def with_(cls, table, name):
+        return cls._builder().with_(table, name)
+
     @classmethod
     def select(cls, *terms):
         """
@@ -302,6 +329,7 @@ class QueryBuilder(Selectable, Term):
         self._update_table = None
         self._delete_from = False
 
+        self._with = []
         self._selects = []
         self._columns = []
         self._values = []
@@ -368,6 +396,11 @@ class QueryBuilder(Selectable, Term):
             selectable.alias = 'sq%d' % sub_query_count
             self._subquery_count = sub_query_count + 1
 
+    @builder
+    def with_(self, selectable, name):
+        t = AliasedQuery(name, selectable)
+        self._with.append(t)
+
     @builder
     def into(self, table):
         if self._insert_table is not None:
@@ -523,6 +556,9 @@ class QueryBuilder(Selectable, Term):
         elif isinstance(item, QueryBuilder):
             return Joiner(self, item, how, type_label='subquery')
 
+        elif isinstance(item, AliasedQuery):
+            return Joiner(self, item, how, type_label='table')
+
         raise ValueError("Cannot join on type '%s'" % type(item))
 
     @builder
@@ -600,7 +636,7 @@ class QueryBuilder(Selectable, Term):
         return []
 
     def do_join(self, join):
-        base_tables = self._from + [self._update_table]
+        base_tables = self._from + [self._update_table] + self._with
 
         join.validate(base_tables, self._joins)
 
@@ -700,7 +736,12 @@ class QueryBuilder(Selectable, Term):
                 querystring += ' ' + self._select_sql(**kwargs)
 
         else:
-            querystring = self._select_sql(**kwargs)
+            if self._with:
+                querystring = self._with_sql(**kwargs)
+            else:
+                querystring = ''
+
+            querystring += self._select_sql(**kwargs)
 
             if self._insert_table:
                 querystring += self._into_sql(**kwargs)
@@ -743,6 +784,15 @@ class QueryBuilder(Selectable, Term):
 
         return querystring
 
+    def _with_sql(self, with_namespace=False, **kwargs):
+        return 'WITH ' + ','.join(
+            clause.name + ' AS (' + clause.get_sql(
+                subquery=False,
+                with_alias=False,
+                **kwargs) +
+            ') '
+            for clause in self._with)
+
     def _select_sql(self, **kwargs):
         return 'SELECT {distinct}{select}'.format(
               distinct='DISTINCT ' if self._distinct else '',
diff --git a/pypika/terms.py b/pypika/terms.py
index 13ddafe..174de3d 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 import inspect
 import re
 from datetime import date
diff --git a/pypika/utils.py b/pypika/utils.py
index 394e66f..4fdfd84 100644
--- a/pypika/utils.py
+++ b/pypika/utils.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
diff --git a/setup.py b/setup.py
index d6c270b..f166c35 100644
--- a/setup.py
+++ b/setup.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 
 import ast
 from setuptools import setup


Test patch:
diff --git a/pypika/tests/__init__.py b/pypika/tests/__init__.py
index 5716173..8fc8b1a 100644
--- a/pypika/tests/__init__.py
+++ b/pypika/tests/__init__.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 8644a7b..c9f15d2 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 import unittest
 
 from datetime import (
diff --git a/pypika/tests/test_deletes.py b/pypika/tests/test_deletes.py
index 7d5128f..4196b85 100644
--- a/pypika/tests/test_deletes.py
+++ b/pypika/tests/test_deletes.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 import unittest
 
 from pypika import Table, Query, PostgreSQLQuery
diff --git a/pypika/tests/test_functions.py b/pypika/tests/test_functions.py
index e272d6c..8d5fa00 100644
--- a/pypika/tests/test_functions.py
+++ b/pypika/tests/test_functions.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 import unittest
 
 from pypika import (
diff --git a/pypika/tests/test_inserts.py b/pypika/tests/test_inserts.py
index cbccc26..b863c3c 100644
--- a/pypika/tests/test_inserts.py
+++ b/pypika/tests/test_inserts.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 import unittest
 
 from pypika import (
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index e3dfff6..ef26111 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -1,7 +1,8 @@
-# coding: utf-8
+# coding: utf8
 import unittest
 
 from pypika import (
+    AliasedQuery,
     Case,
     Field as F,
     MSSQLQuery,
@@ -749,3 +750,31 @@ class SubqueryTests(unittest.TestCase):
                          'FROM ('
                          'SELECT "base_id" "x","fizz","buzz" FROM "efg"'
                          ') "subq"', str(test_query))
+
+    def test_with(self):
+        sub_query = (Query
+                     .from_(self.table_efg)
+                     .select('fizz'))
+        test_query = (Query
+                      .with_(sub_query, "an_alias")
+                      .from_(AliasedQuery("an_alias"))
+                      .select('*'))
+
+        self.assertEqual(
+            'WITH an_alias AS (SELECT "fizz" FROM "efg") SELECT * FROM an_alias',
+            str(test_query))
+
+    def test_join_with_with(self):
+        sub_query = (Query
+                     .from_(self.table_efg)
+                     .select('fizz'))
+        test_query = (Query
+                      .with_(sub_query, "an_alias")
+                      .from_(self.table_abc)
+                      .join(AliasedQuery('an_alias'))
+                      .on(AliasedQuery('an_alias').fizz == self.table_abc.buzz)
+                      .select('*'))
+        self.assertEqual(
+            'WITH an_alias AS (SELECT "fizz" FROM "efg") '
+            'SELECT * FROM "abc" JOIN an_alias ON "an_alias"."fizz"="abc"."buzz"',
+            str(test_query))
diff --git a/pypika/tests/test_updates.py b/pypika/tests/test_updates.py
index bc0a88c..a3b858e 100644
--- a/pypika/tests/test_updates.py
+++ b/pypika/tests/test_updates.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 import unittest
 
 from pypika import Table, Query, PostgreSQLQuery
diff --git a/pypika/tests/test_utils.py b/pypika/tests/test_utils.py
index 900f649..ce5a821 100644
--- a/pypika/tests/test_utils.py
+++ b/pypika/tests/test_utils.py
@@ -1,4 +1,4 @@
-# coding: utf-8
+# coding: utf8
 import unittest
 
 from pypika import utils