Annotating on a distinct Django Queryset is no longer using the distinct queryset-CodePudding

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.

CodePudding user response：

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.