In this article i want to show you How to Use Aggregation in Django , so the meaning of aggregation is “the collection of related items of content so that they can be displayed or linked to”. there are different situations that you will need to use Aggregation in Django, for example:
- for finding “maximum”, “minimum” value of column in database table in django models.
- for finding “count” of records in database table based on a column.
- for finding “average” value of a group of similar objects.
- also for finding sum of values in a column.
In most of the cases we use aggregation on columns of data type “integer”, “float”, “date”, “datetime” etc.
essentially, aggregations are nothing but a way to perform an operation on group of rows. In databases, they are represented by operators as sum, avg etc. to do these operations Django added two new methods to querysets.
these two methods are aggregate and annotate. also we can tell that in sql terms, aggregate is a operation(SUM, AVG, MIN, MAX), without a group by, while annotate is a operation with a group by on rowset_table.id. (Unless explicitly overriden).
Also you can read more django articles
1: Django Pagination Complete Example
2: Django Sending Email to Gmail Account
3: Build News Application in Django
So now first of all you need to create a new project in django using this command.
|
1 |
django-admin startproject MyProject |
After project creation, we need to create a new App in django. make sure that you have changed your directory to the created project, in my case it is MyProject.
|
1 |
python manage.py startapp MyApp |
So now open your settings.py in your created App, and add your App in there. in my case it is MyApp.
|
1 2 3 4 5 6 7 8 9 |
INSTALLED_APPS = [ 'django.contrib.admin', 'django.contrib.auth', 'django.contrib.contenttypes', 'django.contrib.sessions', 'django.contrib.messages', 'django.contrib.staticfiles', 'MyApp' #newly added ] |
OK after this you need to migrate your project with this command.
|
1 |
python manage.py migrate |
Also you need to create a super user for your django project like this.
|
1 |
python manage.py createsuperuser |
And now open your models.py and add these models like this.
|
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 26 27 |
from django.db import models # Create your models here. class Author(models.Model): name = models.CharField(max_length=50) def __str__(self): return self.name class Publisher(models.Model): name = models.CharField(max_length=50) def __str__(self): return self.name class Book(models.Model): name = models.CharField(max_length=300) price = models.DecimalField(max_digits=10, decimal_places=2) authors = models.ManyToManyField(Author) publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE) def __str__(self): return self.name |
basically we have created three models. now you need to do migrations.
|
1 |
python manage.py makemigrations |
|
1 |
python manage.py migrate |
also you need to register these models in admin.py file.
|
1 2 3 4 5 6 7 8 |
from django.contrib import admin from .models import Book, Author, Publisher # Register your models here. admin.site.register(Book) admin.site.register(Author) admin.site.register(Publisher) |
and after that you need to open your admin panel and add some items to your database. after that we are going to start our aggregation commands.
Now you need to open django shell, because we are using django shell for our aggregation commands.
|
1 |
python manage.py shell |
So now you can get the number of totals books from your model like this.
|
1 2 3 4 |
In [1]: from MyApp.models import Book In [2]: Book.objects.count() Out[2]: 8 |
Total number of books according to publication.
|
1 2 |
In [5]: Book.objects.filter(publisher__name = 'Second') Out[5]: <QuerySet [<Book: Python New Book>, <Book: Kotlin Book>]> |
Finding average price across all books.
|
1 2 3 4 |
In [6]: from django.db.models import Avg In [7]: Book.objects.all().aggregate(Avg('price')) Out[7]: {'price__avg': Decimal('121.25')} |
Max price across all books.
|
1 2 3 4 |
In [8]: from django.db.models import Max In [9]: Book.objects.all().aggregate(Max('price')) Out[9]: {'price__max': Decimal('185')} |
Finding min price for all books.
|
1 2 3 4 |
In [10]: from django.db.models import Min In [11]: Book.objects.all().aggregate(Min('price')) Out[11]: {'price__min': Decimal('50')} |
Sum of prices for all books.
|
1 2 3 4 |
In [12]: from django.db.models import Sum In [13]: Book.objects.all().aggregate(Min('price')) Out[13]: {'price__min': Decimal('50')} |
Also you can do multiple aggregation in a queryset.
|
1 2 3 4 5 |
In [5]: Book.objects.aggregate(Avg('price'), Max('price'), Min('price')) Out[5]: {'price__avg': Decimal('121.25'), 'price__max': Decimal('185'), 'price__min': Decimal('50')} |
Listing all the publisher for the book using annotate.
|
1 2 3 4 5 6 7 8 |
In [9]: from MyApp.models import Publisher In [10]: from django.db.models import Count In [11]: pubs = Publisher.objects.annotate(num_books=Count('book')) In [12]: pubs[0].num_books Out[12]: 3 |
Also you can watch the complete video for this article
Subscribe and Get Free Video Courses & Articles in your Email
Comments are closed.