TopolearnReports, counters and complex filters belong in the database, not in Python loops.
Q objects combine conditions with | (OR), & (AND) and ~ (NOT):
Post.objects.filter(Q(status="pub") | Q(author__name="Ann"), ~Q(views=0))F expressions refer to a column's value inside the query:
- compare columns:
filter(updated__gt=F("created")) - update atomically:
Post.objects.filter(pk=pk).update(views=F("views") + 1)runsviews = views + 1in SQL, with no race between concurrent requests. Doingpost.views += 1; post.save()loses increments under load.
`aggregate()` summarizes a whole QuerySet into a dict: Post.objects.aggregate(total=Sum("views"), avg=Avg("views")) returns {"total": 15, "avg": 5.0}.
`annotate()` adds a computed value to each row:
Author.objects.annotate(n=Count("posts")).filter(n__gte=5).order_by("-n")Annotations can be filtered and ordered like fields, and conditional aggregation counts subsets: Count("posts", filter=Q(posts__status="pub")).
values("field").annotate(...) turns into GROUP BY field, the ORM's way of grouping: Post.objects.values("author__name").annotate(total=Sum("views")).
Database functions (Lower, Coalesce, TruncMonth, Concat, ...) and Case/When work anywhere expressions do. For correlated queries, Subquery(...) with OuterRef("pk") and Exists(...) express "latest comment per post" or "has any unapproved comment" in a single SQL statement.
Beware combining two multi-valued Counts in one annotate: the joins multiply rows. Use distinct=True or subqueries.
Example
from django.db.models import Avg, Count, Exists, F, OuterRef, Q, Sum
from django.db.models.functions import TruncMonth
from blog.models import Author, Comment, Post
popular_or_mine = Post.objects.filter(Q(views__gte=100) | Q(author__name="Ann"), ~Q(status="draft"))
Post.objects.filter(pk=1).update(views=F("views") + 1) # race-free counter
Post.objects.aggregate(total=Sum("views"), avg=Avg("views")) # {'total': 15, 'avg': 5.0}
authors = (Author.objects
.annotate(n_posts=Count("posts"),
n_published=Count("posts", filter=Q(posts__status="pub")))
.filter(n_posts__gte=1)
.order_by("-n_posts"))
# [(a.name, a.n_posts, a.n_published) for a in authors] -> [('Ann', 2, 1), ('Bo', 1, 1)]
per_author = Post.objects.values("author__name").annotate(total=Sum("views")) # GROUP BY
per_month = Post.objects.annotate(m=TruncMonth("created")).values("m").annotate(n=Count("id"))
pending = Comment.objects.filter(post=OuterRef("pk"), approved=False)
needs_review = Post.objects.filter(Exists(pending))Remember
- Q objects build OR/NOT conditions; F references columns inside SQL
- update(x=F("x") + 1) avoids lost updates
- aggregate() returns one dict; annotate() adds a value per row
- values(...).annotate(...) is GROUP BY
Watch out for
- Incrementing counters in Python with obj.x += 1; obj.save(), which loses concurrent updates.