How to count items containing specific value in Django Many to Many relationship-CodePudding

I have a django application used for annotation where i save information regarding image annotations into the model BiomarkerAnnotations through a many-to-many relationship. I keep track of which users have completed the image annotation with the annotated field.

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)

I would like to create a view that returns, for a specific user (the one sending the request), how many images he has annotated and how many are left to annotate. So far I've reached this point, but it doesn't seem to work: the total count returned by the query is bigger than the images count.

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

Any help appreciated.

CodePudding user response：

You can work with:

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

to obtain the number of annotated_images and images_todo.

or if you are working with the annotated many-to-many relation:

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

We can let this work with a set of users with:

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))
    )
)

This produces a query that looks like:

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.*