I use PostgreSQL in my project and have three related models:
class Timer(models.Model):
start = models.DateTimeField()
end = models.DateTimeField()
task = models.ForeignKey(
Task,
models.CASCADE,
related_name="timers",
)
class Task(models.Model):
name = models.CharField(max_length=64)
wanted_duration = models.DurationField()
frequency = models.ForeignKey(
Frequency,
models.CASCADE,
related_name="tasks",
)
class Frequency(models.Model):
class TimeUnitChoices(models.TextChoices):
DAY = "day", "day"
WEEK = "week", "week"
MONTH = "month", "month"
QUARTER = "quarter", "quarter"
YEAR = "year", "year"
events_number = models.PositiveIntegerField()
time_unit = models.CharField(max_length=32, choices=TimeUnitChoices.choices
)
I want to get a start of timespan (day, week - a value of Frequency's time_unit) according to start date (field of Timer).
I tried execute next code: task.timers.annotate(start_of=Trunc('start', kind='task__frequency__time_unit'))
But Django doesn't accept field in kind argument of Trunc class. Error: psycopg.ProgrammingError: cannot adapt type 'F' using placeholder '%t' (format: TEXT)
If I execute a following query in raw SQL:
SELECT
DATE_TRUNC(schedules_frequency.time_unit, timers_timer.start)::date as start_of
FROM public.tasks_task
INNER JOIN public.schedules_frequency ON tasks_task.frequency_id = schedules_frequency.id
INNER JOIN public.timers_timer ON timers_timer.task_id = tasks_task.id;
Everything works as wanted. Is there workaround without using raw SQL directly in the Django project?
The following code works:
class MyTrunc(Func):
def as_postgresql(self, compiler, connection):
return super().as_sql(compiler, connection, function="DATE_TRUNC")
But calling this function demand to swap kind with date when passing them:
task.timers.annotate(start_of=Trunc('task__frequency__time_unit', 'start'))
I'm not sure if this implementation is vulnerable to SQL injection