Add documentation/examples and Upper,Lower (tortoise#225)

zoliszeredi · grigi · commit f089e9da89a2 · 2019-10-27T08:35:05.000+02:00
diff --git a/CHANGELOG.rst b/CHANGELOG.rst
@@ -14,7 +14,7 @@ Enhancements:
 - Models, Fields & QuerySets have significant type annotation improvements,
   leading to better IDE integration and more comprehensive static analysis.
 - Fetching records from the DB is now up to 25% faster.
-- Database functions ``Trim()``, ``Length()``, ``Coalesce()`` added to tortoise.functions module.
+- Database functions ``Trim()``, ``Length()``, ``Coalesce()``, ``Lower()``, ``Upper()`` added to tortoise.functions module.
 - Annotations can be selected inside ``Queryset.values()`` and ``Queryset.values_list()`` expressions.
 - Added support for Python 3.8
 - The Foreign Key property is now ``await``-able as long as one didn't populate it via ``.prefetch_related()``
diff --git a/docs/query.rst b/docs/query.rst
@@ -127,15 +127,19 @@ In cases like this you could use ``values()`` or ``values_list()`` to produce mo
     # And it will be done in one query
     events = await Event.filter(id__in=[1,2,3]).values('id', 'name', tournament_name='tournament__name')
 
-QuerySet also supports aggregation through ``.annotate()`` method
+QuerySet also supports aggregation and database functions through ``.annotate()`` method
 
 .. code-block:: python3
 
-    from tortoise.aggregation import Count
+    from tortoise.functions import Count, Trim, Lower, Upper, Coalesce
 
     # This query will fetch all tournaments with 10 or more events, and will
     # populate filed `.events_count` on instances with corresponding value
     await Tournament.annotate(events_count=Count('events')).filter(events_count__gte=10)
+    await Tournament.annotate(clean_name=Trim('name'))).filter(clean_name='tournament')
+    await Tournament.annotate(name_upper=Upper('name'))).filter(name_upper='TOURNAMENT')
+    await Tournament.annotate(name_lower=Lower('name'))).filter(name_lower='tournament')
+    await Tournament.annotate(desc_clean=Coalesce('desc', ''))).filter(desc_clean='')
 
 Check `examples <https://github.com/tortoise/tortoise-orm/tree/master/examples>`_ to see it all in work
 
diff --git a/examples/functions.py b/examples/functions.py
@@ -1,11 +1,12 @@
 from tortoise import Tortoise, fields, run_async
-from tortoise.aggregation import Count, Min, Sum
+from tortoise.functions import Coalesce, Count, Length, Lower, Min, Sum, Trim, Upper
 from tortoise.models import Model
 
 
 class Tournament(Model):
     id = fields.IntField(pk=True)
     name = fields.TextField()
+    desc = fields.TextField(null=True)
 
     def __str__(self):
         return self.name
@@ -34,9 +35,10 @@ def __str__(self):
 async def run():
     await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
     await Tortoise.generate_schemas()
-    tournament = await Tournament.create(name="New Tournament")
+    tournament = await Tournament.create(name="New Tournament", desc="great")
     await tournament.save()
     await Tournament.create(name="Second tournament")
+    await Tournament.create(name=" final tournament ")
     await Event(name="Without participants", tournament_id=tournament.id).save()
     event = Event(name="Test", tournament_id=tournament.id)
     await event.save()
@@ -56,6 +58,21 @@ async def run():
 
     print(await Event.all().annotate(tournament_test_id=Sum("tournament__id")).first())
 
+    print(await Tournament.annotate(clean_desciption=Coalesce("desc")).filter(clean_desciption=""))
+
+    print(
+        await Tournament.annotate(trimmed_name=Trim("name")).filter(trimmed_name="final tournament")
+    )
+
+    print(
+        await Tournament.annotate(name_len=Length("name")).filter(
+            name_len__gt=len("New Tournament")
+        )
+    )
+
+    print(await Tournament.annotate(name_lo=Lower("name")).filter(name_lo="new tournament"))
+    print(await Tournament.annotate(name_lo=Upper("name")).filter(name_lo="NEW TOURNAMENT"))
+
 
 if __name__ == "__main__":
     run_async(run())
diff --git a/tests/test_filtering.py b/tests/test_filtering.py
@@ -1,6 +1,6 @@
 from tests.testmodels import Event, IntFields, Reporter, Team, Tournament
 from tortoise.contrib import test
-from tortoise.functions import Coalesce, Count, Length, Trim
+from tortoise.functions import Coalesce, Count, Length, Lower, Trim, Upper
 from tortoise.query_utils import Q
 
 
@@ -330,6 +330,20 @@ async def test_filter_by_aggregation_field_comparison_length(self):
         self.assertEqual(len(tournaments), 1)
         self.assertSetEqual({t.name for t in tournaments}, {"Tournament"})
 
+    async def test_filter_by_annotation_lower(self):
+        await Tournament.create(name="Tournament")
+        await Tournament.create(name="NEW Tournament")
+        tournaments = await Tournament.annotate(name_lower=Lower("name"))
+        self.assertEqual(len(tournaments), 2)
+        self.assertSetEqual({t.name_lower for t in tournaments}, {"tournament", "new tournament"})
+
+    async def test_filter_by_annotation_upper(self):
+        await Tournament.create(name="ToUrnAmEnT")
+        await Tournament.create(name="new TOURnament")
+        tournaments = await Tournament.annotate(name_upper=Upper("name"))
+        self.assertEqual(len(tournaments), 2)
+        self.assertSetEqual({t.name_upper for t in tournaments}, {"TOURNAMENT", "NEW TOURNAMENT"})
+
     async def test_values_select_relation(self):
         with self.assertRaises(ValueError):
             tournament = await Tournament.create(name="New Tournament")
diff --git a/tortoise/functions.py b/tortoise/functions.py
@@ -3,10 +3,12 @@
 from pypika.functions import Coalesce as PypikaCoalesce
 from pypika.functions import Count as PypikaCount
 from pypika.functions import Length as PypikaLength
+from pypika.functions import Lower as PypikaLower
 from pypika.functions import Max as PypikaMax
 from pypika.functions import Min as PypikaMin
 from pypika.functions import Sum as PypikaSum
 from pypika.functions import Trim as PypikaTrim
+from pypika.functions import Upper as PypikaUpper
 from pypika.terms import AggregateFunction
 from pypika.terms import Function as BaseFunction
 
@@ -80,6 +82,14 @@ class Coalesce(Function):
     database_func = PypikaCoalesce
 
 
+class Lower(Function):
+    database_func = PypikaLower
+
+
+class Upper(Function):
+    database_func = PypikaUpper
+
+
 ##############################################################################
 # Aggregate functions
 ##############################################################################
