【Django】聚合查询——连接和聚合

还是以下面的模型为例进行讨论，给出示例代码和示例结果

from django.db import models


class Author(models.Model):
    name = models.CharField(max_length=100)
    age = models.IntegerField()


class Publisher(models.Model):
    name = models.CharField(max_length=300)


class Book(models.Model):
    name = models.CharField(max_length=300)
    pages = models.IntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)
    rating = models.FloatField()
    authors = models.ManyToManyField(Author)
    publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)
    pubdate = models.DateField()


class Store(models.Model):
    name = models.CharField(max_length=300)
    books = models.ManyToManyField(Book)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

正向查询

如果需要聚合查询某个模型相关联的其他模型的字段，我们要使用__（双下划线）。
找出每家商店提供的书籍价格范围：

>>> from django.db.models import Max, Min
>>> Store.objects.annotate(min_price=Min("books__price"), max_price=Max("books__price"))
<QuerySet [<Store: Store object (1)>, <Store: Store object (2)>, <Store: Store object (3)>, <Store: Store object (4)>]>
1
2
3

这个规则也适用于aggregate()，找出所有商店在售书籍中的最低价格和最高价格：

>>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price"))
{'min_price': Decimal('5.00'), 'max_price': Decimal('30.00')}
1
2

还可以进行链式连接，找出在售书籍中最年轻作者的年龄：

>>> Store.objects.aggregate(youngest_age=Min("books__authors__age"))
{'youngest_age': 10}
1
2

反向查询

查询所有出版社，计算各自的书籍库存数量：

>>> from django.db.models import Avg, Count, Min, Sum
>>> Publisher.objects.annotate(Count("book"))
<QuerySet [<Publisher: Publisher object (1)>, <Publisher: Publisher object (2)>, <Publisher: Publisher object (3)>, <Publisher: Publisher object (4)>]>
>>> publishers = Publisher.objects.annotate(Count("book"))
>>> publishers[0].book__count
1
1
2
3
4
5
6

可以观察到查询结果里的每一个 Publisher 会有多余的属性—— book__count

要找到所有出版商管理的书籍中最老的一本书的出版日期：

>>> Publisher.objects.aggregate(oldest_pubdate=Min("book__pubdate"))
{'oldest_pubdate': datetime.date(2024, 3, 1)}
1
2

查询所有作者，并得到他所著书籍的总页数：

>>> Author.objects.annotate(total_pages=Sum("book__pages"))
<QuerySet [<Author: Author object (1)>, <Author: Author object (2)>, <Author: Author object (3)>, <Author: Author object (4)>]>
1
2

要找到所有作者所著书籍的平均评分：

>>> Author.objects.aggregate(average_rating=Avg("book__rating"))
{'average_rating': 9.0}
1
2