如何計算Django多對多關系中包含特定值的專案

我有一個用于注釋的 django 應用程式，我BiomarkerAnnotations通過多對多關系將有關影像注釋的資訊保存到模型中。我會跟蹤哪些用戶完成了該annotated欄位的影像注釋。

from django.contrib.auth.models import User

class Biomarker(models.Model):   
    name = models.CharField(max_length=256)
    description = models.CharField(max_length=1024)

class Image(models.Model):
    number = models.IntegerField(default=0)
    absolute_path = models.CharField(max_length=2560)
    biomarkers = models.ManyToManyField(Biomarker, through='BiomarkerAnnotations', related_name="biomarker_annotations")
    annotated = models.ManyToManyField(User, related_name="annotated_by")

class BiomarkerAnnotations(models.Model):
    _image = models.ForeignKey(Image, on_delete=models.CASCADE)
    biomarker = models.ForeignKey(Biomarker, on_delete=models.CASCADE)
    user = models.ForeignKey(User, null=True,on_delete=models.SET_DEFAULT, default=1)     

我想創建一個視圖，為特定用戶（發送請求的用戶）回傳他已注釋的影像數量以及需要注釋的影像數量。到目前為止，我已經到了這一點，但它似乎不起作用：查詢回傳的總計數大于影像計數。

class AnnotStatsSerializer(serializers.ModelSerializer):
    annotations = serializers.IntegerField()
    count = serializers.IntegerField()

    class Meta:
        model = Image
        fields = ("count", "annotations")

class AnnotatedStatsViewSet(viewsets.ReadOnlyModelViewSet):
    queryset = Image.objects.all()
    serializer_class = AnnotStatsSerializer


    def get_queryset(self):
        queryset = self.queryset
        user_object = self.request.user

        project_id = self.request.query_params.get('project', None)
        if project_id is None: raise Http404

        queryset = queryset.annotate(annotations=Case(When(annotated__id__exact=user_object.id, then=Value(1)), default=Value(0), output_field=IntegerField())) \
                           .values('annotations').annotate(count=Count('annotations')).order_by('annotations') \
                           .values('count', 'annotations')

        return queryset

任何幫助表示贊賞。

uj5u.com熱心網友回復：

您可以與：

annotated_images = Image.objects.filter(
    biomarkerannotations__user=user_object
).count()
images_todo = Image.objects.exclude(
    biomarkerannotations__user=user_object
).count()

得到的數量annotated_images和images_todo。

或者如果您正在處理annotated多對多關系：

annotated_images = Image.objects.filter(
    annotated=user_object
).count()
images_todo = Image.objects.exclude(
    annotated=user_object
).count()

我們可以讓這與一組用戶一起作業：

from django.db.models import Count, OuterRef, Subquery, Value

User.objects.annotate(
    tagged=Count('annotated_by'),
    not_tagged=Subquery(
        Image.objects.exclude(
            annotated=OuterRef('pk'),
        ).values(foo=Value(None)).values(
            total=Count('pk')
        ).order_by(Value(None))
    )
)

這會生成一個如下所示的查詢：

SELECT auth_user.*,
       COUNT(app_name_image_annotated.image_id) AS tagged
       (
           SELECT COUNT(V0.id) AS total
           FROM app_name_image V0
           WHERE NOT EXISTS
               (
                   SELECT (1) AS a
                   FROM app_name_image_annotated U1
                   WHERE U1.user_id = auth_user.id AND U1.image_id = V0.id
                   LIMIT 1
               )
       ) AS not_tagged FROM auth_user
LEFT OUTER JOIN app_name_image_annotated ON (auth_user.id = app_name_image_annotated.user_id)
GROUP BY auth_user.*

標籤：Python 姜戈

上一篇：如何在應用程式中使用帶有Django的NPM模塊？

下一篇：如何處理高回應時間