Bài 3.4: Advanced QuerySets - Aggregation, Annotation & F/Q Objects

**Series Navigation:** - [Bài 3.1: QuerySet Fundamentals & Lazy Loading](/django-advance/advanced-querysets-optimization-phan-1) - [Bài 3.2: select_related() vs prefetch_related()](/django-advance/advanced-querysets-optimization-phan-2) - [Bài 3.3: N+1 Problem và Solutions](/django-advance/advanced-querysets-optimization-phan-3) - **Bài 3.4 (bài này): Aggregation, Annotation & F/Q Objects** 👈

Mục Tiêu Bài 3.4

Sau khi hoàn thành Bài 3.4 này, bạn sẽ:

✅ Sử dụng aggregate functions (Count, Sum, Avg, Max, Min)
✅ Annotate querysets với computed fields
✅ Sử dụng F() objects cho field references
✅ Build complex queries với Q() objects
✅ Implement conditional expressions (Case, When)
✅ Write subqueries với Subquery và OuterRef
✅ Optimize queries với Exists()
✅ Understand window functions basics

Aggregation - Tính Toán Trên Toàn Bộ Dataset

What is Aggregation?

Aggregation = Tính toán giá trị summary từ nhiều rows (COUNT, SUM, AVG, MAX, MIN).

Key Points:

Returns single value (or dict of values)
Operates on entire queryset
Executes immediately (not lazy)
Result is NOT a queryset

Basic Aggregate Functions

Import:

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

Models:

from django.db import modelsfrom django.contrib.auth.models import User class Author(models.Model):    name = models.CharField(max_length=100)    age = models.IntegerField()    birth_year = models.IntegerField(null=True) class Book(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, related_name='books', on_delete=models.CASCADE)    price = models.DecimalField(max_digits=10, decimal_places=2)    cost = models.DecimalField(max_digits=10, decimal_places=2, default=0)    pages = models.IntegerField()    published_date = models.DateField()    written_date = models.DateField(null=True)    rating = models.DecimalField(max_digits=3, decimal_places=2)    views = models.IntegerField(default=0)    category = models.CharField(max_length=50, default='General') class Review(models.Model):    book = models.ForeignKey(Book, related_name='reviews', on_delete=models.CASCADE)    user = models.ForeignKey(User, on_delete=models.CASCADE)    text = models.TextField()    rating = models.DecimalField(max_digits=3, decimal_places=2)    created_at = models.DateTimeField(auto_now_add=True)

Count - Đếm Records

Basic Count:

# Count all bookstotal_books = Book.objects.count()# SQL: SELECT COUNT(*) FROM book # Count with filterpublished_count = Book.objects.filter(published_date__year=2024).count()# SQL: SELECT COUNT(*) FROM book WHERE EXTRACT(YEAR FROM published_date) = 2024

Count with aggregate():

from django.db.models import Count result = Book.objects.aggregate(total=Count('id'))# Returns: {'total': 150}# SQL: SELECT COUNT(id) AS total FROM book

Count Distinct:

# Count unique authorsresult = Book.objects.aggregate(    unique_authors=Count('author', distinct=True))# Returns: {'unique_authors': 45}# SQL: SELECT COUNT(DISTINCT author_id) AS unique_authors FROM book

Sum - Tổng Giá Trị

Total Sum:

from django.db.models import Sum result = Book.objects.aggregate(total_pages=Sum('pages'))# Returns: {'total_pages': 125000}# SQL: SELECT SUM(pages) AS total_pages FROM book

Sum with Filter:

result = Book.objects.filter(    published_date__year=2024).aggregate(    revenue=Sum('price'))# Returns: {'revenue': Decimal('15000.00')}

Multiple Sums:

result = Book.objects.aggregate(    total_pages=Sum('pages'),    total_revenue=Sum('price'),    book_count=Count('id'))# Returns: {#     'total_pages': 125000,#     'total_revenue': Decimal('50000.00'),#     'book_count': 150# }

Avg - Trung Bình

Average Value:

from django.db.models import Avg result = Book.objects.aggregate(avg_rating=Avg('rating'))# Returns: {'avg_rating': 4.2}# SQL: SELECT AVG(rating) AS avg_rating FROM book

Average with Grouping (use annotate - covered later):

# Average pages per authorauthors = Author.objects.annotate(    avg_pages=Avg('books__pages'))

Max and Min

Maximum Value:

from django.db.models import Max, Min result = Book.objects.aggregate(    highest_price=Max('price'),    lowest_price=Min('price'),    newest_book=Max('published_date'),    oldest_book=Min('published_date'))# Returns: {#     'highest_price': Decimal('99.99'),#     'lowest_price': Decimal('9.99'),#     'newest_book': date(2024, 12, 1),#     'oldest_book': date(2020, 1, 15)# }

Aggregate vs Count Method

Using aggregate():

result = Book.objects.aggregate(total=Count('id'))print(result['total'])  # Access dict key

Using count():

total = Book.objects.count()print(total)  # Direct integer

When to Use Which:

count(): When you only need count
aggregate(): When you need multiple aggregations or specific naming

Annotation - Add Computed Fields to QuerySet

What is Annotation?

Annotation = Add computed fields to each object in queryset.

Key Differences from Aggregation:

Returns queryset (still lazy)
Adds fields to each object
Can be chained with other queryset methods
Result can be filtered further

Basic Annotate

Count Related Objects:

from django.db.models import Count # Count books per authorauthors = Author.objects.annotate(book_count=Count('books')) for author in authors:    print(f"{author.name}: {author.book_count} books")    # author.book_count is accessible like any field!

SQL Generated:

SELECT author.*, COUNT(book.id) AS book_countFROM authorLEFT JOIN book ON author.id = book.author_idGROUP BY author.id;

Multiple Annotations

Complex Aggregations:

from django.db.models import Count, Avg, Sum, Max authors = Author.objects.annotate(    book_count=Count('books'),    avg_rating=Avg('books__rating'),    total_pages=Sum('books__pages'),    highest_price=Max('books__price')) for author in authors:    print(f"{author.name}:")    print(f"  Books: {author.book_count}")    print(f"  Avg Rating: {author.avg_rating}")    print(f"  Total Pages: {author.total_pages}")    print(f"  Max Price: ${author.highest_price}")

Filter on Annotated Fields

Powerful Pattern:

# Find prolific authors (more than 5 books)prolific_authors = Author.objects.annotate(    book_count=Count('books')).filter(    book_count__gt=5) # Find authors with high average ratingstop_authors = Author.objects.annotate(    avg_rating=Avg('books__rating')).filter(    avg_rating__gte=4.5).order_by('-avg_rating')

SQL Generated:

SELECT author.*, AVG(book.rating) AS avg_ratingFROM authorLEFT JOIN book ON author.id = book.author_idGROUP BY author.idHAVING AVG(book.rating) >= 4.5ORDER BY avg_rating DESC;

Annotate with Conditional Counting

Count with Filters:

from django.db.models import Q authors = Author.objects.annotate(    total_books=Count('books'),    recent_books=Count('books', filter=Q(books__published_date__year=2024)),    highly_rated=Count('books', filter=Q(books__rating__gte=4.5))) for author in authors:    print(f"{author.name}:")    print(f"  Total: {author.total_books}")    print(f"  Recent: {author.recent_books}")    print(f"  Highly Rated: {author.highly_rated}")

Key Feature: filter=Q(...) parameter in aggregate functions (Django 2.0+)

F() Objects - Field References

What are F() Objects?

F() objects = Reference to model field values in database queries (without loading into Python).

Benefits:

✅ Perform operations in database
✅ Avoid race conditions
✅ More efficient
✅ Atomic operations

Basic F() Usage

Import:

from django.db.models import F

Example 1: Increment Field

# ❌ Bad: Load into Python, then save (race condition!)book = Book.objects.get(id=1)book.views = book.views + 1book.save() # ✅ Good: Atomic database operationBook.objects.filter(id=1).update(views=F('views') + 1)# SQL: UPDATE book SET views = views + 1 WHERE id = 1

Example 2: Copy Field Value

# Set discount_price to 90% of original priceBook.objects.update(discount_price=F('price') * 0.9)# SQL: UPDATE book SET discount_price = price * 0.9

F() in Filters

Compare Two Fields:

# Find books where price > pages (weird but demonstrates concept)expensive_per_page = Book.objects.filter(price__gt=F('pages') / 10) # Find books published in same year as author's birthbooks = Book.objects.filter(    published_date__year=F('author__birth_year'))

Date Arithmetic:

from datetime import timedelta # Find books published within 1 year of being writtenrecent_releases = Book.objects.filter(    published_date__lte=F('written_date') + timedelta(days=365))

F() with Annotations

Calculate Derived Values:

from django.db.models import F # Calculate profit marginbooks = Book.objects.annotate(    profit=F('price') - F('cost'),    profit_margin=(F('price') - F('cost')) / F('price') * 100) for book in books:    print(f"{book.title}: ${book.profit} ({book.profit_margin:.2f}%)")

String Operations:

from django.db.models.functions import Concatfrom django.db.models import Value # Concatenate fieldsauthors = Author.objects.annotate(    full_info=Concat(        F('name'),        Value(' - Age: '),        F('age'),        Value(' years')    ))

F() Expressions in Updates

Bulk Update with Calculations:

# Increase all book prices by 10%Book.objects.update(price=F('price') * 1.1) # Give 5-star books a bonus rating displayBook.objects.filter(rating=5.0).update(    display_rating=F('rating') + 0.5) # Swap two fieldsBook.objects.update(    temp=F('field_a'),    field_a=F('field_b'),    field_b=F('temp'))

F() and Race Conditions

Problem Without F():

# Two requests at same time:# Request 1: Read views=100, increment to 101, save# Request 2: Read views=100, increment to 101, save# Result: views=101 (should be 102!) book = Book.objects.get(id=1)book.views += 1book.save()

Solution With F():

# Atomic operation at database levelBook.objects.filter(id=1).update(views=F('views') + 1)# Both requests result in correct final value

Q() Objects - Complex Queries

What are Q() Objects?

Q() objects = Complex query conditions with AND, OR, NOT logic.

Use Cases:

Multiple OR conditions
Combining AND/OR
NOT conditions
Dynamic query building

Basic Q() Usage

Import:

from django.db.models import Q

OR Conditions:

# Find books by author "John" OR "Jane"books = Book.objects.filter(    Q(author__name='John Doe') | Q(author__name='Jane Smith'))# SQL: WHERE author.name = 'John Doe' OR author.name = 'Jane Smith'

AND Conditions (explicit):

# Expensive AND highly ratedbooks = Book.objects.filter(    Q(price__gte=50) & Q(rating__gte=4.5))# SQL: WHERE price >= 50 AND rating >= 4.5 # Same as:books = Book.objects.filter(price__gte=50, rating__gte=4.5)

NOT Conditions:

# Books NOT by "John Doe"books = Book.objects.filter(~Q(author__name='John Doe'))# SQL: WHERE NOT author.name = 'John Doe' # Exclude low-rated OR expensive booksbooks = Book.objects.exclude(Q(rating__lt=3) | Q(price__gt=100))

Complex Q() Combinations

Mixing AND/OR:

# (High rating OR low price) AND recentbooks = Book.objects.filter(    (Q(rating__gte=4.5) | Q(price__lte=20)) &    Q(published_date__year=2024))# SQL: WHERE (rating >= 4.5 OR price <= 20) AND EXTRACT(YEAR FROM published_date) = 2024

Nested Conditions:

# ((Cheap OR medium price) AND high rating) OR (expensive AND perfect rating)books = Book.objects.filter(    (        (Q(price__lte=20) | Q(price__lte=50)) &        Q(rating__gte=4.0)    ) |    (        Q(price__gt=50) &        Q(rating=5.0)    ))

Dynamic Q() Building

Build Query Conditionally:

def search_books(title=None, author=None, min_rating=None, max_price=None):    """Build dynamic search query"""    query = Q()  # Empty Q object        if title:        query &= Q(title__icontains=title)        if author:        query &= Q(author__name__icontains=author)        if min_rating:        query &= Q(rating__gte=min_rating)        if max_price:        query &= Q(price__lte=max_price)        return Book.objects.filter(query) # Usagebooks = search_books(title='Django', min_rating=4.0, max_price=50)

OR Conditions Dynamically:

def search_multiple_authors(author_names):    """Find books by any of the given authors"""    query = Q()        for name in author_names:        query |= Q(author__name__icontains=name)        return Book.objects.filter(query) # Usagebooks = search_multiple_authors(['John', 'Jane', 'Bob'])# SQL: WHERE author.name ILIKE '%John%' OR author.name ILIKE '%Jane%' OR author.name ILIKE '%Bob%'

Across Relationships:

# Books by authors aged 30-50 OR with "Dr." titlebooks = Book.objects.filter(    Q(author__age__range=(30, 50)) |    Q(author__name__startswith='Dr.')) # Books with reviews by specific users OR with high ratingsbooks = Book.objects.filter(    Q(reviews__user__username='john') |    Q(reviews__rating__gte=4.5)).distinct()

Conditional Expressions - Case/When

What are Conditional Expressions?

Conditional expressions = SQL CASE/WHEN statements in Django ORM.

Use Cases:

Categorize data
Conditional calculations
Different logic per row

Basic Case/When

Import:

from django.db.models import Case, When, Value, CharField, IntegerField, DecimalField

Example - Categorize Books:

from django.db.models import Case, When, Value, CharField books = Book.objects.annotate(    price_category=Case(        When(price__lte=20, then=Value('Cheap')),        When(price__lte=50, then=Value('Medium')),        When(price__gt=50, then=Value('Expensive')),        default=Value('Unknown'),        output_field=CharField()    )) for book in books:    print(f"{book.title}: ${book.price} - {book.price_category}")

SQL Generated:

SELECT *,    CASE        WHEN price <= 20 THEN 'Cheap'        WHEN price <= 50 THEN 'Medium'        WHEN price > 50 THEN 'Expensive'        ELSE 'Unknown'    END AS price_categoryFROM book;

Conditional Aggregation

Count with Conditions:

from django.db.models import Count, Case, When, IntegerField authors = Author.objects.annotate(    total_books=Count('books'),    cheap_books=Count(        Case(            When(books__price__lte=20, then=1),            output_field=IntegerField()        )    ),    expensive_books=Count(        Case(            When(books__price__gt=50, then=1),            output_field=IntegerField()        )    ))

Alternative with filter Parameter (Simpler):

authors = Author.objects.annotate(    total_books=Count('books'),    cheap_books=Count('books', filter=Q(books__price__lte=20)),    expensive_books=Count('books', filter=Q(books__price__gt=50)))

Conditional Values

Different Calculations per Condition:

from django.db.models import DecimalField books = Book.objects.annotate(    discount=Case(        When(rating__gte=4.5, then=F('price') * 0.2),  # 20% off        When(rating__gte=4.0, then=F('price') * 0.1),  # 10% off        When(rating__gte=3.0, then=F('price') * 0.05), # 5% off        default=Value(0),        output_field=DecimalField()    ),    final_price=F('price') - F('discount'))

Ordering with Case

Custom Sort Order:

from django.db.models import Case, When # Prioritize certain categoriesbooks = Book.objects.annotate(    category_order=Case(        When(category='Fiction', then=Value(1)),        When(category='Science', then=Value(2)),        When(category='History', then=Value(3)),        default=Value(99),        output_field=IntegerField()    )).order_by('category_order', 'title')

Subqueries and OuterRef

What are Subqueries?

Subqueries = Nested queries that reference outer query.

Use Cases:

Get latest related object
Filter based on related aggregate
Complex annotations

Basic Subquery

Import:

from django.db.models import Subquery, OuterRef

Example - Latest Review per Book:

from django.db.models import Subquery, OuterRef # Subquery to get latest review text for each booklatest_review = Review.objects.filter(    book=OuterRef('pk')).order_by('-created_at').values('text')[:1] books = Book.objects.annotate(    latest_review_text=Subquery(latest_review)) for book in books:    print(f"{book.title}: {book.latest_review_text}")

SQL Generated:

SELECT book.*,    (SELECT review.text     FROM review     WHERE review.book_id = book.id     ORDER BY review.created_at DESC     LIMIT 1) AS latest_review_textFROM book;

OuterRef - Reference Outer Query

OuterRef = Reference to field in outer query (like SQL correlation).

Example - Authors with Best Book:

# Get author's highest-rated book titlebest_book = Book.objects.filter(    author=OuterRef('pk')).order_by('-rating').values('title')[:1] authors = Author.objects.annotate(    best_book_title=Subquery(best_book))

Subquery with Aggregation

Aggregate in Subquery:

from django.db.models import Avg # Average rating of books by same authoravg_author_rating = Book.objects.filter(    author=OuterRef('author')).values('author').annotate(    avg=Avg('rating')).values('avg') books = Book.objects.annotate(    author_avg_rating=Subquery(avg_author_rating)) for book in books:    print(f"{book.title}: {book.rating} (author avg: {book.author_avg_rating})")

Complex Subquery Example

Books Above Author's Average:

# Find books rated higher than author's averageavg_rating_subquery = Book.objects.filter(    author=OuterRef('author')).values('author').annotate(    avg=Avg('rating')).values('avg') above_average_books = Book.objects.annotate(    author_avg=Subquery(avg_rating_subquery)).filter(    rating__gt=F('author_avg'))

Exists() - Efficient Existence Checks

What is Exists()?

Exists() = Check if related objects exist (more efficient than count).

Benefits:

✅ Only checks existence (not count)
✅ Short-circuits on first match
✅ Uses SQL EXISTS clause
✅ More efficient than filtering

Basic Exists() Usage

Import:

from django.db.models import Exists, OuterRef

Example - Authors with Books:

has_books = Book.objects.filter(author=OuterRef('pk')) authors = Author.objects.annotate(    has_published_books=Exists(has_books)) for author in authors:    if author.has_published_books:        print(f"{author.name} has published books")

SQL Generated:

SELECT author.*,    EXISTS(        SELECT 1        FROM book        WHERE book.author_id = author.id    ) AS has_published_booksFROM author;

Exists() with Filters

Conditional Existence:

# Check if author has highly-rated bookshas_good_books = Book.objects.filter(    author=OuterRef('pk'),    rating__gte=4.5) authors = Author.objects.annotate(    has_bestsellers=Exists(has_good_books)).filter(has_bestsellers=True)

Exists() vs Count() Performance

Scenario: Check if author has any books.

Using Count (Slower):

# Counts ALL booksauthors = Author.objects.annotate(    book_count=Count('books')).filter(book_count__gt=0)# SQL: SELECT COUNT(*) ... (counts everything)

Using Exists (Faster):

# Stops at first matchhas_books = Book.objects.filter(author=OuterRef('pk'))authors = Author.objects.annotate(    has_books=Exists(has_books)).filter(has_books=True)# SQL: SELECT EXISTS(...) LIMIT 1 (stops immediately)

Performance Difference:

Count: Must scan all related rows
Exists: Stops at first match
10x-100x faster for large datasets!

Window Functions (Advanced)

What are Window Functions?

Window functions = Perform calculations across rows related to current row.

Use Cases:

Row numbering
Running totals
Percentile ranks
Moving averages

Requires: Django 2.0+ and PostgreSQL/Oracle/SQL Server.

Basic Window Function

Import:

from django.db.models import Window, Ffrom django.db.models.functions import RowNumber

Example - Rank Books by Price:

from django.db.models import Windowfrom django.db.models.functions import RowNumber books = Book.objects.annotate(    price_rank=Window(        expression=RowNumber(),        order_by=F('price').desc()    )) for book in books:    print(f"#{book.price_rank}: {book.title} - ${book.price}")

Window with Partition

Rank Within Groups:

from django.db.models.functions import Rank books = Book.objects.annotate(    author_rank=Window(        expression=Rank(),        partition_by=[F('author')],        order_by=F('rating').desc()    )) # Each author's books ranked by rating within author's collectionfor book in books:    print(f"{book.author.name} - #{book.author_rank}: {book.title}")

SQL Generated:

SELECT *,    RANK() OVER (        PARTITION BY author_id        ORDER BY rating DESC    ) AS author_rankFROM book;

Running Total

Cumulative Sum:

from django.db.models.functions import Sum as WindowSum books = Book.objects.annotate(    cumulative_revenue=Window(        expression=WindowSum('price'),        order_by=F('published_date').asc()    )).order_by('published_date') for book in books:    print(f"{book.published_date}: ${book.cumulative_revenue}")

Performance Tips

Tip 1: Use only() and defer()

Load Only Needed Fields:

# Only load title and price (skip content)books = Book.objects.only('title', 'price') # Load everything EXCEPT content (large field)books = Book.objects.defer('content')

When to Use:

Large text/blob fields
Many fields but only need few
Performance-critical endpoints

Tip 2: Use values() for Dictionaries

Skip Model Instantiation:

# Returns list of dicts (faster)books = Book.objects.values('id', 'title', 'price')# [{'id': 1, 'title': 'Book 1', 'price': 29.99}, ...] # Even faster with values_listprices = Book.objects.values_list('price', flat=True)# [29.99, 39.99, 19.99, ...]

Performance Gain: 2-3x faster for large datasets.

Tip 3: Use iterator() for Large QuerySets

Memory Efficient Processing:

# Process millions of recordsfor book in Book.objects.iterator(chunk_size=1000):    process_book(book)  # No memory overflow

Tip 4: Database Indexes

Add Indexes for Frequent Queries:

class Book(models.Model):    title = models.CharField(max_length=200, db_index=True)    author = models.ForeignKey(Author, db_index=True)    published_date = models.DateField(db_index=True)        class Meta:        indexes = [            models.Index(fields=['author', 'published_date']),            models.Index(fields=['-rating', 'price']),        ]

Tip 5: Use select_for_update() for Locking

Prevent Race Conditions:

from django.db import transaction with transaction.atomic():    book = Book.objects.select_for_update().get(id=1)    book.stock -= 1    book.save()    # Lock held until transaction commits

Tóm Tắt Bài 3.4

Trong Bài 3.4 này, bạn đã học:

✅ Aggregation:

Count, Sum, Avg, Max, Min functions
aggregate() returns single value/dict
Operates on entire queryset
Executes immediately

✅ Annotation:

annotate() adds fields to each object
Returns queryset (chainable)
Can filter on annotated fields
GROUP BY in SQL

✅ F() Objects:

Reference database fields
Atomic operations
Avoid race conditions
Field comparisons và calculations

✅ Q() Objects:

Complex AND/OR/NOT logic
Dynamic query building
Nested conditions
Related lookups

✅ Conditional Expressions:

Case/When for SQL CASE statements
Categorization
Conditional aggregation
Custom ordering

✅ Subqueries:

Nested queries với Subquery()
OuterRef for correlation
Latest related objects
Complex annotations

✅ Exists():

Efficient existence checks
SQL EXISTS clause
10-100x faster than Count for existence
Short-circuits on first match

✅ Window Functions:

Row numbering và ranking
Partition by groups
Running totals
Percentile calculations

✅ Performance Tips:

only() và defer() for selective loading
values() for dictionary results
iterator() for memory efficiency
Database indexes
select_for_update() for locking

Exercises

Exercise 1: E-commerce Analytics

Models:

from django.db import models class Customer(models.Model):    name = models.CharField(max_length=100)    join_date = models.DateField() class Product(models.Model):    name = models.CharField(max_length=200)    price = models.DecimalField(max_digits=10, decimal_places=2) class Order(models.Model):    customer = models.ForeignKey(Customer, related_name='orders', on_delete=models.CASCADE)    order_date = models.DateField()    status = models.CharField(max_length=20)  # pending, completed, cancelled class OrderItem(models.Model):    order = models.ForeignKey(Order, related_name='items', on_delete=models.CASCADE)    product = models.ForeignKey(Product, on_delete=models.CASCADE)    quantity = models.IntegerField()    unit_price = models.DecimalField(max_digits=10, decimal_places=2)

Tasks:

Annotate each customer with:
- Total orders count
- Completed orders count
- Total revenue (sum of all order items)
- Average order value
- Last order date
Find customers who spent > $1000
Rank customers by total revenue
Categorize customers: VIP (>$5000), Regular ($1000-$5000), New (<$1000)

Exercise 2: Blog Statistics

Models:

from django.db import models class Author(models.Model):    name = models.CharField(max_length=100) class Post(models.Model):    title = models.CharField(max_length=200)    author = models.ForeignKey(Author, related_name='posts', on_delete=models.CASCADE)    content = models.TextField()    published_date = models.DateField()    views = models.IntegerField(default=0) class Comment(models.Model):    post = models.ForeignKey(Post, related_name='comments', on_delete=models.CASCADE)    text = models.TextField()    is_approved = models.BooleanField(default=False)

Tasks:

Annotate posts with:
- Comment count
- Approved comment count
- Has comments (using Exists)
Find posts with comments but no approved comments
Get each author's most viewed post (using Subquery)
Calculate engagement score: (views + comments * 10)
Find posts above their author's average views

Exercise 3: Inventory Management

Models:

from django.db import models class Warehouse(models.Model):    name = models.CharField(max_length=100)    location = models.CharField(max_length=200) class Product(models.Model):    name = models.CharField(max_length=200)    price = models.DecimalField(max_digits=10, decimal_places=2)    reorder_level = models.IntegerField() class Stock(models.Model):    warehouse = models.ForeignKey(Warehouse, related_name='stocks', on_delete=models.CASCADE)    product = models.ForeignKey(Product, related_name='stocks', on_delete=models.CASCADE)    quantity = models.IntegerField()    last_updated = models.DateTimeField(auto_now=True)

Tasks:

Annotate products with:
- Total stock across all warehouses
- Number of warehouses carrying product
- Needs reorder (stock < reorder_level) using Case/When
Find products out of stock in all warehouses
Calculate total inventory value per warehouse
Find warehouses with low stock alerts (>10 products below reorder level)
Use F() to update stock levels after sale

Hints:

Use aggregate() for totals across all
Use annotate() for per-object calculations
Use Exists() for efficient checks
Use Case/When for conditional flags
Use F() for atomic updates

Exercise Solutions

Solution 1: E-commerce Analytics

from django.db.models import Count, Sum, Avg, Max, Case, When, Value, F, Qfrom django.db.models.functions import Coalesce # Task 1: Annotate customers with metricscustomers = Customer.objects.annotate(    total_orders=Count('orders'),    completed_orders=Count('orders', filter=Q(orders__status='completed')),    total_revenue=Coalesce(        Sum('orders__items__quantity', filter=Q(orders__status='completed')) *         Sum('orders__items__unit_price', filter=Q(orders__status='completed')),        Value(0)    ),    avg_order_value=Avg('orders__items__quantity' * F('orders__items__unit_price')),    last_order_date=Max('orders__order_date')) # Task 2: Customers who spent > $1000high_spenders = Customer.objects.annotate(    total_spent=Sum(        F('orders__items__quantity') * F('orders__items__unit_price'),        filter=Q(orders__status='completed')    )).filter(total_spent__gt=1000) # Task 3: Rank customers by revenuefrom django.db.models import Windowfrom django.db.models.functions import Rank customers_ranked = Customer.objects.annotate(    total_revenue=Sum(        F('orders__items__quantity') * F('orders__items__unit_price'),        filter=Q(orders__status='completed')    ),    revenue_rank=Window(        expression=Rank(),        order_by=F('total_revenue').desc()    )).order_by('revenue_rank') # Task 4: Categorize customerscustomers_categorized = Customer.objects.annotate(    total_spent=Sum(        F('orders__items__quantity') * F('orders__items__unit_price'),        filter=Q(orders__status='completed')    ),    customer_tier=Case(        When(total_spent__gt=5000, then=Value('VIP')),        When(total_spent__gte=1000, then=Value('Regular')),        default=Value('New'),        output_field=CharField()    ))

Solution 2: Blog Statistics

from django.db.models import Count, Exists, OuterRef, Subquery # Task 1: Annotate postsposts = Post.objects.annotate(    comment_count=Count('comments'),    approved_count=Count('comments', filter=Q(comments__is_approved=True)),    has_comments=Exists(Comment.objects.filter(post=OuterRef('pk')))) # Task 2: Posts with comments but no approved commentsposts_pending = Post.objects.annotate(    total_comments=Count('comments'),    approved_comments=Count('comments', filter=Q(comments__is_approved=True))).filter(    total_comments__gt=0,    approved_comments=0) # Task 3: Each author's most viewed postmost_viewed_subquery = Post.objects.filter(    author=OuterRef('pk')).order_by('-views').values('title')[:1] authors = Author.objects.annotate(    most_viewed_post=Subquery(most_viewed_subquery)) # Task 4: Engagement scoreposts_with_score = Post.objects.annotate(    comment_count=Count('comments'),    engagement_score=F('views') + (F('comment_count') * 10)).order_by('-engagement_score') # Task 5: Posts above author's averageauthor_avg_views = Post.objects.filter(    author=OuterRef('author')).values('author').annotate(    avg=Avg('views')).values('avg') above_average = Post.objects.annotate(    author_avg=Subquery(author_avg_views)).filter(views__gt=F('author_avg'))

Solution 3: Inventory Management

from django.db.models import Sum, Count, Case, When, IntegerField, DecimalField, BooleanField # Task 1: Annotate productsproducts = Product.objects.annotate(    total_stock=Sum('stocks__quantity'),    warehouse_count=Count('stocks__warehouse', distinct=True),    needs_reorder=Case(        When(total_stock__lt=F('reorder_level'), then=Value(True)),        default=Value(False),        output_field=BooleanField()    )) # Task 2: Out of stock productsout_of_stock = Product.objects.annotate(    total_stock=Sum('stocks__quantity')).filter(    Q(total_stock=0) | Q(total_stock__isnull=True)) # Task 3: Inventory value per warehousewarehouses = Warehouse.objects.annotate(    total_value=Sum(F('stocks__quantity') * F('stocks__product__price'))) for warehouse in warehouses:    print(f"{warehouse.name}: ${warehouse.total_value}") # Task 4: Warehouses with low stock alertswarehouses_with_alerts = Warehouse.objects.annotate(    low_stock_count=Count(        'stocks',        filter=Q(stocks__quantity__lt=F('stocks__product__reorder_level'))    )).filter(low_stock_count__gt=10) # Task 5: Update stock after saledef process_sale(product_id, warehouse_id, quantity):    """Update stock using F() for atomic operation"""    Stock.objects.filter(        product_id=product_id,        warehouse_id=warehouse_id    ).update(        quantity=F('quantity') - quantity    )        # Check if reorder needed    stock = Stock.objects.filter(        product_id=product_id,        warehouse_id=warehouse_id    ).annotate(        needs_reorder=Case(            When(quantity__lt=F('product__reorder_level'), then=Value(True)),            default=Value(False),            output_field=BooleanField()        )    ).first()        if stock and stock.needs_reorder:        # Trigger reorder process        create_reorder_request(product_id, warehouse_id) def create_reorder_request(product_id, warehouse_id):    """Create reorder request (implement as needed)"""    pass

Hints:

Use aggregate() for totals across all
Use annotate() for per-object calculations
Use Exists() for efficient checks
Use Case/When for conditional flags
Use F() for atomic updates

Congratulations! 🎉

You've completed Bài 3: Advanced QuerySets và Database Optimization (all 4 parts)!

What You've Mastered:

QuerySet fundamentals and lazy loading
select_related() and prefetch_related()
N+1 problem detection and solutions
Aggregation, annotation, F/Q objects
Conditional expressions and subqueries
Window functions and performance optimization

Total Content: ~10,800 words across 4 comprehensive parts!

Next Steps

Trong Bài 4: Giới Thiệu Django REST Framework, bạn sẽ học:

REST principles và best practices
DRF installation và configuration
APIView basics
Serializers fundamentals
Request/Response objects
Status codes và format suffixes
Browsable API

Navigation:

← Bài 3.3: N+1 Problem và Solutions
Bài 3.4: Aggregation, Annotation & F/Q Objects (current)
Bài 4: Giới Thiệu Django REST Framework →

#subquery

Advanced QuerySets và Database Optimization - Phần 4 - Aggregation & Annotation