Kim Majali

Django Aggregation Functions

Kim Majali wrote on 03/06/2022

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)

Total number of books.

>>> Book.objects.count()
2452

Total number of books with publisher=BaloneyPress

>>> Book.objects.filter(publisher__name='BaloneyPress').count()
73

Average price across all books.

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

Max price across all books.

>>> from django.db.models import Max
>>> Book.objects.all().aggregate(Max('price'))
{'price__max': Decimal('81.20')}

Difference between the highest priced book and the average price of all books.

>>> from django.db.models import FloatField
>>> Book.objects.aggregate(
...     price_diff=Max('price', output_field=FloatField()) - Avg('price'))
{'price_diff': 46.85}

Avg, Max, Min of books prices.

>>> from django.db.models import Avg, Max, Min
>>> Book.objects.aggregate(Avg('price'), Max('price'), Min('price'))
{'price__avg': 34.35, 'price__max': Decimal('81.20'), 'price__min': Decimal('12.99')}

How to use the dict example

job_ratings = JobRating.objects.filter(translator=translatr)
quality_rating = job_ratings.aggregate(Avg('quality_rating'))
translatr.quality_rating = quality_rating['quality_rating__avg']

All the following queries involve traversing the Book<->Publisher

foreign key relationship backwards.

>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
, , ...]>
>>> pubs[0].num_books
73

Each publisher, with a separate count of books with a rating above and below 5

>>> from django.db.models import Q
>>> above_5 = Count('book', filter=Q(book__rating__gt=5))
>>> below_5 = Count('book', filter=Q(book__rating__lte=5))
>>> pubs = Publisher.objects.annotate(below_5=below_5).annotate(above_5=above_5)
>>> pubs[0].above_5
23
>>> pubs[0].below_5
12

The top 5 publishers, in order by number of books.

>>> pubs = Publisher.objects.annotate(num_books=Count('book')).order_by('-num_books')[:5]
>>> pubs[0].num_books
1323

Pagination with Django Filter (Package)

Kim Majali wrote on 03/06/2022

Views.py

Django Filters – Package

Kim Majali wrote on 03/06/2022

1. Filters.py

from django.contrib.auth.models import User
import django_filters

class UserFilter(django_filters.FilterSet):
    class Meta:
        model = User
        fields = ['username', 'first_name', 'last_name', ]
 

Django Filters Package with Ordering

Kim Majali wrote on 03/06/2022

class CompanyLanguageFilter(django_filters.FilterSet):
    language__name = django_filters.CharFilter(label='Language', lookup_expr='icontains',)
    group__name = django_filters.CharFilter(label='Group', lookup_expr='icontains',)

    class Meta:
        model = CompanyLanguage
        fields = fields = {
        }

Ordering

    CHOICES = (
        ('ascending', 'Ascending'),
        ('desending', 'Descending')
    )
    ordering = django_filters.ChoiceFilter(label='Ordering', choices=CHOICES, method='filter_by_order')

    def filter_by_order(self, queryset, name, value):
        expression = 'language__name' if value == 'ascending' else '-language__name'
        return queryset.order_by(expression)

Resource:

Official example:

class UserFilter(FilterSet):
    account = CharFilter(field_name='username')
    status = NumberFilter(field_name='status')

    o = OrderingFilter(

Tuple-mapping retains order

        fields=(
            ('username', 'account'),
            ('first_name', 'first_name'),
            ('last_name', 'last_name'),
        ),

Labels do not need to retain order

        field_labels={
            'username': 'User account',
        }
    )

    class Meta:
        model = User
        fields = ['first_name', 'last_name']

>>> UserFilter().filters['o'].field.choices
[
    ('account', 'User account'),
    ('-account', 'User account (descending)'),
    ('first_name', 'First name'),
    ('-first_name', 'First name (descending)'),
    ('last_name', 'Last name'),
    ('-last_name', 'Last name (descending)'),
]

Django Filter Package – Exclude

Kim Majali wrote on 03/06/2022

Automatically all fields will be added but the excluded

class RequestFilter(django_filters.FilterSet):

    class Meta:
        model = Request
        exclude = ['id']