tortoise / tortoise/tortoise-orm
Filtering with "gt", "gte", "lt" and "lte" - error.
Open
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 5.6k
- Forks
- 516
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 9
Description
Describe the bug
Can't use the specified modifiers. Tortoise-ORM v0.17.3
To Reproduce
The following example demonstrates the problem.
from tortoise import Tortoise, fields, run_async, transactions
from tortoise.models import Model
from datetime import datetime, timedelta
class NewData(Model):
id = fields.IntField(pk=True)
name = fields.CharField(max_length=20)
events: fields.ReverseRelation["Event"]
class Event(Model):
id = fields.IntField(pk=True)
name = fields.CharField(max_length=20)
date = fields.DatetimeField()
new_data: fields.ForeignKeyRelation["NewData"] = fields.ForeignKeyField(
"models.NewData", related_name="events"
)
async def run():
await Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
await Tortoise.generate_schemas()
d = await NewData().create(name="data_name_0")
async with transactions.in_transaction():
for i in range(10):
await Event.create(
name="event_name_" + str(i),
date=datetime.now(),
new_data_id=d.id
)
async for event in d.events:
print(event.name, "=>",event.date)
now = datetime.now()
day_ago = now - timedelta(days=1)
print("\nday_ago =", day_ago)
print("\n---------------------\nEvents with filter by date:")
async for event in d.events.filter(date__gt=day_ago.isoformat()):
print(event.name, event.date)
print(" =(\n---------------------\n")
print("Because sql look like this:")
print(d.events.filter(date__gt=day_ago.isoformat()).sql())
print(d.events.filter(date__gte=day_ago.isoformat()).sql())
print(d.events.filter(date__gt=day_ago.timestamp()).sql())
print(d.events.filter(date__gte=day_ago.timestamp()).sql())
print(d.events.filter(date__lt=now.isoformat()).sql())
print(d.events.filter(date__lte=now.isoformat()).sql())
print(d.events.filter(date__lt=now.timestamp()).sql())
print(d.events.filter(date__lte=now.timestamp()).sql())
print("\nBut if we exec this query with little fix and replace NULL on date:")
conn = Tortoise.get_connection("default")
SQL = (
'SELECT "date","name","id","new_data_id" FROM "event" WHERE "new_data_id"=1 AND "date">"' +
day_ago.isoformat() + '";'
)
SQL_TIMESTAMP = (
'SELECT "date","name","id","new_data_id" FROM "event" WHERE "new_data_id"=1 AND "date">' +
str(day_ago.timestamp()) + ';'
)
print("It worked with ISO:\n---------------------\n")
(row_count, result) = await conn.execute_query(SQL)
for r in result:
(date, name) = (r[0], r[1])
print(f"{name} => {date}")
print("\n---------------------\nAnd with timestamp:\n---------------------\n")
(row_count, result) = await conn.execute_query(SQL_TIMESTAMP)
for r in result:
(date, name) = (r[0], r[1])
print(f"{name} => {date}")
print("\n---------------------\n")
if __name__ == "__main__":
run_async(run())
Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
The reproduction uses the async d.events.filter(...) path with SQLite; start by running it and inspecting the SQL from .sql() and execute_query(). Done means date__gt, date__gte, date__lt, and date__lte with ISO and timestamp bounds generate valid comparisons and return the expected events.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sqlite
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 43/100