Django group object by the first character of a column

Question

I'm trying to group records by the character of name field of each record and limit the items in each group, here is what I have came up with:

desired_letters = ['a','b','c',..,'z']
some_variable = {}
for desired_letter in desired_letters:
    some_variable += User.objects.all().order_by("name").filter(name__startswith=desired_letter)[:10]

And I run this query in a for loop and change desired_letter to my desired letter, is there any other way to optimize this solution and make it a single query instead of a for loop?

Answer 1

From the comment in the other answer:

I was actually looking for a way to implement Group By First Character in django orm

I would do it in following 3 steps:

1. Annotate each record with first letter of the name field. For that you could use Substr function along with Lower

   from django.db.models.functions import Substr, Lower 
   qs = User.objects.annotate(fl_name=Lower(Substr('name', 1, 1)))
   

2. Next, group all the records with this first letter and get the count of ids. This can be done by using annotate with values:

   # since, we annotated each record we can use the first letter for grouping, and 
   # then get the count of ids for each group
   from django.db.models import Count
   qs = qs.values('fl_name').annotate(cnt_users=Count('id'))
   

3. Next, you can order this queryset with the first letter:

   qs = qs.order_by('fl_name')
   

---

Combining all these in one statement:

from django.db.models.functions import Substr, Lower
from django.db.models import Count 

qs = User.objects \
         .annotate(fl_name=Lower(Substr('name', 1, 1))) \
         .values('fl_name') \
         .annotate(cnt_users=Count('id')) \
         .order_by('fl_name')

At the end, your queryset would look something like this. Do note, that I converted first character to lower case while annotating. If you don't need that, you can remove the Lower function:

[{'fl_name': 'a', 'cnt_users': 12}, 
 {'fl_name': 'b', 'cnt_users': 4},
 ...
 ...
 {'fl_name': 'z', 'cnt_users': 3},]

If, you need a dictionary of letter and count:

fl_count = dict(qs.values('fl_name', 'cnt_users'))
# {'a': 12, 'b': 4, ........., 'z': 3}

Answer 2

first ordering and then filtering is overkill and in vain. you should only order the data you need. Otherwise you are ordering all rows by name and then filtering and slicing what you need.

I would do:

User.objects.filter(name__startswith=desired_letter).order_by("name")[:10]

and .all() was redundant.