Django ORM – Multi-table Examples (Aggregation and Grouping Queries)

Aggregation Query (aggregate)

Aggregation query functions perform calculations on a set of values and return a single value.

Before using aggregation queries in Django, you need to import Avg, Max, Min, Count, Sum (capitalized first letter) from django.db.models.

from django.db.models import Avg, Max, Min, Count, Sum  # Import functions

The return value of an aggregation query is of dictionary type.

The aggregate() function is a terminating clause of QuerySet, generating a summarized value, similar to count().

After using aggregate(), the data type becomes a dictionary, and you can no longer use some APIs of the QuerySet data type.

Date data types (DateField) can use Max and Min.

In the returned dictionary: the key name defaults to (attribute name plus __aggregation function name), and the value is the calculated aggregation value.

If you want to customize the key name of the returned dictionary, you can use an alias:

aggregate(alias=aggregation function name("attribute name"))

Calculate the average price of all books:

Example

from django.db.models import Avg, Max, Min, Count, Sum  # Import functions
...
res = models.Book.objects.aggregate(Avg("price"))
print(res, type(res))
...

Calculate the number of all books, the most expensive price, and the cheapest price:

Example

res = models.Book.objects.aggregate(c=Count("id"), max=Max("price"), min=Min("price"))
print(res, type(res))

Grouping Query (annotate)

Grouping queries typically use aggregation functions, so you need to import Avg, Max, Min, Count, Sum (capitalized first letter) from django.db.models before using them.

from django.db.models import Avg, Max, Min, Count, Sum  # Import functions

Return Value:

After grouping, if values are taken with values, the return value is a QuerySet data type containing individual dictionaries;
After grouping, if values are taken with values_list, the return value is a QuerySet data type containing individual tuples.

The limit in MySQL is equivalent to slicing in the QuerySet data type in ORM.

Note:

Place aggregation functions inside annotate.

values or valueslist before annotate: values or valueslist declare the field to group by, and annotate performs the grouping.

values or values_list after annotate: annotate indicates grouping directly by the current table's primary key, and values or values_list indicate which fields to query, and you need to alias the aggregation functions in annotate and write their aliases in values or values_list.

Preparing Data and Creating Models

models.py

class Emp(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8, decimal_places=2)
    dep = models.CharField(max_length=32)
    province = models.CharField(max_length=32)

class Emps(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    salary = models.DecimalField(max_digits=8, decimal_places=2)
    dep = models.ForeignKey("Dep", on_delete=models.CASCADE)
    province = models.CharField(max_length=32)

class Dep(models.Model):
    title = models.CharField(max_length=32)

Data:

Executing in MySQL Command Line:

```sql
INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('1', 'Linghu Chong', '24', '6000.00', 'Sales Department', 'Henan'); 
INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('2', 'Ren Yingying', '18', '8000.00', 'Guan Gong Department', 'Guangdong'); 
INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('3', 'Ren Woxing', '56', '10000.00', 'Sales Department', 'Guangdong'); 
INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('4', 'Yue Lingshan', '19', '6000.00', 'Guan Gong Department', 'Henan'); 
INSERT INTO `app01_emp` (`id`, `name`, `age`, `salary`, `dep`, `province`) VALUES ('5', 'Xiaolongnv', '20', '8000.00', 'Guan Gong Department', 'Hebei'); 
INSERT INTO `app01_dep` (`id`, `title`) VALUES ('1', 'Sales Department'); 
INSERT INTO `app01_dep` (`id`, `title`) VALUES ('2', 'Guan Gong Department'); 
INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('2', 'Linghu Chong', '24', '8000.00', 'Henan', '1'); 
INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('3', 'Ren Yingying', '18', '9000.00', 'Guangdong', '2'); 
INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('4', 'Ren Woxing', '57', '10000.00', 'Guangdong', '1'); 
INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('5', 'Yue Lingshan', '19', '6000.00', 'Henan', '2'); 
INSERT INTO `app01_emps` (`id`, `name`, `age`, `salary`, `province`, `dep_id`) VALUES ('6', 'Xiaolongnv', '20', '8000.00', 'Hebei', '2');

Count the price of the cheapest book for each publisher:

Example

res = models.Publish.objects.values("name").annotate(in_price = Min("book__price"))
print(res)

You can see the following output in the command line:

&lt;QuerySet [{'name': 'tutorialpro', 'in_price': Decimal('100.00')}, {'name': 'tpro', 'in_price': Decimal('300.00')}]>

Count the number of authors for each book:

Example

res = models.Book.objects.annotate(c = Count("authors__name")).values("title","c")
print(res)

&lt;QuerySet [{'title': 'tutorialpro.org', 'c': 1}, {'title': 'python deepin', 'c': 1}, {'title': 'jQueryPro', 'c': 1}]>

Count the number of authors for each book starting with "tutorialpro":

Example

res = models.Book.objects.filter(title__startswith="tutorialpro").annotate(c = Count("authors__name")).values("title","c")
print(res)

&lt;QuerySet [{'title': 'tutorialpro.org', 'c': 1}]>

res = models.Book.objects.filter(title__startswith="tutorialpro").annotate(c=Count("authors__name")).values("title", "c")
print(res)

Count books with more than one author:

Example

res = models.Book.objects.annotate(c=Count("authors__name")).filter(c__gt=0).values("title", "c")
print(res)

&lt;QuerySet [{'title': 'tutorialpro.org', 'c': 1}, {'title': 'PythonPro', 'c': 1}, {'title': 'jQueryPro', 'c': 1}]>

Sort QuerySet by the number of authors in descending order:

Example

res = models.Book.objects.annotate(c=Count("authors__name")).order_by("-c").values("title", "c")
print(res)

Query the total price of books for each author:

Example

res = models.Author.objects.annotate(all=Sum("book__price")).values("name", "all")
print(res)

F() Queries

F() instances can reference fields within the model to compare values of two different fields within the same model instance.

Previously, filters were constructed to compare field values with constants. To compare two field values, F() is used.

Import F from django.db.models:

from django.db.models import F

Usage:

F("field_name")

F dynamically retrieves the value of an object's field and allows for operations.

Django supports arithmetic operations between F() objects and constants.

Update operations can also use F() functions.

Query employees with a salary greater than their age:

Example

from django.db.models import F 
...
book = models.Emp.objects.filter(salary__gt=F("age")).values("name", "age")
...

Increase the price of each book by 100:

Example

res = models.Book.objects.update(price=F("price") + 100)
print(res)

Q() Queries

from django.db.models import Q

Usage:

Q(condition)

For example:

Q(title__startswith="菜")

Previously, multiple conditions in filters were combined with AND. For more complex queries (e.g., OR statements), Q can be used.

Q objects can be combined using & | ~ (AND, OR, NOT) operators.

Precedence: ~ & |.

Q objects and keyword arguments can be mixed. Q objects and keyword arguments are combined with AND, but Q objects must precede all keyword arguments.

Query book titles and prices where the price is greater than 350 or the title starts with "菜":

Example

...
res = models.Book.objects.filter(Q(price__gt=350) | Q(title__startswith="菜")).values("title", "price")
print(res)
...

Query books ending with "菜" or not published in October 2010:

Example

res = models.Book.objects.filter(Q(title__endswith="菜") | ~Q(Q(pub_date__year=2010) & Q(pub_date__month=10)))
print(res)

Query books published in 2004 or 1999 containing "菜" in the title:

Q objects and keywords mixed, Q objects must be first:

Example

res = models.Book.objects.filter(Q(pub_date__year=2004) | Q(pub_date__year=1999), title__contains="菜")
print(res)

❮ Django Tutorial Django Intro ❯