Django组对象按列的第一个字符 [英] Django group object by the first character of a column

问题描述

我试图按每个记录的 name 字段的字符对记录进行分组，并限制每个组中的项目，这是我想出的： / p>

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]

然后在for循环中运行此查询，并将 desired_letter 更改为我的所需的字母，是否有其他方法可以优化此解决方案并使它成为单个查询，而不是for循环？

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?

推荐答案

从另一个答案中的注释：

From the comment in the other answer:

我实际上是在寻找一种实现在Django Orm中按首字符分组

请按照以下3个步骤进行操作：

I would do it in following 3 steps:

1. 用 name 字段的首字母注释每个记录。为此，您可以使用 Substr 函数以及 Lower

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)))

接下来，使用第一个字母将所有记录分组，并获得id的计数。这可以通过使用用值进行注释：

# 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'))

接下来，您可以使用第一个字母订购此查询集：

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')

最后，您的查询集看起来像这样。请注意，我在注释时将第一个字符转换为小写。如果不需要，可以删除 Lower 函数：

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}

这篇关于Django组对象按列的第一个字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！