Difference between Django annotates and aggregate methods?

Question

Django QuerySet has two methods: annotate and aggregate . The documentation states that:

Unlike aggregate (), annotate () is not a terminal clause. The result of the annotate () clause is a QuerySet.

Is there any other difference between the two? If not, why does aggregate exist?

Answer 1

I would like to focus on sample queries, rather than quotes from the documentation. Aggregate calculates values ​​to query everything . Annotate calculates the totals for each item in the query.

Aggregation

 >>> Book.objects.aggregate(average_price=Avg('price')) {'average_price': 34.35} 

Returns a dictionary containing the average price of all books in the query.

annotation

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

q is a collection of book requests, but each book has been annotated by the number of authors.

Answer 2

This is the main difference, but aggregates also work on a larger scale than annotations. Annotations are inherently associated with individual elements in a query set. If you run the Count annotation for a many-to-many field, you will get a separate counter for each member of the query set (as an added attribute). However, if you were to do the same with aggregation, he would try to calculate each relationship for each member of the query set, even duplicates, and return it as only one value.

Answer 3

Let's look at an example:

 class Country(models.Model): name = models.CharField(max_length=30) class City(models.Model): name = models.CharField(max_length=30) country = models.ForeignKey(Country) population = models.PositiveIntegerField() 

And the original data stored in the database:

cities

 id name country_id population 1 Brazil 28 36,923,000 2 Turkey 13 34,000,000 3 Italy 19 30,000,000 4 Seoul 21 25,514,000 

countries

 id name 1 Brazil 2 Turkey 3 Italy 4 Seoul 

If we wanted to know the total number of inhabitants in all cities, we could use the general query:

 from django.db.models import Sum City.objects.aggregate(Sum('population')) {'population__sum': 126437000} 

Or the average population in cities:

 from django.db.models import Avg City.objects.aggregate(Avg('population')) {'population__avg': 31609250} 

What if we now want to see the total population, rather than aggregate by country? Not the entire data set. In this case, we can no longer use the aggregate, we will use the annotation instead.

 City.objects.values('country__name').annotate(Sum('population')) [ {'country__name': u'Brazil', 'population__sum': 36,923,000}, {'country__name': u'Turkey', 'population__sum': 34,000,000}, {'country__name': u'Italy', 'population__sum': 30,000,000}, {'country__name': u'Seoul', 'population__sum': 25,514,000}, ] 

Renouncement

for more details: Django queryset (aggregate and annotate)

Hope you found this answer helpful!

Answer 4

Aggregate Aggregate generates totals (totals) throughout the QuerySet. The unit is working on a rowset to get a single value from a rowset (for example, the sum of all prices in a rowset). The aggregate is used for the entire QuerySet and generates the total (total) values ​​throughout the QuerySet.

In the model:

 `class Books(models.Model): name = models.CharField(max_length=100) pages = models.IntegerField() price = models.DecimalField(max_digits=5, decimal_places=3) 

In the shell:

 >>> Books.objects.all().aggregate(Avg('price')) # Above code will give the Average of the price Column >>> {'price__avg': 34.35} ` 

Annotate Annotation generates an independent summary for each object in the QuerySet (we can say that it iterates over each object in the QuerySet and applies the operation)

In the model:

 class Video(models.Model): name = models.CharField(max_length=52, verbose_name='Name') video = models.FileField(upload_to=document_path, verbose_name='Upload video') created_by = models.ForeignKey(User, verbose_name='Created by', related_name="create_%(class)s") user_likes = models.ManyToManyField(UserProfile, null=True, blank=True, help_text='User can like once', verbose_name='Like by') 

In view

videos = Video.objects.values ​​('id', 'name', 'video'). annotate (Count ('user_likes', distinct = True)

`** In view of what he will consider liked for each video, **