Aggregation, Q Objects and F Expressions

annotate, aggregate, Q, F

annotate adds computed values per row (count of products per category), aggregate returns totals for a whole QuerySet, Q objects combine conditions with OR and NOT, and F expressions refer to column values inside queries. update(price=F("price") * 2) updates in one SQL statement without reading rows into Python, avoiding race conditions between read and write.

Per-category stats, totals, OR queries and an F update, run

I ran this script with python manage.py shell --no-imports in the demo project (Django 6.1.1, SQLite). Each category has two products with average prices; totals come from aggregate; Q finds cheap or Home products; F doubles Office prices directly in SQL.

from django.db.models import Avg, Count, F, Max, Q, Sum
from catalog.models import Category, Product

for c in Category.objects.annotate(n=Count("products"), avg=Avg("products__price")).order_by("name"):
    print(c.name, c.n, round(c.avg, 2))
print(Product.objects.aggregate(total=Sum("price"), top=Max("price")))
cheap_or_home = Product.objects.filter(Q(price__lt=200) | Q(category__name="Home")).order_by("name")
print([p.name for p in cheap_or_home])
Product.objects.filter(category__name="Office").update(price=F("price") * 2)   # done in SQL, no race
print(list(Product.objects.filter(category__name="Office").values_list("name", "price")))

Output:

Home 2 874.50
Office 2 509.50
{'total': Decimal('2768'), 'top': Decimal('1499')}
['Lamp', 'Mug', 'Notebook']
[('Notebook', Decimal('240.00')), ('Pen', Decimal('1798.00'))]

Prefer database-side updates

F expressions and update() avoid lost updates that read-modify-write code in Python can cause.

Quick check: What does F("price") * 2 in update() do?

• Creates a new field
• Loads all rows into Python first
• Doubles each row's price in a single SQL statement
• Formats the price as text