Repository: kayak/pypika (python)

Issue:
Select from multiple tables or subqueries
Please make it possible to select from multiple tables and subqueries:

```
select * from
    table1 as T1,
    table2 as T2,
    table3  as T3
```

- or - 

```
select * from
    (select * from table1) as T1,
    (select * from table2) as T2,
    (select * from table3) as T3
```

This can be done by chaining the `from_` function.

Pull request:
Support for multiple FROM clauses
Fixes #30 Added support from multiple from clauses using either tables or subqueries

Patch:
diff --git a/pypika/queries.py b/pypika/queries.py
index 6afa498..f070a6a 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -152,7 +152,7 @@ class QueryBuilder(Selectable, Term):
     def __init__(self):
         super(QueryBuilder, self).__init__(None)
 
-        self._from = None
+        self._from = []
         self._insert_table = None
 
         self._selects = []
@@ -192,13 +192,11 @@ class QueryBuilder(Selectable, Term):
         :returns
             A copy of the query with the table added.
         """
-        if self._from is not None:
-            raise AttributeError("'Query' object has no attribute '%s'" % 'from_')
 
-        self._from = Table(selectable) if isinstance(selectable, str) else selectable
+        self._from.append(Table(selectable) if isinstance(selectable, str) else selectable)
 
         if isinstance(selectable, QueryBuilder):
-            selectable.table_name = 'sq0'
+            selectable.table_name = 'sq%d' % self._subquery_count
             self._subquery_count += 1
 
     @builder
@@ -280,7 +278,7 @@ class QueryBuilder(Selectable, Term):
     def groupby(self, *terms):
         for term in terms:
             if isinstance(term, str):
-                term = Field(term, table=self._from)
+                term = Field(term, table=self._from[0])
 
             self._validate_term(term)
             self._groupbys.append(term)
@@ -306,7 +304,7 @@ class QueryBuilder(Selectable, Term):
     @builder
     def orderby(self, *fields, **kwargs):
         for field in fields:
-            field = (Field(field, table=self._from)
+            field = (Field(field, table=self._from[0])
                      if isinstance(field, str)
                      else self._wrap(field))
 
@@ -362,7 +360,7 @@ class QueryBuilder(Selectable, Term):
             self._selects = [Star()]
             return
 
-        self._select_field(Field(term, table=self._from))
+        self._select_field(Field(term, table=self._from[0]))
 
     def _select_field(self, term):
         if self._select_star:
@@ -417,25 +415,28 @@ class QueryBuilder(Selectable, Term):
         if isinstance(item, QueryBuilder):
             self._tag_subquery(item)
 
-        if (isinstance(item, Table) and isinstance(self._from, Table) and
-                    item.alias is None and item.table_name == self._from.table_name):
+        table_in_from = any(isinstance(clause, Table)
+                            and item.table_name == self._from[0].table_name
+                            for clause in self._from)
+        if (isinstance(item, Table) and item.alias is None and table_in_from):
             # On the odd chance that we join the same table as the FROM table and don't set an alias
+            # FIXME only works once
             item.alias = item.table_name + '2'
 
         self._joins.append(Join(item, criterion, how))
 
     def _validate_term(self, term):
         for field in term.fields():
-            if (field.table is not None
-                and field.table != self._from
-                and field.table not in [join.item for join in self._joins]):
+            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:
                 raise JoinException('Table [%s] missing from query.  '
                                     'Table must be first joined before any of '
                                     'its fields can be used' % field.table)
 
     def _validate_join(self, criterion, item):
         criterion_tables = set([f.table for f in criterion.fields()])
-        available_tables = ({self._from} | {join.item for join in self._joins} | {item})
+        available_tables = (set(self._from) | {join.item for join in self._joins} | {item})
         missing_tables = criterion_tables - available_tables
         if missing_tables:
             raise JoinException('Invalid join criterion. One field is required from the joined item and '
@@ -457,16 +458,13 @@ class QueryBuilder(Selectable, Term):
         if not self.alias == other.alias:
             return False
 
-        if not self._from == other._from:
-            return False
-
         return True
 
     def __ne__(self, other):
         return not self.__eq__(other)
 
     def __hash__(self):
-        return hash(self.alias) + hash(self._from)
+        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):
         if not (self._selects or self._insert_table):
@@ -474,7 +472,7 @@ class QueryBuilder(Selectable, Term):
         if self._insert_table and not (self._selects or self._values):
             return ''
 
-        kwargs = {'with_namespace': bool(self._joins)}
+        kwargs = {'with_namespace': bool(self._joins) or 1 < len(self._from)}
 
         if not self._select_into and self._insert_table:
             querystring = self._insert_sql()
@@ -494,7 +492,7 @@ class QueryBuilder(Selectable, Term):
                 querystring += self._into_sql()
 
         if self._from:
-            querystring += self._from_sql()
+            querystring += self._from_sql(**kwargs)
 
         if self._joins:
             for join_item in self._joins:
@@ -567,10 +565,11 @@ class QueryBuilder(Selectable, Term):
             table=self._insert_table.get_sql(with_quotes=True, with_alias=False),
         )
 
-    def _from_sql(self):
-        return ' FROM {selectable}'.format(
-            selectable=self._from.get_sql(with_quotes=True, subquery=True, with_alias=bool(self._joins)),
-        )
+    def _from_sql(self, with_namespace=False, **kwargs):
+        return ' FROM {selectable}'.format(selectable=','.join(
+            clause.get_sql(with_quotes=True, subquery=True, with_alias=with_namespace)
+            for clause in self._from
+        ))
 
     def _jointype_sql(self, join_item):
         return ' {type}'.format(type=join_item.how.value)


Test patch:
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 457e365..e705b29 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -8,7 +8,7 @@ __email__ = "theys@kayak.com"
 
 
 class SelectTests(unittest.TestCase):
-    t = Table('abc')
+    table_abc, table_efg = Tables('abc', 'efg')
 
     def test_empty_query(self):
         q = Query.from_('abc')
@@ -59,16 +59,31 @@ class SelectTests(unittest.TestCase):
         self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q2))
 
     def test_select__columns__multi__field(self):
-        q1 = Query.from_(self.t).select(self.t.foo, self.t.bar)
-        q2 = Query.from_(self.t).select(self.t.foo).select(self.t.bar)
+        q1 = Query.from_(self.table_abc).select(self.table_abc.foo, self.table_abc.bar)
+        q2 = Query.from_(self.table_abc).select(self.table_abc.foo).select(self.table_abc.bar)
 
         self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q1))
         self.assertEqual('SELECT "foo","bar" FROM "abc"', str(q2))
 
-    def test_select__fields_after_star(self):
-        q = Query.from_('abc').select('*').select('foo')
+    def test_select__multiple_tables(self):
+        q = Query.from_(self.table_abc) \
+            .select(self.table_abc.foo) \
+            .from_(self.table_efg) \
+            .select(self.table_efg.bar)
 
-        self.assertEqual('SELECT * FROM "abc"', str(q))
+        self.assertEqual('SELECT "abc"."foo","efg"."bar" FROM "abc","efg"', str(q))
+
+    def test_select__multiple_subqueries(self):
+        subquery0 = Query.from_(self.table_abc).select("foo")
+        subquery1 = Query.from_(self.table_efg).select("bar")
+        q = Query \
+            .from_(subquery0) \
+            .from_(subquery1) \
+            .select(subquery0.foo, subquery1.bar)
+
+        self.assertEqual('SELECT "sq0"."foo","sq1"."bar" '
+                         'FROM (SELECT "foo" FROM "abc") "sq0",'
+                         '(SELECT "bar" FROM "efg") "sq1"', str(q))
 
     def test_select__no_table(self):
         q = Query.select(1, 2, 3)
@@ -80,14 +95,6 @@ class SelectTests(unittest.TestCase):
 
         self.assertEqual('SELECT 1,2,3,"foo" FROM "abc"', str(q))
 
-    def test_static_from_function_removed_after_called(self):
-        with self.assertRaises(AttributeError):
-            Query.from_('abc').from_('efg')
-
-    def test_instance_from_function_removed_after_called(self):
-        with self.assertRaises(AttributeError):
-            Query.select(1).from_('abc').from_('efg')
-
     def test_select_with_limit(self):
         q1 = Query.from_('abc').select('foo')[:10]
 
@@ -393,6 +400,16 @@ class AliasTests(unittest.TestCase):
 
         self.assertListEqual(['foo', 'buz2'], test_query.groupby_aliases())
 
+    def test_select__multiple_tables(self):
+        table_abc, table_efg = Table('abc', alias='q0'), Table('efg', alias='q1')
+
+        q = Query.from_(table_abc) \
+            .select(table_abc.foo) \
+            .from_(table_efg) \
+            .select(table_efg.bar)
+
+        self.assertEqual('SELECT "q0"."foo","q1"."bar" FROM "abc" "q0","efg" "q1"', str(q))
+
 
 class SubqueryTests(unittest.TestCase):
     maxDiff = None