本文翻译自: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
标记之前,请先按石斑鱼的键对列表进行排序。