Django中如何筛选isCompleted=True的订单计算对应Gig的平均完成时间

修改方案 #

你需要修改两处核心逻辑：视图层的聚合查询、模型层的备用计算属性，同时修复原代码中聚合结果取值错误的问题。

1. 修改Views.py #

在聚合函数Avg中新增filter参数，仅统计isCompleted=True的订单，需要先导入Q查询类：

from django.db.models import Q, DurationField, ExpressionWrapper, F, Avg

class RetrieveGigsAPI(GenericAPIView, RetrieveModelMixin):
    def get_queryset(self):
        return Gigs.objects.annotate(
            _average_completionTime=Avg(
                ExpressionWrapper(F('gig__orderCompletedTime') - F('gig__orderStartTime'), output_field=DurationField()),
                # 新增过滤条件，仅统计已完成订单
                filter=Q(gig__isCompleted=True)
            )
        )
    serializer_class = GigsSerializerWithAvgTime
    permission_classes = (AllowAny,)

    def get(self, request , *args, **kwargs):
        return self.retrieve(request, *args, **kwargs)

2. 修改Models.py中Gigs类的计算属性 #

原属性没有过滤未完成订单，且直接返回聚合字典而非时长值，修改如下：

from django.db.models import F, Avg, DurationField

class Gigs(models.Model):
    # 原有字段保持不变
    title = models.CharField(max_length=255)
    category = models.ForeignKey(Categories , on_delete=models.CASCADE)
    images = models.ImageField(blank=True, null = True, upload_to= upload_path)
    price = models.DecimalField(max_digits=6, decimal_places=2)
    details = models.TextField()
    seller = models.ForeignKey(User,default=None, on_delete=models.CASCADE)

    @property
    def average_completionTime(self):
        if getattr(self, '_average_completionTime', None):
            return self._average_completionTime
        # 过滤已完成订单后再聚合，同时取聚合结果的具体值而非字典
        agg_res = self.gig.filter(isCompleted=True).aggregate(
            avg_time=Avg(F('orderCompletedTime') - F('orderStartTime'), output_field=DurationField())
        )
        # 没有已完成订单时返回None，也可根据业务需求返回默认值比如timedelta(0)
        return agg_res.get('avg_time')

可选优化：格式化时长输出 #

如果需要给前端返回更易读的格式（比如总小时数），可以在序列化器中做转换：

class GigsSerializerWithAvgTime(serializers.ModelSerializer):
    average_completionTime = serializers.SerializerMethodField()
    def get_average_completionTime(self, obj):
        duration = obj.average_completionTime
        if not duration:
            return 0
        # 示例：返回总小时数，保留1位小数
        return round(duration.total_seconds() / 3600, 1)
    class Meta:
        model = Gigs
        fields = ['id','title','category','price','details','seller','images','average_completionTime']

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