Repository: kayak/pypika (python)

Issue:
additional Join functionality 
- It would be a nice convenience function just to specify a string or list of strs if the column(s) to join on are the same in both tables. e.g. from_(t1).join(t2).on('c1')

- it's not clear how I can construct a join USING clause "... from t1 join t2 USING (c1, c2)"

i'm happy to contribute if this is not yet available but you think would be helpful

I'm sorry the docs don't show a preferred method of getting in touch. please suggest most appropriate communication channel. You can look me up as "CJ Lee" from London on linkedin

P.S. love this package thanks for this

Pull request:
Added support for joins with USING and updated the documentation
Fixes #36 

Added support for USING joins with tests. Rewrote the join documentation to omit low level details and to cover both join types.

Patch:
diff --git a/README.rst b/README.rst
index 2154b78..3bba5bd 100644
--- a/README.rst
+++ b/README.rst
@@ -289,11 +289,13 @@ After adding a ``GROUP BY`` clause to a query, the ``HAVING`` clause becomes ava
 Joining Tables and Subqueries
 """""""""""""""""""""""""""""
 
-Tables and subqueries can be joined to any query using the ``Query.join()`` method.  When joining tables and
-subqueries, a criterion must provided containing an equality between a field from the primary table or joined tables and
-a field from the joining table.  When calling ``Query.join()`` with a table, a ``TablerJoiner`` will be
-returned with only the ``Joiner.on()`` function available which takes a ``Criterion`` parameter.  After
-calling ``Joiner.on()`` the original query builder is returned and additional methods may be chained.
+Tables and subqueries can be joined to any query using the ``Query.join()`` method.  Joins can be performed with either
+a ``USING`` or ``ON`` clauses.  The ``USING`` clause can be used when both tables/subqueries contain the same field and
+the ``ON`` clause can be used with a criterion. To perform a join, ``...join()`` can be chained but then must be
+followed immediately by ``...on(<criterion>)`` or ``...using(*field)``.
+
+Example of a join using `ON`
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 .. code-block:: python
 
@@ -308,9 +310,33 @@ calling ``Joiner.on()`` the original query builder is returned and additional me
         customers.id == 5
     )
 
+
 .. code-block:: sql
 
-    SELECT history.* FROM history JOIN customers ON history.customer_id=customers.id WHERE customers.id=5
+    SELECT "history".* FROM "history" JOIN "customers" ON "history"."customer_id"="customers"."id" WHERE "customers"."id"=5
+
+
+Example of a join using `USING`
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+.. code-block:: python
+
+    history, customers = Tables('history', 'customers')
+    q = Query.from_(history).join(
+        customers
+    ).on(
+        'customer_id'
+    ).select(
+        history.star
+    ).where(
+        customers.id == 5
+    )
+
+
+.. code-block:: sql
+
+    SELECT "history".* FROM "history" JOIN "customers" USING "customer_id" WHERE "customers"."id"=5
+
 
 Unions
 """"""
@@ -333,7 +359,7 @@ union all, use ``Query.union_all()`` or the `*` operator.
 
 .. code-block:: sql
 
-    SELECT created_time,foo,bar FROM provider_a UNION SELECT created_time,fiz,buz FROM provider_b
+    SELECT "created_time","foo","bar" FROM "provider_a" UNION SELECT "created_time","fiz","buz" FROM "provider_b"
 
 
 Date, Time, and Intervals
diff --git a/pypika/queries.py b/pypika/queries.py
index 91faa46..3feffe2 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -409,21 +409,21 @@ class QueryBuilder(Selectable, Term):
         """
         return self._list_aliases(self._groupbys)
 
-    def do_join(self, item, criterion, how):
-        self._validate_join(criterion, item)
+    def do_join(self, join):
+        join.validate(self._from, self._joins)
 
-        if isinstance(item, QueryBuilder):
-            self._tag_subquery(item)
+        if isinstance(join.item, QueryBuilder):
+            self._tag_subquery(join.item)
 
         table_in_from = any(isinstance(clause, Table)
-                            and item.table_name == self._from[0].table_name
+                            and join.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):
+        if isinstance(join.item, Table) and join.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'
+            join.item.alias = join.item.table_name + '2'
 
-        self._joins.append(Join(item, criterion, how))
+        self._joins.append(join)
 
     def _validate_term(self, term):
         for field in term.fields():
@@ -434,16 +434,6 @@ class QueryBuilder(Selectable, Term):
                                     '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 = (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 '
-                                'another from the selected table or an existing join.  Found [{tables}]'.format(
-                tables=', '.join(map(str, missing_tables))
-            ))
-
     def _tag_subquery(self, subquery):
         subquery.alias = 'sq%d' % self._subquery_count
         self._subquery_count += 1
@@ -495,10 +485,8 @@ class QueryBuilder(Selectable, Term):
             querystring += self._from_sql(**kwargs)
 
         if self._joins:
-            for join_item in self._joins:
-                if join_item.how.value:
-                    querystring += self._jointype_sql(join_item)
-                querystring += self._join_sql(join_item, **kwargs)
+            querystring += " " + " ".join(join.get_sql(**kwargs)
+                                          for join in self._joins)
 
         if self._wheres:
             querystring += self._where_sql(**kwargs)
@@ -571,15 +559,6 @@ class QueryBuilder(Selectable, Term):
             for clause in self._from
         ))
 
-    def _jointype_sql(self, join_item):
-        return ' {type}'.format(type=join_item.how.value)
-
-    def _join_sql(self, join, **kwargs):
-        return ' JOIN {table} ON {criterion}'.format(
-            table=join.item.get_sql(with_quotes=True, subquery=True, with_alias=True),
-            criterion=join.criteria.get_sql(with_quotes=True, **kwargs),
-        )
-
     def _where_sql(self, **kwargs):
         return ' WHERE {where}'.format(where=self._wheres.get_sql(with_quotes=True, subquery=True, **kwargs))
 
@@ -649,12 +628,67 @@ class Joiner(object):
             raise JoinException("Parameter 'on' is required when joining a "
                                 "{type} but was not supplied.".format(type=self.type_label))
 
-        self.query.do_join(self.item, criterion, self.how)
+        self.query.do_join(JoinOn(self.item, self.how, criterion))
+        return self.query
+
+    def using(self, *fields):
+        if not fields:
+            raise JoinException("Parameter 'fields' is required when joining with "
+                                "a using clause but was not supplied.".format(type=self.type_label))
+
+        self.query.do_join(JoinUsing(self.item, self.how, [Field(field) for field in fields]))
         return self.query
 
 
 class Join(object):
-    def __init__(self, item, criteria, how):
+    def __init__(self, item, how):
         self.item = item
-        self.criteria = criteria
         self.how = how
+
+    def get_sql(self, **kwargs):
+        sql = 'JOIN {table}'.format(
+            table=self.item.get_sql(with_quotes=True, subquery=True, with_alias=True),
+        )
+
+        if self.how.value:
+            return '{type} {join}'.format(join=sql, type=self.how.value)
+        return sql
+
+
+class JoinOn(Join):
+    def __init__(self, item, how, criteria):
+        super(JoinOn, self).__init__(item, how)
+        self.criterion = criteria
+
+    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(with_quotes=True, **kwargs),
+        )
+
+    def validate(self, _from, _joins):
+        criterion_tables = set([f.table for f in self.criterion.fields()])
+        available_tables = (set(_from) | {join.item for join in _joins} | {self.item})
+        missing_tables = criterion_tables - available_tables
+        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))
+            ))
+
+
+class JoinUsing(Join):
+    def __init__(self, item, how, fields):
+        super(JoinUsing, self).__init__(item, how)
+        self.fields = fields
+
+    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)
+        )
+
+    def validate(self, _from, _joins):
+        pass


Test patch:
diff --git a/pypika/tests/test_joins.py b/pypika/tests/test_joins.py
index ad423f9..7667ced 100644
--- a/pypika/tests/test_joins.py
+++ b/pypika/tests/test_joins.py
@@ -31,6 +31,16 @@ class JoinTypeTests(unittest.TestCase):
         self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query))
         self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query_explicit))
 
+    def test_join_using_string_field_name(self):
+        query = Query.from_(self.table0).join(self.table1).using('id').select('*')
+
+        self.assertEqual('SELECT * FROM "abc" JOIN "efg" USING ("id")', str(query))
+
+    def test_join_using_multiple_fields(self):
+        query = Query.from_(self.table0).join(self.table1).using('foo', 'bar').select('*')
+
+        self.assertEqual('SELECT * FROM "abc" JOIN "efg" USING ("foo","bar")', str(query))
+
     def test_outer_join(self):
         query = Query.from_(self.table0).join(self.table1, how=JoinType.outer).on(
             self.table0.foo == self.table1.bar).select('*')