django Orm group by

在django 的orm里面想要group by一个模型的时候遇到一个问题，根据官方文档的调教，怎么都没办法。

1、orm model

class CvmAlarmDataByMetric(models.Model):
    """
    cvm metric类型的报警
    """
    objId = models.CharField('objId', max_length=100, help_text='objId')
    objName = models.CharField('objName', max_length=100, help_text='objName')
    alarmType = models.CharField('alarmType', max_length=20, help_text='告警类型')
    firstOccurTime = models.CharField('firstOccurTime', max_length=40, help_text='第一次告警时间')
    recoverTime = models.CharField('recoverTime', max_length=40, help_text='恢复时间')
    alarmStatus = models.CharField('alarmStatus', max_length=3, help_text='告警状态')
    metricName = models.ForeignKey(Metric, on_delete=models.CASCADE, help_text='监控项')
    durationTime = models.IntegerField('durationTime', help_text='持续时间')
    policyType = models.CharField('policyType', max_length=20, help_text='策略类型')
    policyName = models.CharField('policyName', max_length=20, help_text='策略名')
    unInstanceId = models.CharField('unInstanceId', max_length=20, help_text='实例id')
    sessionId = models.CharField('sessionId', max_length=50, help_text='会话id')
    createDate = models.DateField('创建日期', auto_now_add=True)

    def __str__(self):
        return self.objName

    class Meta:
        db_table = 'receive_CvmAlarmDataByMetric'
        ordering = ['id']
        verbose_name = 'cvm metric告警数据'
        verbose_name_plural = verbose_name

　

2、sql的实现效果就是这样

SELECT
	createDate,
	count(id) AS count
FROM
	receive_CvmAlarmDataByMetric
GROUP BY
	createDate;

3、在pycharm 的python console里面尝试，参考文档 https://docs.djangoproject.com/zh-hans/3.2/topics/db/aggregation/#order-of-annotate-and-values-clauses

from receive.models import CvmAlarmDataByMetric
from django.db.models import Count
CvmAlarmDataByMetric.objects.filter(createDate__gt="2023-01-22").values_list('createDate').annotate(counts=Count(id))
<QuerySet [(datetime.date(2023, 1, 26), 1), (datetime.date(2023, 1, 26), 1), (datetime.date(2023, 1, 24), 1), (datetime.date(2023, 1, 24), 1), (datetime.date(2023, 1, 27), 1), (datetime.date(2023, 1, 28), 1)]>

　　看见这样就傻逼了，为啥跟着文档一步步操作居然没有用？

 

4、参考了这个同学的文章https://www.jianshu.com/p/9ac4ca98b8e4

q =CvmAlarmDataByMetric.objects.filter(createDate__gt="2023-01-22").values_list('createDate').annotate(counts=Count(id))
print(q.query)
SELECT `receive_CvmAlarmDataByMetric`.`createDate`, COUNT(<built-in function id>) AS `counts` FROM `receive_CvmAlarmDataByMetric` WHERE `receive_CvmAlarmDataByMetric`.`createDate` > 2023-01-22 GROUP BY `receive_CvmAlarmDataByMetric`.`id`



q =CvmAlarmDataByMetric.objects.filter(createDate__gt="2023-01-22").values_list('createDate').annotate(counts=Count(id)).order_by()
print(q.query)
SELECT `receive_CvmAlarmDataByMetric`.`createDate`, COUNT(<built-in function id>) AS `counts` FROM `receive_CvmAlarmDataByMetric` WHERE `receive_CvmAlarmDataByMetric`.`createDate` > 2023-01-22 GROUP BY `receive_CvmAlarmDataByMetric`.`createDate` ORDER BY NULL

　　看看上面的两个结果，一个group by未生效，而一个生效了？参考了上面的文档后，然后我就想这么傻逼的操作官网不可能没有，然后我又去仔细的看了一下，如下链接：

https://docs.djangoproject.com/zh-hans/3.2/topics/db/aggregation/#interaction-with-order-by

原文粘贴：

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

　　

所以这个就可以解释了，在模型了默认按照id进行了排序，所以就出现了想不到的后果。