djangopostgresqldjango-modelsdjango-postgresql

Using field in Trunc's kind property


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?


Solution

  • 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