本文介绍了Django组对象按列的第一个字符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图按每个记录的 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:


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

  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组对象按列的第一个字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

    08-14 18:38
    查看更多