Aggregate and sort functions

This is the seventh day of my participation in the August More text Challenge. For details, see:August is more challenging

In this guide (and resources), we will refer to the following models,

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)
Copy the code

1. Aggregate functions

aggregate()

The aggregate function is called with the aggregate() filter. Aggregate functions such as Avg average, Count number, Max Maximum, Min minimum, and Sum are defined in Django.db. models.

Here are some common aggregated queries to perform based on the above model:

>>> Book.objects.count() >>> Book.objects.filter(publisher__name='BaloneyPress').count() >>> Book.objects.all().aggregate(Avg('price')) >>> Book.objects.all().aggregate(Max('price')) >>> Book.objects.aggregate( . price_diff=Max('price', output_field=FloatField()) - Avg('price')) >>> pubs = Publisher.objects.annotate(num_books=Count('book')) >>> above_5 = Count('book', filter=Q(book__rating__gt=5)) >>> below_5 = Count('book', filter=Q(book__rating__lte=5))Copy the code

Note that the return value of the count function is a number.

inQuerySetUpgeneration polymerization

Django provides two ways to generate aggregation. The first is to generate a summary value from the entire QuerySet. Let’s say you want to calculate the average price of all the books on sale. Django’s query syntax provides a way to describe an entire collection of books:

>>> Book.objects.all()
Copy the code

• This can be done by usingQuerySetAfter addingaggregate()ClauseQuerySetObject.

>>> from django.db.models import Avg >>> book.objects.all ().objects.all().aggregate(Avg('price')) {'price__avg': 34.35}Copy the code

• In this caseall()Is redundant, so it can be reduced to something like this:

> > > Book. Objects. Aggregate (Avg (" price ")) {' price__avg: 34.35}Copy the code

The argument passed to aggregate() describes the aggregate value we want to calculate. In this example, the average value of the price field on the Book model is calculated. A list of available aggregate functions can be found in the QuerySet Reference.

annotate()

Generate an aggregate for each entry in the QuerySet. Like aggregate(), the name of the annotation is automatically generated based on the aggregate function and the name of the field being aggregated. When specifying an annotation, you can override the default name by providing an alias:

>>> q = Book.objects.annotate(num_authors=Count('authors'))
>>> q[0].num_authors
2
>>> q[1].num_authors
1
Copy the code

Unlike aggregate(), annotate() is nota terminal clause. The output of the Annotate () clause is QuerySet; This QuerySet is modified by other QuerySet operations, including filter(), order_by(), and even additional calls to Annotate () ‘.

2. The sorting

Use order_BY to sort the results

order_by()

Order_by can be used as a basic sort. When you define an order_by() clause, the aggregation you provide can refer to any aliases that are defined as part of the annotate() clause in the query.

For example, to sort the QuerySet of books by the number of authors of the books, you could use the following query:

>>> Book.objects.annotate(num_authors=Count('authors')).order_by('num_authors')
Copy the code

With the default sort ororder_by()interaction

Deprecated since version 2.2: Starting with Django 3.1, the ordering of the model meta-.annotate is not used in GROUP BY queries such as.annotate().values(). Starting with Django 2.2, these queries issue a deprecation warning indicating that an explicit order_by() warning is to be added to the query set for silent warning.