Topic: Django ORM – Advanced Queries, Aggregations, and Query Optimization (Part 2)

---

1. Aggregation Functions

• Django provides built-in functions for aggregating data.

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

# Average number of pages
avg_pages = Book.objects.aggregate(Avg("pages"))

# Total number of pages
total_pages = Book.objects.aggregate(Sum("pages"))

# Count of books per author
book_counts = Book.objects.values("author").annotate(total=Count("id"))

---

2. Grouping and Annotating

• annotate() is used to compute values for each row (e.g., totals per group).

# Number of books per author
from django.db.models import Count

authors = Author.objects.annotate(book_count=Count("book"))
for author in authors:
    print(author.name, author.book_count)

---

3. Complex Lookups with Q Objects

• Use Q for OR, AND, and NOT conditions.

from django.db.models import Q

# Books with title containing 'war' OR author name 'Leo Tolstoy'
books = Book.objects.filter(Q(title__icontains="war") | Q(author__name="Leo Tolstoy"))

# Books not published in 2023
books = Book.objects.filter(~Q(published_date__year=2023))

---

4. Selecting Specific Fields

• Use values() or values\_list() to retrieve specific fields.

# Dictionary of titles and authors
data = Book.objects.values("title", "author__name")

# List of titles
titles = Book.objects.values_list("title", flat=True)

---

5. Related Model Queries

• Use select\_related and prefetch\_related to optimize related data access.

# Optimized: Single JOIN query for ForeignKey
books = Book.objects.select_related("author")

# For ManyToMany or reverse relations
authors = Author.objects.prefetch_related("book_set")

---

6. Raw SQL Queries (When Necessary)

books = Book.objects.raw("SELECT * FROM myapp_book WHERE pages > %s", [300])
for book in books:
    print(book.title)

---

7. Performance Tips

• Use only() or defer() to limit retrieved fields.

books = Book.objects.only("title")

• Avoid chaining queries in loops.

• Use bulk\_create, bulk\_update for inserting/updating many records.

---

Summary

• Use aggregate(), annotate(), and Q objects for powerful filtering.

• Fetch only what you need using values, only, and select\_related.

• Optimize queries by reducing database hits and using Django’s ORM efficiently.

---

Exercise

• Write a Django query that returns all authors with more than 5 books, sorted by the number of books (descending). Then print their name and book count.

---

#Django #ORM #AdvancedQueries #QueryOptimization #WebDevelopment

https://t.iss.one/DataScience4