还是以下面的模型为例进行讨论,给出示例代码和示例结果
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)
正向查询
如果需要聚合查询某个模型相关联的其他模型的字段,我们要使用__(双下划线)
。
找出每家商店提供的书籍价格范围:
>>> 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)>]>
这个规则也适用于aggregate()
,找出所有商店在售书籍中的最低价格和最高价格:
>>> Store.objects.aggregate(min_price=Min("books__price"), max_price=Max("books__price"))
{'min_price': Decimal('5.00'), 'max_price': Decimal('30.00')}
还可以进行链式连接,找出在售书籍中最年轻作者的年龄:
>>> Store.objects.aggregate(youngest_age=Min("books__authors__age"))
{'youngest_age': 10}
反向查询
查询所有出版社,计算各自的书籍库存数量:
>>> 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
可以观察到查询结果里的每一个 Publisher 会有多余的属性—— book__count
要找到所有出版商管理的书籍中最老的一本书的出版日期:
>>> Publisher.objects.aggregate(oldest_pubdate=Min("book__pubdate"))
{'oldest_pubdate': datetime.date(2024, 3, 1)}
查询所有作者,并得到他所著书籍的总页数:文章来源:https://www.toymoban.com/news/detail-838227.html
>>> 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)>]>
要找到所有作者所著书籍的平均评分:文章来源地址https://www.toymoban.com/news/detail-838227.html
>>> Author.objects.aggregate(average_rating=Avg("book__rating"))
{'average_rating': 9.0}
到了这里,关于【Django】聚合查询——连接和聚合的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!