Repository: kayak/pypika (python)

Issue:
Add EmptyCriterion for easier generic queries
There are cases when you are building query complex criterion based on some external checks.
And in those cases you could want to have some empty criterion to build you complex criterion from it.

Basic case, where you have dict of argument and want to have any corresponding rows, could look like this:
```
criterion = EmptyCriterion()

for key, value in filters.items():
    criterion |= (table.key == value)

query = query.where(criterion)
```

Which right now would look like this:
```
criterion = None

for key, value in filters.items():
    if not criterion:
        criterion = (table.key == value)
    else:
        criterion |= (table.key == value)

if criterion:
    query = query.where(criterion) 
```

Also, there could be more complex cases where you got two criterions from some generic functions, and there you would have to do:
```
if not criterion1 and criterion2:
    res_criterion = criterion2
elif criterion1 and not criterion2:
    res_criterion = criterion1
else:
   res_criterion = criterion1 | criterion2
```
While, if functions could return EmptyCriterion, you could just do:
```
res_criterion = criterion1 | criterion2
```

Does this seem like good idea for you? If you approve this design I could try implementing it

Pull request:
Empty Criterion
Fixes #196 Added an EmptyCriterion class which allows the user to build up boolean clauses using lists of constituents. Added any and all static methods to Criterion to give an easy syntax for using this.

Patch:
diff --git a/README.rst b/README.rst
index d01df1d..ae46faa 100644
--- a/README.rst
+++ b/README.rst
@@ -257,6 +257,32 @@ XOR
     SELECT id,fname,lname,phone FROM customers WHERE age>=18 XOR is_registered
 
 
+Convenience Methods
+"""""""""""""""""""
+
+In the `Criterion` class, there are the static methods `any` and `all` that allow building chains AND and OR expressions with a list of terms.
+
+.. code-block:: python
+
+    from pypika import Criterion
+
+    customers = Table('customers')
+    q = Query.from_(customers).select(
+        customers.id,
+        customers.fname
+    ).where(
+        Criterion.all([
+            customers.is_registered,
+            customers.age >= 18,
+            customers.lname == "Jones",
+        ])
+    )
+
+.. code-block:: sql
+
+    SELECT id,fname FROM customers WHERE is_registered AND age>=18 AND lname = "Jones"
+
+
 Grouping and Aggregating
 """"""""""""""""""""""""
 
diff --git a/pypika/__init__.py b/pypika/__init__.py
index 0e3cd2f..d02b878 100644
--- a/pypika/__init__.py
+++ b/pypika/__init__.py
@@ -58,6 +58,8 @@ from .queries import (
 # noinspection PyUnresolvedReferences
 from .terms import (
     Case,
+    Criterion,
+    EmptyCriterion,
     Field,
     Interval,
     Not,
@@ -75,4 +77,4 @@ from .utils import (
 
 __author__ = "Timothy Heys"
 __email__ = "theys@kayak.com"
-__version__ = "0.18.4"
+__version__ = "0.18.3"
diff --git a/pypika/enums.py b/pypika/enums.py
index 8ac1e5f..b85320f 100644
--- a/pypika/enums.py
+++ b/pypika/enums.py
@@ -38,6 +38,8 @@ class Boolean(Comparator):
     and_ = 'AND'
     or_ = 'OR'
     xor_ = 'XOR'
+    true = 'TRUE'
+    false = 'FALSE'
 
 
 class Order(Enum):
diff --git a/pypika/queries.py b/pypika/queries.py
index 75621fb..ec36000 100644
--- a/pypika/queries.py
+++ b/pypika/queries.py
@@ -1,4 +1,3 @@
-# coding: utf-8
 from functools import reduce
 
 from .enums import (
@@ -7,6 +6,7 @@ from .enums import (
 )
 from .terms import (
     ArithmeticExpression,
+    EmptyCriterion,
     Field,
     Function,
     Rollup,
@@ -517,6 +517,9 @@ class QueryBuilder(Selectable, Term):
 
     @builder
     def where(self, criterion):
+        if isinstance(criterion, EmptyCriterion):
+            return
+
         self._validate_term(criterion)
 
         if self._wheres:
diff --git a/pypika/terms.py b/pypika/terms.py
index bb441af..bbe308b 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -292,6 +292,24 @@ class Criterion(Term):
     def __xor__(self, other):
         return ComplexCriterion(Boolean.xor_, self, other)
 
+    @staticmethod
+    def any(terms=()):
+        crit = EmptyCriterion()
+
+        for term in terms:
+            crit |= term
+
+        return crit
+
+    @staticmethod
+    def all(terms=()):
+        crit = EmptyCriterion()
+
+        for term in terms:
+            crit &= term
+
+        return crit
+
     def fields(self):
         raise NotImplementedError()
 
@@ -299,20 +317,23 @@ class Criterion(Term):
         raise NotImplementedError()
 
 
-class Field(Criterion):
-    def __init__(self, name, alias=None, table=None):
-        super(Field, self).__init__(alias)
-        self.name = name
-        self.table = table
-
+class EmptyCriterion:
     def __and__(self, other):
-        return ComplexCriterion(Boolean.and_, self, other)
+        return other
 
     def __or__(self, other):
-        return ComplexCriterion(Boolean.or_, self, other)
+        return other
 
     def __xor__(self, other):
-        return ComplexCriterion(Boolean.xor_, self, other)
+        return other
+
+
+
+class Field(Criterion):
+    def __init__(self, name, alias=None, table=None):
+        super(Field, self).__init__(alias)
+        self.name = name
+        self.table = table
 
     def fields(self):
         return [self]
@@ -747,7 +768,10 @@ class Function(Criterion):
                 for table in param.tables_}
 
     def fields(self):
-        return self.args
+        return [field
+                for param in self.args
+                if hasattr(param, 'fields')
+                for field in param.fields()]
 
     @property
     def is_aggregate(self):
@@ -800,12 +824,6 @@ class Function(Criterion):
 
         return alias_sql(function_sql, self.alias, quote_char)
 
-    def fields(self):
-        return [field
-                for param in self.args
-                if hasattr(param, 'fields')
-                for field in param.fields()]
-
 
 class AggregateFunction(Function):
     is_aggregate = True
diff --git a/requirements.txt b/requirements.txt
index a9b7146..5cefa40 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -1,1 +1,1 @@
-aenum==2.1.2
+aenum==2.0.8


Test patch:
diff --git a/pypika/tests/test_criterions.py b/pypika/tests/test_criterions.py
index 1e1e4a3..b0cddc5 100644
--- a/pypika/tests/test_criterions.py
+++ b/pypika/tests/test_criterions.py
@@ -1,12 +1,12 @@
-# coding: utf-8
 import unittest
-
 from datetime import (
     date,
     datetime,
 )
 
 from pypika import (
+    Criterion,
+    EmptyCriterion,
     Field,
     Table,
     functions as fn,
@@ -650,3 +650,43 @@ class CriterionOperationsTests(unittest.TestCase):
         f = self.table_abc.foo.isnull().for_(self.table_efg)
 
         self.assertEqual('"cx1"."foo" IS NULL', str(f))
+
+
+class AnyTests(unittest.TestCase):
+    def test_zero_args_returns_empty_criterion(self):
+        crit = Criterion.any()
+        self.assertIsInstance(crit, EmptyCriterion)
+
+    def test_single_arg_returns_self(self):
+        f = Field('a')
+        crit = Criterion.any([f])
+        self.assertEqual(str(f), str(crit))
+
+    def test_multiple_args_returned_in_chain_of_ors(self):
+        crit = Criterion.any([Field('a'), Field('b'), Field('c'), Field('d')])
+        self.assertEqual(str(crit), '"a" OR "b" OR "c" OR "d"')
+
+    def test_with_generator(self):
+        crit = Criterion.any(Field(letter)
+                             for letter in 'abcd')
+        self.assertEqual(str(crit), '"a" OR "b" OR "c" OR "d"')
+
+
+class AllTests(unittest.TestCase):
+    def test_zero_args_returns_empty_criterion(self):
+        crit = Criterion.all()
+        self.assertIsInstance(crit, EmptyCriterion)
+
+    def test_single_arg_returns_self(self):
+        f = Field('a')
+        crit = Criterion.all([f])
+        self.assertEqual(str(f), str(crit))
+
+    def test_multiple_args_returned_in_chain_of_ors(self):
+        crit = Criterion.all([Field('a'), Field('b'), Field('c'), Field('d')])
+        self.assertEqual(str(crit), '"a" AND "b" AND "c" AND "d"')
+
+    def test_with_generator(self):
+        crit = Criterion.all(Field(letter)
+                             for letter in 'abcd')
+        self.assertEqual(str(crit), '"a" AND "b" AND "c" AND "d"')
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index dea79e9..1f3a14f 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -4,6 +4,7 @@ import unittest
 from pypika import (
     AliasedQuery,
     Case,
+    EmptyCriterion,
     Field as F,
     MSSQLQuery,
     MySQLQuery,
@@ -301,6 +302,11 @@ class WhereTests(unittest.TestCase):
 
         self.assertEqual("SELECT * FROM \"abc\" WHERE \"foo\" REGEX '^b'", str(q))
 
+    def test_ignore_empty_criterion(self):
+        q1 = Query.from_(self.t).select('*').where(EmptyCriterion())
+
+        self.assertEqual('SELECT * FROM "abc"', str(q1))
+
 
 class PreWhereTests(WhereTests):
     t = Table('abc')