如何在Django中以GROUP BY查询？

本文翻译自：How to query as GROUP BY in django?

I query a model: 我查询一个模型：

Members.objects.all()

And it returns: 它返回：

Eric, Salesman, X-Shop
Freddie, Manager, X2-Shop
Teddy, Salesman, X2-Shop
Sean, Manager, X2-Shop

What I want is to know the best Django way to fire a group_by query to my database, like: 我想要的是知道向我的数据库触发group_by查询的最佳Django方法，例如：

Members.objects.all().group_by('designation')

Which doesn't work, of course. 当然，这不起作用。 I know we can do some tricks on django/db/models/query.py , but I am just curious to know how to do it without patching. 我知道我们可以在django/db/models/query.py上做一些技巧，但是我很好奇知道如何在不打补丁的情况下做到这一点。

---

#1楼

参考：https://stackoom.com/question/2dm3/如何在Django中以GROUP-BY查询

---

#2楼

如果我没有记错的话，可以使用what -query-set .group_by = [' field ']

---

#3楼

Django does not support free group by queries . Django不支持免费的按组分组查询 。 I learned it in the very bad way. 我以非常糟糕的方式学到了它。 ORM is not designed to support stuff like what you want to do, without using custom SQL. ORM并非设计为不使用自定义SQL来支持您想做的事情。 You are limited to: 您仅限于：

• RAW sql (ie MyModel.objects.raw()) 原始sql（即MyModel.objects.raw（））
• cr.execute sentences (and a hand-made parsing of the result). cr.execute句子（并对结果进行手工分析）。
• .annotate() (the group by sentences are performed in the child model for .annotate(), in examples like aggregating lines_count=Count('lines'))). .annotate() （按语句在.annotate（）的子模型中执行句子分组，例如汇总lines_count = Count（'lines'）之类的示例）。

Over a queryset qs you can call qs.query.group_by = ['field1', 'field2', ...] but it is risky if you don't know what query are you editing and have no guarantee that it will work and not break internals of the QuerySet object. 在查询集qs您可以调用qs.query.group_by = ['field1', 'field2', ...]但是如果您不知道要编辑的查询并且不能保证它能正常工作，则存在风险。不会破坏QuerySet对象的内部。 Besides, it is an internal (undocumented) API you should not access directly without risking the code not being anymore compatible with future Django versions. 此外，它是一个内部（未记录）的API，您不应直接访问它，而不必担心代码不再与将来的Django版本兼容。

---

#4楼

There is module that allows you to group Django models and still work with a QuerySet in the result: https://github.com/kako-nawao/django-group-by 有一个模块可以让您对Django模型进行分组，并且仍然可以在结果中使用QuerySet： https : //github.com/kako-nawao/django-group-by

For example: 例如：

from django_group_by import GroupByMixin

class BookQuerySet(QuerySet, GroupByMixin):
    pass

class Book(Model):
    title = TextField(...)
    author = ForeignKey(User, ...)
    shop = ForeignKey(Shop, ...)
    price = DecimalField(...)

---

class GroupedBookListView(PaginationMixin, ListView):
    template_name = 'book/books.html'
    model = Book
    paginate_by = 100

    def get_queryset(self):
        return Book.objects.group_by('title', 'author').annotate(
            shop_count=Count('shop'), price_avg=Avg('price')).order_by(
            'name', 'author').distinct()

    def get_context_data(self, **kwargs):
        return super().get_context_data(total_count=self.get_queryset().count(), **kwargs)

'book/books.html' 'book / books.html'

<ul>
{% for book in object_list %}
    <li>
        <h2>{{ book.title }}</td>
        <p>{{ book.author.last_name }}, {{ book.author.first_name }}</p>
        <p>{{ book.shop_count }}</p>
        <p>{{ book.price_avg }}</p>
    </li>
{% endfor %}
</ul>

The difference to the annotate / aggregate basic Django queries is the use of the attributes of a related field, eg book.author.last_name . 与annotate / aggregate基本Django查询的区别在于使用了相关字段的属性，例如book.author.last_name 。

If you need the PKs of the instances that have been grouped together, add the following annotation: 如果需要已分组在一起的实例的PK，请添加以下注释：

.annotate(pks=ArrayAgg('id'))

NOTE: ArrayAgg is a Postgres specific function, available from Django 1.9 onwards: https://docs.djangoproject.com/en/1.10/ref/contrib/postgres/aggregates/#arrayagg 注意： ArrayAgg是Postgres特定的功能，可从Django 1.9开始使用： https : ArrayAgg

---

#5楼

The document says that you can use values to group the queryset . 该文档说，您可以使用值对queryset进行分组。

class Travel(models.Model):
    interest = models.ForeignKey(Interest)
    user = models.ForeignKey(User)
    time = models.DateTimeField(auto_now_add=True)

# Find the travel and group by the interest:

>>> Travel.objects.values('interest').annotate(Count('user'))
<QuerySet [{'interest': 5, 'user__count': 2}, {'interest': 6, 'user__count': 1}]>
# the interest(id=5) had been visited for 2 times, 
# and the interest(id=6) had only been visited for 1 time.

>>> Travel.objects.values('interest').annotate(Count('user', distinct=True)) 
<QuerySet [{'interest': 5, 'user__count': 1}, {'interest': 6, 'user__count': 1}]>
# the interest(id=5) had been visited by only one person (but this person had 
#  visited the interest for 2 times

You can find all the books and group them by name using this code: 您可以找到所有书籍，并使用以下代码按名称分组：

Book.objects.values('name').annotate(Count('id')).order_by() # ensure you add the order_by()

You can watch some cheet sheet here . 你可以在这里看一些指南。

---

#6楼

You can also use the regroup template tag to group by attributes. 您还可以使用regroup分组模板标记对属性进行分组。 From the docs: 从文档：

cities = [
    {'name': 'Mumbai', 'population': '19,000,000', 'country': 'India'},
    {'name': 'Calcutta', 'population': '15,000,000', 'country': 'India'},
    {'name': 'New York', 'population': '20,000,000', 'country': 'USA'},
    {'name': 'Chicago', 'population': '7,000,000', 'country': 'USA'},
    {'name': 'Tokyo', 'population': '33,000,000', 'country': 'Japan'},
]

...

{% regroup cities by country as country_list %}

<ul>
    {% for country in country_list %}
        <li>{{ country.grouper }}
            <ul>
            {% for city in country.list %}
                <li>{{ city.name }}: {{ city.population }}</li>
            {% endfor %}
            </ul>
        </li>
    {% endfor %}
</ul>

Looks like this: 看起来像这样：

• India 印度
  • Mumbai: 19,000,000 孟买：19,000,000
  • Calcutta: 15,000,000 加尔各答：15,000,000
• USA 美国
  • New York: 20,000,000 纽约：20,000,000
  • Chicago: 7,000,000 芝加哥：7,000,000
• Japan 日本
  • Tokyo: 33,000,000 东京：33,000,000

It also works on QuerySet s I believe. 我相信它也可以在QuerySet 。

source: https://docs.djangoproject.com/en/2.1/ref/templates/builtins/#regroup 来源： https : //docs.djangoproject.com/en/2.1/ref/templates/builtins/#regroup

edit: note the regroup tag does not work as you would expect it to if your list of dictionaries is not key-ordered. 编辑：请注意，如果字典列表不是按键排序的，则regroup标签将无法正常工作。 It works iteratively. 它迭代地工作。 So order your list by the key of the grouper before passing it to the regroup tag. 因此，在将其传递给regroup标记之前，请先按石斑鱼的键对列表进行排序。