问题描述
我试图按每个记录的 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:
请按照以下3个步骤进行操作:
I would do it in following 3 steps:
-
用
name
字段的首字母注释每个记录。为此,您可以使用函数以及
Annotate each record with first letter of the
name
field. For that you could useSubstr
function along withLower
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组对象按列的第一个字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!