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