Issue: Example of custom operators for custom fields?
Author: eddyg
State: closed
Created: 2023-02-07 19:43:24

I've tried to find an instance of a custom field that needed to override the behavior of something like the `+` operator but haven't found one.

As an example scenario, let's say I had a custom field named `CounterDictField` (that subclassed `JSONField` for storage).

I want to override the `__add__` method in the `CounterDictField` class with code that behaves like this:

```python
# override '+' operator to numerically add together dictionary values with the same key
def __add__(lhs, rhs):
    return {key: lhs.get(key, 0) + rhs.get(key, 0) for key in set(lhs).union(rhs)}
```

so `+` _numerically sums_ the entries of the two dictionaries:

```python
stats = {"a": 1, "b": 2, "c": 3}
new_stats = {"b": -1, "d": 1}

stats = __add__(stats, new_stats)
# stats={'c': 3, 'd': 1, 'a': 1, 'b': 1}
# 'stats' now contains the result of numerically summing the values with matching keys in 'new_stats'
```

This is so that I can make an "upsert" call (where the `stats` column is the above `CounterDictField` field type):

```python
MyTable.insert(user_id=user_id, username=username, stats=stats).
    on_conflict(
        conflict_target=[MyTable.user_id],
        preserve=[MyTable.username],
        update={MyTable.stats: MyTable.stats + stats}
    ).execute()
```

and have the `stats` field _add to_ existing stats in the dict for records that already exist.

Is something like this possible?


You can find an example of a field with a custom `__add__` implementation in `peewee._StringField` which overrides the add operation to perform a string concatenation. The implementation looks like this:

```python
class _StringField(Field):
    def adapt(self, value):
        if isinstance(value, text_type):
            return value
        elif isinstance(value, bytes_type):
            return value.decode('utf-8')
        return text_type(value)

    def __add__(self, other): return StringExpression(self, OP.CONCAT, other)
    def __radd__(self, other): return StringExpression(other, OP.CONCAT, self)

class CharField(_StringField):
    # ...
```

And `StringExpression` is a subclass of `Expression` that similary overrides `__add__` so that additions can be chained or combined.

For your case I can't really make sense of what you're trying to do so it's hard to advise. Binary operations need to take two operands and output some kind of a self-contained expression in Peewee. For your case, if it is more complex than this, you probably are at the wrong API level and need to go higher-up.

I did see that example, but I was looking for something more complex; one that showed how to properly access the _current_ value of the field in the row, and return an updated value that was based on the current value.

Of course I could do this by issuing a `select()` to get the current values, updating the dictionary, and then running an `update()`, but I was hoping to be able to take advantage of PeeWee's custom fields and do it in a nice, clean "upsert" by overloading `+` to sum the dictionaries.

However, it seems like overriding operators with arbitrary functionality is not possible like I had initially envisioned; any operation you want to perform must exist in SQL, which is the key part I was missing.

As always, thanks for your time!

You shouldn't need to do a select. Depends on what db you use but I think your first step is to write a sql expression using the available json functions to perform the merge and add. Once you have the sql you can wrap it up however you want, whether via an operator overload or just a helper method.

I was fiddling around with how you might do this using Sqlite, here's a small example:

```sql
CREATE TABLE IF NOT EXISTS "reg" (
  "id" INTEGER NOT NULL PRIMARY KEY, 
  "key" TEXT NOT NULL, "data" JSON NOT NULL);

INSERT INTO "reg" ("key", "data") VALUES ('k1', json('{"a": 1, "b": 2, "c": 3}'));
INSERT INTO "reg" ("key", "data") VALUES ('k2', json('{"a": 10, "b": 20}'));

select coalesce(j.key, k.key), coalesce(j.value, 0) + coalesce(k.value, 0)
from (
  select json_each.key, json_each.value from reg, json_each(reg.data)
  where reg.key = 'k1') as j
full outer join (
  select key, value from json_each('{"a": 1, "c": 3, "e": 5}')) as k
on (j.key = k.key);
```

Output:

```
a 2
b 2
c 6
e 5
```

Wow, thanks for taking the time to think about this more. 🎉 I'm new to Peewee, so _very_ much appreciate your time and insight!

I definitely would not have thought of using `coaelsce()` for this. Your example query returns the summed result — is there any way to _update_ the `k1` row with the newly-summed value? (My thinking being that single-operation database events are better than multiple sequential operations that could potentially lead to race conditions.)

Thanks again! 👍 

>is there any way to update the k1 row with the newly-summed value?

Probably but that is left as an exercise to the reader.

Fair enough, just didn't want to spin my wheels unnecessarily. Thanks again.

You know, I'm not sure if you're using sqlite, but if so you might try using a user-defined function:

```python
import json
from peewee import *
from playhouse.sqlite_ext import *

db = SqliteExtDatabase(':memory:')
class Reg(db.Model):
    key = TextField(unique=True)
    data = JSONField()
db.create_tables([Reg])

Reg.create(key='k1', data={'a': 1, 'b': 2, 'c': 3})

@db.func()
def merge_sum(j1, j2):
    d1 = json.loads(j1) if j1 else {}
    d2 = json.loads(j2) if j2 else {}
    for k, v in d2.items():
        d1.setdefault(k, 0)
        d1[k] += v
    return json.dumps(d1)

q = (Reg
     .insert(key='k1', data={'a': 1, 'c': 3, 'e': 5})
     .on_conflict(
         conflict_target=[Reg.key],
         update={Reg.data: fn.merge_sum(Reg.data, EXCLUDED.data)})
     .execute())

print(Reg.get().data)
```

I am indeed using SQLite!

Thanks for the suggestion... I had _not_ thought of using a user-defined function — but that is pretty much the functionality _(no pun intended)_ I was trying to achieve by overloading the `+` operator.

Really appreciate you taking the time to follow-up on this (closed) issue with this solution. You rock! 🎉