Master Advanced Django ORM Queries: Aggregates, Grouping, F & Q Objects

Introduction

Previously we covered basic and advanced Django ORM operations; you can refer to those articles for a refresher.

Aggregation

Aggregation functions like Sum and Avg can be used after filtering data to compute total and average values.

Example SQL:

SELECT SUM(price) AS "所有书总价格", avg(price) AS "所有书平均价格" FROM web_book;

Equivalent ORM:

price = models.Book.objects.all().aggregate(Sum("price"), Avg("price"))
print(price)

ORM returns a dict where keys are automatically generated as price__sum and price__avg. You can rename fields in the aggregation call to get custom column names.

Grouping

Grouping compresses rows with the same value and can count occurrences. When grouping by a foreign key you can also retrieve related fields.

Example: count books per publisher.

ret = models.Book.objects.values("publish_id").annotate(publish_count=Count("publish_id"))
print(ret)

Corresponding SQL:

SELECT `web_book`.`publish_id`, COUNT(`web_book`.`publish_id`) AS `publish_count`
FROM `web_book`
GROUP BY `web_book`.`publish_id`;

Grouping with foreign‑key details

To include publisher details:

ret = models.Book.objects.values("publish_id").annotate(publish_count=Count("publish_id")).values("publish__title", "publish__phone", "publish_count")
print(ret)

Grouping with HAVING

Use filter after annotation to emulate HAVING:

ret = models.Book.objects.values("publish_id").annotate(publish_count=Count("publish_id")).filter(publish_count__gt=2)
print(ret)

F expressions

F objects allow field‑to‑field comparisons and arithmetic.

Example: books where comment_num is less than collect_num:

book = models.Book.objects.filter(comment_num__lt=F("collect_num"))
print(book)

Example with multiplication:

book = models.Book.objects.filter(comment_num__lt=F("collect_num")*2)
print(book)

F can also be used in updates:

models.Book.objects.all().update(price=F("price") + 30)

Q objects

Q objects enable OR, AND, NOT, and dynamic query construction.

Simple OR query:

books = models.Book.objects.filter(Q(title="<<大明帝国>>") | Q(title="<<安史之乱>>"))
print(books)

Negation using ~Q:

books = models.Book.objects.filter(Q(title="<<大明帝国>>") | ~Q(title="<<安史之乱>>"))
print(books)

Combining Q with additional filters:

books = models.Book.objects.filter(Q(title="<<大明帝国>>") | ~Q(title="<<安史之乱>>"), publish_id=1)
print(books)

Dynamic Q construction (or/and selectable):

q = Q()
q.connector = "or"  # can be "and" as well
q.children.append(("publish_id", "1"))
q.children.append(("title__contains", "大明"))
books = models.Book.objects.filter(q)
print(books)

Create, Update, Delete

Adding a book can be done via objects.create, .save(), or passing a dictionary with **kwargs:

models.Book.objects.create(title="<<人类简史2>>", price=66.66, PublishDate="2020-01-02", comment_num=23, collect_num=12, publish_id=1)

Updating records requires a preceding filter:

models.Book.objects.filter(title="<<大明帝国>>").update(title="<<大明帝国666>>")

Deletion also follows a filter:

models.Book.objects.filter(title="<<大明帝国666>>").delete()

Conclusion

The article covers high‑level Django ORM operations such as aggregation, grouping, F and Q queries, and dynamic Q construction, providing a comprehensive reference for backend developers.