Repository: tortoise/tortoise-orm

Issue:
F Expression for atomic update?
I'm looking for a way to do an atomic update of a field or multiple fields (to increment it)

In Django ORM this is possible using the `F` class, I didn't see anything equivalent in tortoise documentation.

The goal would be to achieve something like this

```
counter = Counter.objects.get_or_create(name = name)
counter.count = F('count') +1
counter.save( update_fields=["count"] )
```

To generate some SQL like

`update counter set count = count + 1`

(In my case I also want to cast in some cases)

`update counter set count = cast(cast(count as integer) + 1) as char)`

Wondering if either of the above are possible yet with tortoise.


Discussion:

Patch:
diff --git a/CHANGELOG.rst b/CHANGELOG.rst
index 7dbfcdf..68bad76 100644
--- a/CHANGELOG.rst
+++ b/CHANGELOG.rst
@@ -2,6 +2,10 @@
 
 Changelog
 =========
+0.15.14
+-------
+- add F expression support in queryset.update()
+
 0.15.13
 -------
 - Applies default ordering on related queries
diff --git a/docs/query.rst b/docs/query.rst
index b8751ec..edc2358 100644
--- a/docs/query.rst
+++ b/docs/query.rst
@@ -282,3 +282,19 @@ You can view full example here:  :ref:`example_prefetching`
 
 .. autoclass:: tortoise.query_utils.Prefetch
     :members:
+
+
+F expression
+================
+
+An F() object represents the value of a model field. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory.
+
+For example to use ``F`` to update user balance atomic:
+
+.. code-block:: python3
+
+    from tortoise.expressions import F
+    await User.filter(id=1).update(balance = F('balance') - 10)
+    await User.filter(id=1).update(balance = F('balance') + F('award'), award = 0)
+
+Only support ``F`` expressions as part of a ``.update()`` queryset parameter for now.
\ No newline at end of file
diff --git a/tortoise/expressions.py b/tortoise/expressions.py
new file mode 100644
index 0000000..8356186
--- /dev/null
+++ b/tortoise/expressions.py
@@ -0,0 +1,32 @@
+from typing import Union
+
+from pypika import Field
+from pypika.terms import ArithmeticExpression
+
+from tortoise.exceptions import FieldError
+
+
+class F(Field):  # type: ignore
+    @classmethod
+    def resolver_arithmetic_expression(
+        cls,
+        fields_db_projection: dict,
+        arithmetic_expression_or_field: Union[ArithmeticExpression, Field],
+    ):
+        if isinstance(arithmetic_expression_or_field, Field):
+            name = arithmetic_expression_or_field.name
+            try:
+                name = fields_db_projection[name]
+            except KeyError:
+                raise FieldError(f"Field {name} is virtual and can not be updated")
+            arithmetic_expression_or_field.name = name
+        elif isinstance(arithmetic_expression_or_field, ArithmeticExpression):
+            left = arithmetic_expression_or_field.left
+            right = arithmetic_expression_or_field.right
+            arithmetic_expression_or_field.left = cls.resolver_arithmetic_expression(
+                fields_db_projection, left
+            )
+            arithmetic_expression_or_field.right = cls.resolver_arithmetic_expression(
+                fields_db_projection, right
+            )
+        return arithmetic_expression_or_field
diff --git a/tortoise/queryset.py b/tortoise/queryset.py
index 91d0cc3..7b063d1 100644
--- a/tortoise/queryset.py
+++ b/tortoise/queryset.py
@@ -22,10 +22,12 @@ from typing import (
 from pypika import JoinType, Order, Table
 from pypika.functions import Count
 from pypika.queries import QueryBuilder
+from pypika.terms import ArithmeticExpression
 from typing_extensions import Protocol
 
 from tortoise.backends.base.client import BaseDBAsyncClient, Capabilities
 from tortoise.exceptions import DoesNotExist, FieldError, IntegrityError, MultipleObjectsReturned
+from tortoise.expressions import F
 from tortoise.fields.relational import (
     ForeignKeyFieldInstance,
     OneToOneFieldInstance,
@@ -658,7 +660,12 @@ class UpdateQuery(AwaitableQuery):
                     db_field = self.model._meta.fields_db_projection[key]
                 except KeyError:
                     raise FieldError(f"Field {key} is virtual and can not be updated")
-                value = executor.column_map[key](value, None)
+                if not isinstance(value, (F, ArithmeticExpression)):
+                    value = executor.column_map[key](value, None)  # type: ignore
+                else:
+                    value = F.resolver_arithmetic_expression(
+                        self.model._meta.fields_db_projection, value
+                    )
 
             self.query = self.query.set(db_field, value)
 


Test patch:
diff --git a/tests/fields/test_decimal.py b/tests/fields/test_decimal.py
index 42375f9..50c0f52 100644
--- a/tests/fields/test_decimal.py
+++ b/tests/fields/test_decimal.py
@@ -4,6 +4,7 @@ from tests import testmodels
 from tortoise import fields
 from tortoise.contrib import test
 from tortoise.exceptions import ConfigurationError, IntegrityError
+from tortoise.expressions import F
 from tortoise.functions import Avg, Max, Sum
 
 
@@ -51,6 +52,12 @@ class TestDecimalFields(test.TestCase):
         self.assertEqual(obj.decimal_nodec, 19)
         self.assertEqual(obj.decimal_null, None)
 
+    async def test_f_expression(self):
+        obj0 = await testmodels.DecimalFields.create(decimal=Decimal("1.23456"), decimal_nodec=18.7)
+        await obj0.filter(id=obj0.id).update(decimal=F("decimal") + Decimal("1"))
+        obj1 = await testmodels.DecimalFields.get(id=obj0.id)
+        self.assertEqual(obj1.decimal, Decimal("2.2346"))
+
     async def test_values(self):
         obj0 = await testmodels.DecimalFields.create(decimal=Decimal("1.23456"), decimal_nodec=18.7)
         values = await testmodels.DecimalFields.get(id=obj0.id).values("decimal", "decimal_nodec")
diff --git a/tests/fields/test_float.py b/tests/fields/test_float.py
index cf6b043..ed6367d 100644
--- a/tests/fields/test_float.py
+++ b/tests/fields/test_float.py
@@ -3,6 +3,7 @@ from decimal import Decimal
 from tests import testmodels
 from tortoise.contrib import test
 from tortoise.exceptions import IntegrityError
+from tortoise.expressions import F
 
 
 class TestFloatFields(test.TestCase):
@@ -47,3 +48,9 @@ class TestFloatFields(test.TestCase):
         obj0 = await testmodels.FloatFields.create(floatnum=1.23)
         values = await testmodels.FloatFields.filter(id=obj0.id).values_list("floatnum")
         self.assertEqual(list(values[0]), [1.23])
+
+    async def test_f_expression(self):
+        obj0 = await testmodels.FloatFields.create(floatnum=1.23)
+        await obj0.filter(id=obj0.id).update(floatnum=F("floatnum") + 0.01)
+        obj1 = await testmodels.FloatFields.get(id=obj0.id)
+        self.assertEqual(obj1.floatnum, 1.24)
diff --git a/tests/fields/test_int.py b/tests/fields/test_int.py
index 3f1f867..f05ab9d 100644
--- a/tests/fields/test_int.py
+++ b/tests/fields/test_int.py
@@ -1,6 +1,7 @@
 from tests import testmodels
 from tortoise.contrib import test
 from tortoise.exceptions import IntegrityError
+from tortoise.expressions import F
 
 
 class TestIntFields(test.TestCase):
@@ -52,6 +53,12 @@ class TestIntFields(test.TestCase):
         values = await testmodels.IntFields.get(id=obj0.id).values_list("intnum", flat=True)
         self.assertEqual(values[0], 1)
 
+    async def test_f_expression(self):
+        obj0 = await testmodels.IntFields.create(intnum=1)
+        await obj0.filter(id=obj0.id).update(intnum=F("intnum") + 1)
+        obj1 = await testmodels.IntFields.get(id=obj0.id)
+        self.assertEqual(obj1.intnum, 2)
+
 
 class TestSmallIntFields(test.TestCase):
     async def test_empty(self):
@@ -88,6 +95,12 @@ class TestSmallIntFields(test.TestCase):
         )
         self.assertEqual(values[0], 2)
 
+    async def test_f_expression(self):
+        obj0 = await testmodels.SmallIntFields.create(smallintnum=1)
+        await obj0.filter(id=obj0.id).update(smallintnum=F("smallintnum") + 1)
+        obj1 = await testmodels.SmallIntFields.get(id=obj0.id)
+        self.assertEqual(obj1.smallintnum, 2)
+
 
 class TestBigIntFields(test.TestCase):
     async def test_empty(self):
@@ -126,3 +139,9 @@ class TestBigIntFields(test.TestCase):
         obj0 = await testmodels.BigIntFields.create(intnum=1)
         values = await testmodels.BigIntFields.get(id=obj0.id).values_list("intnum", flat=True)
         self.assertEqual(values[0], 1)
+
+    async def test_f_expression(self):
+        obj0 = await testmodels.BigIntFields.create(intnum=1)
+        await obj0.filter(id=obj0.id).update(intnum=F("intnum") + 1)
+        obj1 = await testmodels.BigIntFields.get(id=obj0.id)
+        self.assertEqual(obj1.intnum, 2)
diff --git a/tests/test_queryset.py b/tests/test_queryset.py
index 84e8ff1..a1c50fe 100644
--- a/tests/test_queryset.py
+++ b/tests/test_queryset.py
@@ -3,6 +3,7 @@ import sys
 from tests.testmodels import IntFields, MinRelation, Tournament
 from tortoise.contrib import test
 from tortoise.exceptions import DoesNotExist, FieldError, IntegrityError, MultipleObjectsReturned
+from tortoise.expressions import F
 
 # TODO: Test the many exceptions in QuerySet
 # TODO: .filter(intnum_null=None) does not work as expected
@@ -216,6 +217,12 @@ class TestQueryset(test.TestCase):
         self.assertEqual(obj.intnum, 2147483646)
         self.assertEqual(obj.intnum_null, None)
 
+    async def test_update_f_expression(self):
+        obj0 = await IntFields.create(intnum=2147483647)
+        await IntFields.filter(id=obj0.id).update(intnum=F("intnum") - 1)
+        obj = await IntFields.get(id=obj0.id)
+        self.assertEqual(obj.intnum, 2147483646)
+
     async def test_update_badparam(self):
         obj0 = await IntFields.create(intnum=2147483647)
         with self.assertRaisesRegex(FieldError, "Unknown keyword argument"):
diff --git a/tests/test_source_field.py b/tests/test_source_field.py
index c986442..0e44bdf 100644
--- a/tests/test_source_field.py
+++ b/tests/test_source_field.py
@@ -6,6 +6,7 @@ This is to test that behaviour doesn't change when one defined source_field para
 """
 from tests.testmodels import SourceFields, StraightFields
 from tortoise.contrib import test
+from tortoise.expressions import F
 
 
 class StraightFieldTests(test.TestCase):
@@ -160,6 +161,12 @@ class StraightFieldTests(test.TestCase):
         obj1a = await self.model.get(eyedee=obj1.eyedee).prefetch_related("rel_to")
         self.assertEqual(list(obj1a.rel_to), [obj2])
 
+    async def test_f_expression(self):
+        obj1 = await self.model.create(chars="aaa")
+        await self.model.filter(eyedee=obj1.eyedee).update(chars=F("blip"))
+        obj2 = await self.model.get(eyedee=obj1.eyedee)
+        self.assertEqual(obj2.chars, "BLIP")
+
 
 class SourceFieldTests(StraightFieldTests):
     def setUp(self) -> None: