DRF:聚合相似条目名称

Question

我想按名称合并条目，并在它们的其他字段中执行附加操作。

在本例中，我希望获得具有相同项目名称的条目的total_cost和average_pct。

假设：

# models.py
class Project(models.Model):
    project = models.CharField(max_length=200)
    subproject = models.CharField(max_length=200)
    physical_pct = models.FloatField()
    cost = models.FloatField()

# serializers.py
class ProjectSerializer(serializers.ModelSerializer):

    class Meta:
        model = Project
        fields = '__all__'

# views.py
class ProjectsViewSet(viewsets.ModelViewSet):
    serializer_class = ProjectSerializer

    def get_queryset(self):
        queryset = Project.objects.all()
        return queryset

如果我有3个条目：

[
    {
        "project": "Project-X",
        "subproject": "Subproject-1",
        "physical_pct": 58,
        "cost": 1000.00
    },
    {
        "project": "Project-X",
        "subproject": "Subproject-2",
        "physical_pct": 100,
        "cost": 2000.00
    },
    {
        "project": "Project-Y",
        "subproject": "Subproject-1",
        "physical_pct": 73,
        "cost": 560.00
    }
]

我想要的输出：

[
    {
        "project": "Project-X",
        "average_pct": 79,
        "total_cost": 3000.00
    },
    {
        "project": "Project-Y",
        "average_pct": 73,
        "total_cost": 560.00
    }
]

更新1:

在值()的前提下，它必须根据字段参数按相似项进行分组。然而，当我尝试的时候：

# serializers.py
class ProjectSerializer(serializers.ModelSerializer):
    class Meta:
        model = Project
        fields = ['id', 'project', 'cost']

# views.py
from django.db.models import Avg, Sum

class ProjectsViewSet(viewsets.ModelViewSet):
    serializer_class = ProjectSerializer

    def get_queryset(self):
        queryset = Project.objects.values('project').\
                   annotate(total_cost=Sum('cost'))
        return queryset

我收到一个错误：

"Got KeyError when attempting to get a value for field `cost` on serializer `ProjectSerializer`.\nThe serializer field might be named incorrectly and not match any attribute or key on the `dict` instance.\nOriginal exception text was: 'cost'."

Answer 1

您可以在注释之前使用.values()按项目分组。

from django.db.models import Avg, Sum

Project.objects.values('project').annotate(
    total_cost=Sum('cost'),
    average_pct=Avg('physical_pct'),
)

对于您的更新，由于分组的原因，queryset中没有成本字段。.values()和.annotate()以外的字段都不会出现。要在序列化程序中使用总成本，需要使用SerializerMethodField()。

class ProjectSerializer(serializers.ModelSerializer):
    total_cost = serializers.SerializerMethodField()

    class Meta:
        model = Project
        fields = ['project', 'total_cost']

    def get_total_cost(self, instance):
        return instance.total_cost

如果您想用总成本来注释每个实例，而不是分组，并且您使用的是Django >= 2.0，那么您可以执行以下操作。

from django.db.models import F, Q, Sum

Project.objects.annotate(total_cost=Sum('cost', filter=Q(project=F('project'))))