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

Question

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.

It's not clear to me why you have *two* `ManyToManyField`s in your `Image` model. — Willem Van Onsem, Nov 23 '21 at 19:41
A user could have annotated the image with some biomarkers, without having fully completed the annotation. If a user object is part of the `annotated` field, the image won't be offered to the user for annotation. Otherwise it will be and the `biomarkers` annotations pre-populated with the content of biomarkers field. For this question, I would be interested in completed annotations, i.e. `annotated` — scandav, Nov 23 '21 at 19:51
Have you seen `in` [field lookup](https://docs.djangoproject.com/en/dev/ref/models/querysets/#in)? Also see this answer https://stackoverflow.com/a/4508083/6143954. — NKSM, Nov 23 '21 at 19:56

Willem Van Onsem · Accepted Answer · 2021-11-23T20:16:05.333

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

Thanks. Is there a possibility to do everything in a query? I'm looking for some way to simply return the query in the form `[{"annotated": 1, "count": 70}, {"annotated": 0, "count": 85}]` — scandav, Nov 23 '21 at 19:56
@scandav: But I don't understand why there are multiple items here: you want to know how many `Image`s are annotated/not annotated by the user. That are two numbers... — Willem Van Onsem, Nov 23 '21 at 19:58
Because the problem is a bit bigger than that, as I had to simplify it for the sake of asking the question. The idea is that for each user i get these two numbers and create an API view for the frontend. But I might just be overdoing that. — scandav, Nov 23 '21 at 20:02

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

1 Answers1