1

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

andnik
  • 2,405
  • 2
  • 22
  • 33

1 Answers1

2

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.

andnik
  • 2,405
  • 2
  • 22
  • 33
  • ModuleNotFoundError: No module named 'pypika' – WhyWhat Dec 04 '21 at 10:56
  • That's odd, Tortoise uses `pypika-tortoise` as a dependency, so it should be installed together with your tortoise package: https://github.com/tortoise/tortoise-orm/blob/develop/pyproject.toml#L38. Could you check your package folder? This is the pypika package: https://github.com/tortoise/pypika-tortoise/tree/main/pypika – andnik Dec 06 '21 at 09:21
  • @andnik would you happen to know how to query a `ArrayField` in Postgres created using `from tortoise.contrib.postgres.fields import ArrayField`? I posted a question here if you know: https://stackoverflow.com/questions/73192631/tortoise-orm-how-to-query-a-postgres-arrayfield-from-contrib-postgres-fields – enchance Aug 01 '22 at 11:09
  • Response in your link. – andnik Aug 02 '22 at 06:32