7.13. ORM Functions¶
https://docs.djangoproject.com/en/stable/ref/models/database-functions/
.aggregate().alias().annotate().count().distinct().using()
>>>
... from django.db.models import Value
... from shop.models import Customer
...
...
... Customer.objects \
... .all() \
... .annotate(fullname=Concat('firstname', Value(' '), 'lastname')) \
... .values('fullname')
<QuerySet [{'fullname': 'Melissa Lewis'}, {'fullname': 'Rick Martinez'}, {'fullname': 'Alex Vogel'}, {'fullname': 'Beth Johnssen'}]>
>>> from django.db.models import Avg, Sum, Min, Max, Count
>>> Customer.objects.count()
7
>>> Customer.objects.filter(firstname='Mark').count()
2
>>> Customer.objects.all().aggregate(Avg('age'))
{'age__avg': 30.0}
>>> Customer.objects.all().aggregate(Avg('age'))
{'age__avg': 34.0}
>>> Customer.objects.all().aggregate(Max('age'))
{'age__max': 45}
>>> Customer.objects.all().aggregate(Min('age'))
{'age__min': 27}
>>> Customer.objects.all().aggregate(Sum('age'))
{'age__sum': 102}
>>> Customer.objects.all().aggregate(Sum('salary'))
{'salary__sum': Decimal('1024')}
>>> Customer.objects.all().aggregate(Avg('age'), Min('age'), Max('age'))
{'age__avg': 34.0, 'age__min': 27, 'age__max': 45}
>>> below_30 = Count('age', filter=Q(age__lte=30))
>>> above_30 = Count('age', filter=Q(age__gt=30))
>>>
>>> Customer.objects.annotate(above_30=above_30).annotate(below_30=below_30).values('above_30', 'below_30')
<QuerySet [{'above_30': 0, 'below_30': 1}, {'above_30': 1, 'below_30': 0}, {'above_30': 0, 'below_30': 0}, {'above_30': 0, 'below_30': 0}, {'above_30': 0, 'below_30': 1}, {'above_30': 0, 'below_30': 0}, {'above_30': 0, 'below_30': 0}]>