Django复杂查询问题：筛选到期比例达5/6的培训记录

需要筛选培训到期比例达5/6（即超过83.3%）的用户发送提醒邮件，调度系统每日执行筛选，但查询语句报错。

模型定义 #

class Training(models.Model):

    class Meta:
        ordering = ('-pk',)

    uuid = models.UUIDField(default=uuid.uuid4, editable=False)
    customer = models.ForeignKey('customer.Customer', on_delete=models.CASCADE, related_name='trainings')
    name = models.CharField(max_length=512)
    presentation = models.FileField(upload_to='trainings')
    data = models.JSONField()
    description = models.TextField(default='', blank=True)
    released = models.BooleanField(default=False)
    start_at = models.DateField()
    end_at = models.DateField()
    codes_file = models.FileField(null=True, max_length=1024)
    contact = models.EmailField(blank=True)
    is_archived = models.BooleanField(default=False)
    is_reminded = models.BooleanField(default=False)

尝试的查询语句 #

trainings = Training.objects.filter(is_reminded=False, ).annotate(
    deadline=(datetime.today() - F('start_at')).days / (F('start_at') - F('end_at')).days, output_field=DateField()
).filter(deadline__gt=0.833)

报错信息 #

Internal Server Error: /testing/
Traceback (most recent call last):
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\django\core\handlers\exception.py", line 47, in inner
    response = get_response(request)
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\django\core\handlers\base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\django\views\decorators\csrf.py", line 54, in wrapped_view
    return view_func(*args, **kwargs)
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\rest_framework\viewsets.py", line 125, in view
    return self.dispatch(request, *args, **kwargs)
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\rest_framework\views.py", line 509, in dispatch
    response = self.handle_exception(exc)
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\rest_framework\views.py", line 469, in handle_exception
    self.raise_uncaught_exception(exc)
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\rest_framework\views.py", line 480, in raise_uncaught_exception
    raise exc
  File "C:\Users\HP\.virtualenvs\apaa_backend-tCJa5vIy\lib\site-packages\rest_framework\views.py", line 506, in dispatch
    response = handler(request, *args, **kwargs)
  File "C:\Users\HP\Desktop\apaa_backend\apaa\testing.py", line 22, in list_deadline_trainings
    deadline=(datetime.today() - F('start_at')).days / (F('start_at') - F('end_at')).days, output_field=DateField()
AttributeError: 'CombinedExpression' object has no attribute 'days'

报错根源是Django的F表达式是数据库层面的运算对象，不能直接调用Python的.days属性。需要用ExpressionWrapper包裹日期运算，指定数值类型后再计算比例。

正确查询语句 #

from django.db.models import F, ExpressionWrapper, FloatField
from django.utils import timezone

today = timezone.now().date()

trainings = Training.objects.filter(
    is_reminded=False,
    is_archived=False,
    released=True,
    end_at__gt=F('start_at')  # 避免培训时长为0的情况
).annotate(
    # 计算培训总时长（天数）
    total_days=ExpressionWrapper(F('end_at') - F('start_at'), output_field=FloatField()),
    # 计算已过去的天数
    elapsed_days=ExpressionWrapper(today - F('start_at'), output_field=FloatField())
).annotate(
    # 计算到期比例：已过去天数/总时长
    deadline_ratio=ExpressionWrapper(F('elapsed_days') / F('total_days'), output_field=FloatField())
).filter(
    deadline_ratio__gt=5/6
)

关键说明 #

1. 用ExpressionWrapper处理日期运算：将日期差转换为浮点型数值，确保数据库能正确执行计算。
2. 拆分计算步骤：先算总时长和已过去天数，再计算比例，逻辑更清晰，避免数据库运算报错。
3. 过滤无效数据：添加is_archived=False、released=True和end_at__gt=F('start_at')，只处理有效且有实际时长的培训（可根据业务调整）。
4. 时区一致性：用timezone.now().date()替代datetime.today()，避免时区差异导致的日期计算错误。

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