Hi everyone,
I want to do two counts of objects from a related table. On count should be filtered by a value that is a computed annotation. If I don’t try to make it to one query, this works :
annotated_users = User.objects.annotate(annotation=(…))
for s in Site.objects.all():
print(site)
print(annotated_qs.filter(profile__sites=s.pk).distinct().count())
print(annotated_qs.filter(annotation__gt=3, profile__sites=s.pk).distinct().count())
But I would like to do it in one request, so I try to do the count from the Site manager
This comes from this stackoverflow post but it gives results that are too high, maybe because sites is a many to many relationship.
Site.objects.annotate(
count=Subquery(
annotated_users
.filter(profile__sites=OuterRef(‘pk’))
.values(‘profile__sites’)
.annotate(count=Count(‘pk’))
.values(‘count’)
)
)
I tries prefetching the many to many relationship specifying the annotated queryset in Prefetch but the annotation is lost and I can’t use it in the filter
(Site.objects
.prefetch_related(Prefetch("userprofile_set", queryset=annotated_users))
.annotate(
nb_cerema=Count("cerema_profiles"),
nb_cerema_active=Count("cerema_profiles", filter=Q(cerema_profiles__nb_login__gt=3))
))
I tried doing the annotation in the Site request using a lot of __ to follow the relationship annotations do not seem to follow fields relations
I spent quite a lot of time trying stuff. One of the errors that I get seems to be because WHERE cannot be used with aggregation results, but then I should use HAVING but this is not in my hands while using an ORM.
Do you see something else I could try ? I have not written down every attempt because there was a lot of them ><