django-ORM之聚合函数和CASE|django-ORM之聚合函数和CASE WHEN的使用 django-ORM之聚合函数和CASEWHEN的

！！！for循环执行sql操作，那样是最愚蠢的做法！！！

业务场景有这样一个场景：两张表，文件表 和 文件内容表/query表，表结构如下，由于公司规定，没有办法使用外键，所以使用了逻辑关联（文件和query是一对多的关系）。

class File(models.Model): id = models.AutoField(primary_key=True, editable=False) file_name = models.CharField(max_length=255, db_index=True) create_time = models.DateTimeField(auto_now_add=True) status = models.IntegerField(default=1, db_index=True)# 0：已经标完，页面不可见1：未标完，页面可见 hash = models.CharField(max_length=255) in_mysql = models.IntegerField(default=0, db_index=True)# 0：默认是0，没有同步。后期同步后，变为1 count = models.IntegerField()# 文件中query的条数def __unicode__(self): return self.file_nameclass Meta: db_table = 'mark_file'class Query(models.Model): id = models.AutoField(primary_key=True, editable=False) query = models.CharField(max_length=255) status = models.IntegerField(default=0)# 0：未标注，1：白名单，2：黑名单，3：废弃，4：待扩充 update_time = models.DateTimeField(auto_now=True) file_id = models.IntegerField(db_index=True)# 根据此字段进行逻辑关联def __unicode__(self): return self.queryclass Meta: db_table = 'mark_query'def save(self, *args, **kwargs): self.update_time = datetime.datetime.now() super(Query, self).save(*args, **kwargs)

现在需要查询出id为 1、2、3、4、5、6的文件信息，并且计算出各个文件中对应的各个状态的query（status=0，status=3，status=4）的数量。以达到下面的效果。

django-ORM之聚合函数和CASE|django-ORM之聚合函数和CASE WHEN的使用

文章图片
file.png 简单实现，不考虑性能如果是不考虑性能问题，可能会使用，先查出id为1、2、3、4、5、6的文件，再for循环一个个count，例如：

for file_obj in file_list: file_id = file_obj.id # 未标注 no_recall_count = Query.objects.filter(Q(file_id=file_id) & Q(status=0)).count() # 已废弃 no_use_count = Query.objects.filter(Q(file_id=file_id) & Q(status=3)).count() # 待扩充 no_use_count = Query.objects.filter(Q(file_id=file_id) & Q(status=4)).count()

但是这样做是最笨，最不合理的做法。这才是6个文件，如果是100个文件，应该查询100?3=300次数据库，才能得出最终结果。对于一个接口来说，数据库操作次数越少越好，查这么多次简直是一个天文数字。
其实可以这样做，一次查询就可以得出统计结果。(django1.10.8)

from django.db.models import Q, F, Count, When, Casefiles_id = [1,2,3,4,5,6] counts = [] files_count = Query.objects.filter(file_id__in=files_id).values('file_id').annotate( no_recall_count=Count(Case(When(status=0, then=0))), no_use_count=Count(Case(When(status=3, then=0))), expand=Count(Case(When(status=4, then=0))))for files_count_obj in files_count: counts.append({ "file_id": files_count_obj.get('file_id'), "no_recall_count": files_count_obj.get('no_recall_count'), "no_use_count": files_count_obj.get('no_use_count'), "expand_count": files_count_obj.get('expand'), })

sql语句就是

SELECT `mark_query`.`file_id`, COUNT(CASE WHEN `mark_query`.`status` = 0 THEN 0 ELSE NULL END) AS `no_recall_count`, COUNT(CASE WHEN `mark_query`.`status` = 3 THEN 0 ELSE NULL END) AS `no_use_count`, COUNT(CASE WHEN `mark_query`.`status` = 4 THEN 0 ELSE NULL END) AS `expand` FROM `mark_query` WHERE `mark_query`.`file_id` IN (1, 2, 3, 4, 5, 6) GROUP BY `mark_query`.`file_id`;

【django-ORM之聚合函数和CASE|django-ORM之聚合函数和CASE WHEN的使用】如果是django2.0以上，还可以这样（没有验证是否可行）
https://www.bbsmax.com/A/KE5QKvLPzL/