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

Question

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.

score 1 · Accepted Answer · answered Dec 22 '21 at 07:08

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.

This worked and thanks for the explanation! – Dan S. Dec 22 '21 at 19:25 — Dan S., Dec 22 '21 at 19:25

score 1 · Answer 2 · answered Dec 25 '21 at 13:10

1

Try adding distinct=True:

skis.values('tail_tip').order_by('tail_tip').annotate(count=Count('tail_tip', distinct=True))

answered Dec 25 '21 at 13:10

Uri

2,992
8
43
86

adding distinct results in ``` ``` – Dan S. Dec 29 '21 at 20:56

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

2 Answers2