Django3中分组查询的一个坑

最近在学习django的分组查询，发现使用通常的values加annotate方法，获取不到我想要的结果，后来通过查询官方文档得到答案

一、问题描述

1.1 模型

from django.db import models

# Create your models here.


class Goods(models.Model):
    g_name = models.CharField(max_length=32, help_text='商品名称')
    g_price = models.FloatField(help_text='商品价格')
    g_old_price = models.FloatField(help_text='商品原价')
    g_public = models.DateField(help_text='出厂日期')
    g_number = models.IntegerField(help_text='数量')
    g_store = models.CharField(max_length=32, help_text='商品产地')
    save_time = models.IntegerField(help_text='保质期')
    save_unit = models.CharField(max_length=32, help_text='保存单元')
    del_flag = models.CharField(max_length=32, help_text='逻辑删除')

    def __str__(self):
        return self.g_name

    class Meta:
        ordering = ['-id']
        db_table = 't_goods'

查询每家店铺的生鲜种类

Goods.objects.values("g_store").annotate(total=Count("id"))

结果

<QuerySet [{'g_store': '老边生鲜', 'total': 1}, {'g_store': '老边生鲜', 'total': 1}, {'g_store': '华润万家', 'total': 1}, {'g_store': '老边生鲜',
 'total': 1}, {'g_store': '沃尔玛超市', 'total': 1}, {'g_store': '永辉超市', 'total': 1}, {'g_store': '老边生鲜', 'total': 1}, {'g_store': '沃尔
玛超市', 'total': 1}, {'g_store': '老边生鲜', 'total': 1}, {'g_store': '老边生鲜', 'total': 1}, {'g_store': '老边生鲜', 'total': 1}, {'g_store':
'老边生鲜', 'total': 1}, {'g_store': '华润万家', 'total': 1}, {'g_store': '沃尔玛超市', 'total': 1}, {'g_store': '华润万家', 'total': 1}, {'g_sto
re': '沃尔玛超市', 'total': 1}, {'g_store': '老边生鲜', 'total': 1}, {'g_store': '华润万家', 'total': 1}, {'g_store': '老边生鲜', 'total': 1}, {'
g_store': '老边生鲜', 'total': 1}, '...(remaining elements truncated)...']>

二、解决问题

2.1 解决方案

Goods.objects.order_by("g_store").values("g_store").annotate(total=Count("id"))

结果

<QuerySet [{'g_store': '华润万家', 'total': 2560}, {'g_store': '永辉超市', 'total': 2471}, {'g_store': '沃尔玛超市', 'total': 2463}, {'g_store':
'老边生鲜', 'total': 2508}]>

 

三、官方文档

 有问题实在解决不了，最好不是去看官方文档吧！这段话的意思就是说：如果前面的queryset有用过order_by()，那么这个字段还会影响后面的分组查询操作。你要想得到正确结果，后面要加上一个order_by()。这个括号里就什么也不要写。这样就可以清除前面order_by的影响。

Interaction with order_by()

Fields that are mentioned in the order_by() part of a queryset are used when selecting the output data, even if they are not otherwise specified in the values() call. These extra fields are used to group “like” results together and they can make otherwise identical result rows appear to be separate. This shows up, particularly, when counting things.

By way of example, suppose you have a model like this:

• from django.db import models
  
  class Item(models.Model):
      name = models.CharField(max_length=10)
      data = models.IntegerField()

  If you want to count how many times each distinct data value appears in an ordered queryset, you might try this:

• items = Item.objects.order_by('name')
  # Warning: not quite correct!
  items.values('data').annotate(Count('id'))

  …which will group the Item objects by their common data values and then count the number of id values in each group. Except that it won’t quite work. The ordering by name will also play a part in the grouping, so this query will group by distinct (data, name) pairs, which isn’t what you want. Instead, you should construct this queryset:

• items.values('data').annotate(Count('id')).order_by()

  …clearing any ordering in the query. You could also order by, say, data without any harmful effects, since that is already playing a role in the query.

  This behavior is the same as that noted in the queryset documentation for distinct() and the general rule is the same: normally you won’t want extra columns playing a part in the result, so clear out the ordering, or at least make sure it’s restricted only to those fields you also select in a values() call.

• Note

  You might reasonably ask why Django doesn’t remove the extraneous columns for you. The main reason is consistency with distinct() and other places: Django never removes ordering constraints that you have specified (and we can’t change those other methods’ behavior, as that would violate our API stability policy).

四、终极答案

Goods.objects.order_by().values("g_store").annotate(total=Count("id"))

结果

<QuerySet [{'g_store': '老边生鲜', 'total': 2508}, {'g_store': '华润万家', 'total': 2560}, {'g_store': '沃尔玛超市', 'total': 2463}, {'g_store':
'永辉超市', 'total': 2471}]>