python - How to use db functions in Tortoise ORM

Question

I am trying to write a simple query but using PSQL functions CURRENT_DATE and INTERVAL, for instance:

users = await User.filter(created_at__gt="CURRENT_DATE - INTERVAL '30 DAYS'")

How to make it work? Thanks

question from:https://stackoverflow.com/questions/65951082/how-to-use-db-functions-in-tortoise-orm

Answer 1

Unfortunately, Tortoise ORM processes different queries differently. For instance:

• for update query you can use just a string value:

await User.filter(id=user_id).update(updated_at="now()")

• for filter queries you can use pypika.functions and pypika.terms For instance:

from pypika.terms import Parameter, Interval

await User.filter(created_at__gte=Parameter("CURRENT_DATE") - Interval(days=30))

• for create queries it's very tricky. Tortoise ORM is not built for that and what you need to do is to make your own field type class by inheriting from tortoise.fields.data.DateField or tortoise.fields.data.DateTimeField and override to_db_value method.

Long story short, it is possible but very tricky, especially if you want to use all 3 types of queries: CREATE, UPDATE and SELECT.