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

by

2👍

✅

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.*
👤willeM_ Van Onsem

Leave a comment