I have a query and I am trying to annotate the count of each value for the field tail_tip. My original query filters on a related table so it is necessary to use distinct().
I'm not exactly sure how to describe but it appears when I annotate the distinct query the query is no longer distinct. Here are my queries I am playing with in my shell.
// Without distinct()
Ski.objects.filter(published=True, size__size__in=[178, 179, 180, 181, 182, 183, 184]).count()
// 318
skis = Ski.objects.filter(published=True, size__size__in=[178, 179, 180, 181, 182, 183, 184]).distinct()
skis.count()
// 297
skis.values('tail_tip').order_by('tail_tip').annotate(count=Count('tail_tip'))
// <QuerySet [{'tail_tip': 'flat', 'count': 99}, {'tail_tip': 'full_twin', 'count': 44}, {'tail_tip': 'partial_twin', 'count': 175}]>
// total count = 318
Given that skis is already distinct() I don't know why when I annotate it the total count then equals the non-distinct query.
Assuming related table named Size and Ski has one-to-many with it. This could be done this way:
ski_filtered = Ski.objects.filter(
published=True,
id__in=Size.objects.values("skis__id").filter( # Maybe somethig else than `skis` here, you didn't show its model, so I couldn't know
size__in=[178, 179, 180, 181, 182, 183, 184]
)
)
skis.values('tail_tip').distinct().order_by('tail_tip').annotate(count=Count('tail_tip'))
Why so: DISTINCT couldn't be used with annotate(). Implementation of annotate() in django is made with its own GROUP BY clause. So applying distinct() on already GROUP BY-ed query just couldn't work.
Try adding distinct=True:
skis.values('tail_tip').order_by('tail_tip').annotate(count=Count('tail_tip', distinct=True))