python - django 条件按值聚合外键

Question

TL;DR 我想让长注释查询（在下面的项目对象管理器部分）更加优雅和高效，并且能够在相关对象更新后自行重新计算。

背景 我正在建立一个市场 - 每个客户都应该能够创建一个包含零件的项目。零件是价格优惠的对象。

每个部分都包含一个状态枚举字段 - 因此我们可以知道它是否符合投标或已经在工作中。

我在用着

• Python 3.8.1
• Django-rest-framework 3.10.3
• django-url-过滤器 0.3.14
• Postgres 12.1

我需要的功能

• 项目应按其包含的部件状态过滤 - 具有特定条件。
• 项目状态的更新应更新所有相关部分。
• 可以使用同一个对象管理器创建、更新和删除项目。

零件模型 仅代码的相关部分：

class PartStatuses(Enum):
    Draft = "Saved but not published"
    PendingBID = "It's BIDing time!"
    Proposal = "All BIDs are set"
    PendingPO = "Waiting for vendor to approve PO"
    WorkInProgress = "Vendor has accepted a PO"
    OnItsWay = "The part is ready and now await to be delivered"
    Delivered = "Delivery process has ended"
    Disputed = "Open for Dispute"
    Closed = "Part has received"
    Paid = "Vendor received the payment"

    @classmethod
    def choices(cls):
        return [(key.name, key.value) for key in cls]

class Part(models.Model):
    id = models.AutoField(primary_key=True)
    title = models.CharField(max_length=100)
    project = models.ForeignKey('Project', on_delete=models.CASCADE, related_name='part')
    status = models.CharField(choices=PartStatuses.choices(),
                              max_length=100,
                              default=PartStatuses.Draft)
    ...

项目模型 起初，我尝试对项目状态字段使用属性方法，对部件状态更新使用 setter 方法。

class Project(models.Model):
    id = models.AutoField(primary_key=True)
    owner = models.ForeignKey(User, on_delete=models.CASCADE, editable=False,
                              limit_choices_to={'is_vendor': False})
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    ...

    def got_parts(self):
        return self.part.count()

    @property
    def _status(self):
        if self.got_parts(): 
            all_parts: object = self.part.values_list('status')

            # Project is done and paid
            if all_parts.filter(status=PartStatuses.Paid.value).count() == all_parts.count():
                return str(PartStatuses.Paid.name)

            # At least one parts are open for disputed
            if all_parts.filter(status=PartStatuses.Disputed.value).count() > 0:
                return str(PartStatuses.Disputed.name)

            # Some parts are draft
            if all_parts.filter(status=PartStatuses.Draft.value).count() > 0:
                return str(PartStatuses.Draft.name)

            # Some parts are pending to bid
            if all_parts.filter(status=PartStatuses.PendingBID.value).count() > 0:
                return str(PartStatuses.PendingBID.name)

            # Some parts are on proposal
            if all_parts.filter(status=PartStatuses.Proposal.value).count() > 0:
                return str(PartStatuses.Proposal.name)

            # Some parts are pending to vendor approve PO
            if all_parts.filter(status=PartStatuses.PendingPO.value).count() > 0:
                return str(PartStatuses.PendingPO.name)

            # Some parts are in progress of working
            if all_parts.filter(status=PartStatuses.WorkInProgress.value).count() > 0:
                return str(PartStatuses.WorkInProgress.name)

            # Some parts are on their way
            if all_parts.filter(status=PartStatuses.OnItsWay.value).count() > 0:
                return str(PartStatuses.OnItsWay.name)

            # Some parts has been marked by delivery guys as delivered
            if all_parts.filter(status=PartStatuses.Delivered.value).count() > 0:
                return str(PartStatuses.Delivered.name)

            # Some parts has been marked by customer as delivered
            if all_parts.filter(status=PartStatuses.Closed.value).count() > 0:
                return str(PartStatuses.Closed.name)

            # Error with the parts status mapping
            return "Other"

        else:  # No parts - this projects is draft
            return str(PartStatuses.Draft.name)

    @_status.setter
    def _status(self, status):
        if any(k[0] == status for k in PartStatuses.choices()) and self.got_parts():
            for part in self.part.all():
                part.status = PartStatuses[status].value
                part.save()

    def __str__(self):
        return self.title

很快我意识到没有选项可以将此属性作为字段查询，这是来自docs的引用：

查找中指定的字段必须是模型字段的名称

所以我必须添加我自己的自定义对象管理器，其条件与状态模型属性相同：

class ProjectManager(models.Manager):
    """QuerySet manager for Project class to add non-database fields."""

    def get_queryset(self):
        """Overrides the models.Manager method"""
        qs = super().get_queryset().annotate(
            parts_num=Count(F('part'), distinct=True),
            parts_paid=Count(F('part'), filter=Q(part__status__exact=PartStatuses.Paid.value), distinct=True),
            parts_disputed=Count(F('part'), filter=Q(part__status__exact=PartStatuses.Disputed.value), distinct=True),
            parts_draft=Count(F('part'), filter=Q(part__status__exact=PartStatuses.Draft.value), distinct=True),
            parts_pending_bid=Count(F('part'), filter=Q(part__status__exact=PartStatuses.PendingBID.value), distinct=True),
            parts_proposal=Count(F('part'), filter=Q(part__status__exact=PartStatuses.Proposal.value), distinct=True),
            parts_workin_progress=Count(F('part'), filter=Q(part__status__exact=PartStatuses.WorkInProgress.value),
                                        distinct=True),
            parts_pending_PO=Count(F('part'), filter=Q(part__status__exact=PartStatuses.PendingPO.value), distinct=True),
            parts_on_its_way=Count(F('part'), filter=Q(part__status__exact=PartStatuses.OnItsWay.value), distinct=True),
            parts_delivered=Count(F('part'), filter=Q(part__status__exact=PartStatuses.Delivered.value), distinct=True),
            parts_closed=Count(F('part'), filter=Q(part__status__exact=PartStatuses.Closed.value), distinct=True),
        ).annotate(
            status=Case(
                When(parts_num=0, then=Value(PartStatuses.Draft.name)),
                When(parts_paid=F('parts_num'), then=Value(PartStatuses.Paid.name)),
                When(parts_disputed__gt=0, then=Value(PartStatuses.Disputed.name)),
                When(parts_draft__gt=0, then=Value(PartStatuses.Draft.name)),
                When(parts_pending_bid__gt=0, then=Value(PartStatuses.PendingBID.name)),
                When(parts_proposal=F('parts_num'), then=Value(PartStatuses.Proposal.name)),
                When(parts_workin_progress__gt=0, then=Value(PartStatuses.WorkInProgress.name)),
                When(parts_pending_PO__gt=0, then=Value(PartStatuses.PendingPO.name)),
                When(parts_on_its_way__gt=0, then=Value(PartStatuses.OnItsWay.name)),
                When(parts_delivered__gt=0, then=Value(PartStatuses.Delivered.name)),
                When(parts_closed__gt=0, then=Value(PartStatuses.Closed.name)),
                default=Value("Other"),
                output_field=CharField()
            )
        )
        return qs

将经理添加到项目模型中：

class Project(models.Model):
    with_status = ProjectManager()
    objects = models.Manager()

    id = models.AutoField(primary_key=True)
    owner = models.ForeignKey(User, on_delete=models.CASCADE, editable=False,
                              limit_choices_to={'is_vendor': False})
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    title = models.CharField(max_length=100)
    ...

并在我的项目视图上使用它

from url_filter.integrations.drf import DjangoFilterBackend
from rest_framework import viewsets

class ProjectsViewSet(viewsets.ModelViewSet):
    """
    API endpoint that allows projects to be viewed or edited.
    """
    serializer_class = ProjectSerializer
    permission_classes = (IsAuthenticated, IsVendorStaffOrOwner)
    queryset = Project.with_status.prefetch_related('part').all()
    filter_backends = [DjangoFilterBackend]
    filter_fields = ('status',)

但是我遇到了一个错误：django.core.exceptions.FieldDoesNotExist: Project has no field named 'status'，所以我尝试解决它并编写了我自己的过滤器类：

from url_filter.filtersets import FilterSet
from django.db.models import Q

class ProjectsDynamicFilters(FilterSet):
    def filter(self):
        all_filters = Q()
        if 'status' in self.data:
            all_filters &= Q(status=self.data['status'])

        if len(all_filters):
            return self.queryset.filter(all_filters).distinct()
        else:
            return self.queryset

filter_field并在项目视图中使用它而不是我的财产： filter_class = ProjectsDynamicFilters.

此时，在项目对象管理器处接受很长很笨拙的注释查询，我无法更新或创建对象，因为我的项目序列化程序内部有一个状态字段，而创建或更新不能简单地返回一个实例。

更新功能特别是在更新相关部件对象后不重新计算状态字段。

项目序列化器

class ProjectSerializer(serializers.HyperlinkedModelSerializer):
    owner = serializers.PrimaryKeyRelatedField(queryset=User.objects.all(),
                                               default=serializers.CurrentUserDefault())
    created_at = serializers.DateTimeField(format="%d-%m-%Y %H:%M", read_only=True)
    updated_at = serializers.DateTimeField(format="%d-%m-%Y %H:%M", read_only=True)

    title = serializers.CharField()
    description = serializers.CharField()
    status = serializers.ChoiceField(choices=PartStatuses.choices(), default=PartStatuses.Draft.name)
    parts = NestedHyperlinkedRelatedField(many=True, source="part", parent_lookup_kwargs={'project_pk': 'project__pk'},
                                          view_name="parts-detail", read_only=True)
    ...

    def update(self, instance, validated_data):
        for data in validated_data:
            if data == "status":
                instance._status = validated_data.get(data)
            else:
                setattr(instance, data, validated_data.get(data))
        instance.save()
        return Project.with_status.get(pk=instance.id)

    def create(self, validated_data):
        validated_data.pop('status', None)
        project = Project.objects.create(**validated_data)
        return Project.with_status.get(pk=project.id)

回到问题

1. 有没有办法让项目with_status经理查询更加优雅高效？
2. 目前我只为我用来更新项目状态_status的函数保留模型属性。setter有没有办法在模型级别使用此功能而无需再次进行此计算？
3. 一旦我更改了部件状态，应该重新计算项目状态字段。我通过Project.with_status.get(pk=instance.id)在更新序列化程序方法结束时返回来解决它。有没有更明智的方法呢？

标签： pythonsqldjangopostgresql

Answer 1

您可以更改您的 ENUM：

class PartStatuses(Enum):
    Draft = 0, _("Saved but not published")
    PendingBID = 1, _("It's BIDing time!")
    Proposal = 2, _("All BIDs are set")
    PendingPO = 3, _("Waiting for vendor to approve PO")
    WorkInProgress = 4, _("Vendor has accepted a PO")
    OnItsWay = 5, _("The part is ready and now await to be delivered")
    Delivered = 6, _("Delivery process has ended")
    Disputed = 7, _("Open for Dispute")
    Closed = 8, _("Part has received")
    Paid = 9, _("Vendor received the payment")

这样，您可以将 Enum 值保留为标签并Min()在项目管理器带注释的查询上使用函数。