Django group object by first column character

Question

I am trying to group records by type of name field of each record and restrict the elements in each group, this is what I 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 ran this query in a for loop and changed the desired_letter to my desired letter, is there any other way to optimize this solution and make it the only request instead of a for loop?

Answer 1

From a comment in another answer:

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

I would do this in the following three steps:

• Annotate each entry with the first letter of the name field. For this you can use Substr together with Lower

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

• Then group all the records with this first letter and get the number of identifiers. This can be done using annotate 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')) 

• Then you can order this set of queries with the first letter:

   qs = qs.order_by('fl_name') 

---

Combining all this 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 request will look something like this. Notice that I converted the first character to lowercase on annotation. If you do not need this, 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 with a letter and a counter:

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

Answer 2

first order, and then filtering unnecessarily and in vain. You must order only the data you need. Otherwise, you order all rows by name and then filter and cut what you need.

I would do:

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

and .all() was redundant.