在Django中如何做SELECT COUNT（*）GROUP BY和ORDER BY？ [英] How to do SELECT COUNT(*) GROUP BY and ORDER BY in Django?

问题描述

我正在使用事务模型来跟踪系统中的所有事件

I'm using a transaction model to keep track all the events going through the system

class Transaction(models.Model):
    actor = models.ForeignKey(User, related_name="actor")
    acted = models.ForeignKey(User, related_name="acted", null=True, blank=True)
    action_id = models.IntegerField() 
    ......

在我的系统中的前5名演员？

how do I get the top 5 actors in my system?

在sql中基本上将是

SELECT actor, COUNT(*) as total 
FROM Transaction 
GROUP BY actor 
ORDER BY total DESC

推荐答案

根据文档，您应该使用：

According to the documentation, you should use:

from django.db.models import Count
Transaction.objects.all().values('actor').annotate(total=Count('actor')).order_by('total')

values（）：指定要用哪些列

values() : specifies which columns are going to be used to "group by"

Django文档：

当使用values（）要约束结果集中返回的
的列，评估注释的方法是
略有不同。根据
values（）子句结果返回注释结果，原始结果将分组为

"When a values() clause is used to constrain the columns that are returned in the result set, the method for evaluating annotations is slightly different. Instead of returning an annotated result for each result in the original QuerySet, the original results are grouped according to the unique combinations of the fields specified in the values() clause"

annotate（）：指定分组值的操作

annotate() : specifies an operation over the grouped values

Django docs： / p>

Django docs:

生成摘要值的第二种方法是为QuerySet中的每个对象生成一个独立的摘要，例如，如果
正在检索图书列表，您可能想知道每本书有多少作者
。每本书与作者有多对多关系
;我们要总结每个书籍的这种关系在QuerySet中预订
。

The second way to generate summary values is to generate an independent summary for each object in a QuerySet. For example, if you are retrieving a list of books, you may want to know how many authors contributed to each book. Each Book has a many-to-many relationship with the Author; we want to summarize this relationship for each book in the QuerySet.

可以使用annotate（）子句生成每个对象的摘要
当指定一个annotate（）子句时，QuerySet
中的每个对象都将用指定的值进行注释。

Per-object summaries can be generated using the annotate() clause. When an annotate() clause is specified, each object in the QuerySet will be annotated with the specified values.

order by子句是不言自明的。

The order by clause is self explanatory.

总结：你分组，生成作者的查询，添加注释（这将为返回的值添加一个额外的字段），最后，您通过此值来排序

To summarize: you group by, generating a queryset of authors, add the annotation (this will add an extra field to the returned values) and finally, you order them by this value

请参阅 https://docs.djangoproject.com/en/dev/topics/db/aggregation/ 了解更多信息

这篇关于在Django中如何做SELECT COUNT（*）GROUP BY和ORDER BY？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！