Django中使用字段作为Trunc的kind参数的非原生SQL解决方案

我的项目使用PostgreSQL，包含三个关联模型：

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)

我需要根据Timer的start字段，获取Frequency的time_unit指定的时间跨度（如天、周）的起始值。

尝试执行以下代码时：

task.timers.annotate(start_of=Trunc('start', kind='task__frequency__time_unit'))

Django报错：

psycopg.ProgrammingError: cannot adapt type 'F' using placeholder '%t' (format: TEXT)

直接执行原生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;

请问是否有无需直接使用原生SQL的解决办法？

有两种基于Django ORM的实现方式，无需编写完整原生SQL：

方法一：使用Case-When分支匹配时间单位 #

利用Django的Case和When，针对每个time_unit选项调用对应的Trunc函数：

from django.db.models import Case, When, Value
from django.db.models.functions import TruncDay, TruncWeek, TruncMonth, TruncQuarter, TruncYear

# 为Timer实例添加start_of字段
timers_with_start_of = task.timers.annotate(
    start_of=Case(
        When(task__frequency__time_unit=Frequency.TimeUnitChoices.DAY, then=TruncDay('start')),
        When(task__frequency__time_unit=Frequency.TimeUnitChoices.WEEK, then=TruncWeek('start')),
        When(task__frequency__time_unit=Frequency.TimeUnitChoices.MONTH, then=TruncMonth('start')),
        When(task__frequency__time_unit=Frequency.TimeUnitChoices.QUARTER, then=TruncQuarter('start')),
        When(task__frequency__time_unit=Frequency.TimeUnitChoices.YEAR, then=TruncYear('start')),
        default=Value(None),  # 根据业务需求设置默认值
        output_field=models.DateField(),
    )
)

这个方法会根据每条记录对应的time_unit值，动态选择对应的截断函数，生成的SQL逻辑和原生查询一致。

方法二：自定义Func调用PostgreSQL的DATE_TRUNC #

直接封装PostgreSQL的DATE_TRUNC函数为Django ORM可识别的Func类：

from django.db.models import Func, F
from django.db import models

class DateTrunc(Func):
    function = 'DATE_TRUNC'
    template = "%(function)s(%(expressions)s)::date"

# 生成start_of字段
timers_with_start_of = task.timers.annotate(
    start_of=DateTrunc(F('task__frequency__time_unit'), F('start'))
)

这种方式最接近原生SQL的写法，通过自定义Func模板，直接传递time_unit字段和start字段作为参数给DATE_TRUNC，并转换为日期类型。

内容的提问来源于stack exchange，提问作者user20928849