django-orm case-insensitive group by

Question

I'm trying to annotate my data with their count in a case-insensitive manner. I found this similar question: django-orm case-insensitive order by and tried this:

from django.db.models.functions import Lower
Posts.objects.filter(published=True).values('author').annotate(Lower('author'))

However it returns:

AttributeError: 'Lower' object has no attribute 'split'

I also tried this one:

Posts.objects.filter(published=True).values('author').annotate(c=Count(Lower('author')))

It has no effect and the result is case sensitive.

Answer 1

Try annotating you data using Lower before Count:

Posts.objects.filter(published=True).annotate(lauthor=Lower('author')).values('lauthor').annotate(c=Count('id'))

Answer 2

I don't know how is your Post model, but I recommend using order_by

Posts.objects.filter(published=True).annotate(lauthor=Lower('author')).values('lauthor').annotate(c=Count('lauthor')).order_by('lauthor')