Aggregation and Queries

🔮 Django Database Magic: Aggregation & Queries

Imagine you have a giant toy box with thousands of LEGO pieces. Instead of counting each piece one by one, what if you had a magic wand that could instantly tell you “You have 500 red pieces” or “Your biggest piece is 8 studs long”? That’s what Django aggregation does for your database!

🎯 The Big Picture

Think of your database as a huge library with millions of books. Django gives you special tools to:

• 📊 Count all books instantly
• 💰 Sum up all prices
• 📏 Find the longest or shortest book
• 🔍 Filter with complex conditions

Let’s explore each magic spell!

🔤 Q Expressions: The “AND/OR” Magic

What Are Q Expressions?

Imagine you’re looking for a friend at school. You might say:

• “Find someone who is tall AND has glasses”
• “Find someone who is tall OR plays soccer”

Q expressions let you combine conditions with AND (&), OR (|), and NOT (~).

Simple Example

from django.db.models import Q

# Find books that are cheap OR popular
Book.objects.filter(
    Q(price__lt=10) | Q(rating__gte=4)
)

# Find books NOT written by "Unknown"
Book.objects.filter(~Q(author="Unknown"))

# Complex: (cheap AND new) OR bestseller
Book.objects.filter(
    (Q(price__lt=10) & Q(year=2024)) |
    Q(is_bestseller=True)
)

Why Use Q?

Regular filter() only does AND. Q lets you do OR and NOT!

graph TD
    A["Your Query"] --> B{Need OR/NOT?}
    B -->|Yes| C["Use Q expressions"]
    B -->|No| D["Regular filter works"]
    C --> E["Combine with & | ~"]

📐 F Expressions: Database-Side Math

What Are F Expressions?

Imagine telling your piggy bank: “Add $5 to whatever is inside” without opening it to count first.

F expressions let the database do math without loading data into Python.

Simple Example

from django.db.models import F

# Give everyone a $5 raise
Employee.objects.update(salary=F('salary') + 5)

# Find products where stock < minimum
Product.objects.filter(
    stock__lt=F('minimum_stock')
)

# Compare two fields
Order.objects.filter(
    shipped_qty__lt=F('ordered_qty')
)

Why F is Faster

graph TD
    A["Without F"] --> B["Load all data"]
    B --> C["Change in Python"]
    C --> D["Send back to DB"]
    E["With F"] --> F["DB does math directly"]
    F --> G["✨ Much faster!"]

🧮 Aggregation Functions: The Calculators

Meet the Family

Simple Examples

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

# Single calculations
Book.objects.aggregate(
    total_books=Count('id'),
    total_value=Sum('price'),
    avg_rating=Avg('rating'),
    highest_price=Max('price'),
    lowest_price=Min('price')
)
# Returns: {'total_books': 100,
#           'total_value': 2500, ...}

📊 Aggregation Methods: aggregate() vs annotate()

The Difference is Simple!

• aggregate() = One answer for the whole table
• annotate() = One answer for each row

Picture This

aggregate() is like asking: “What’s the total of ALL my allowance?” annotate() is like asking: “How much did I save EACH month?”

Examples

# aggregate: ONE total result
Book.objects.aggregate(
    total=Sum('price')
)
# Returns: {'total': 2500}

# annotate: result for EACH author
Author.objects.annotate(
    book_count=Count('books')
)
# Returns: Each author with their count
# [Author1: 5 books, Author2: 3 books...]

graph TD
    A["Choose Method"] --> B{One total or per-row?}
    B -->|One total| C["aggregate"]
    B -->|Per row| D["annotate"]
    C --> E["Returns dict"]
    D --> F["Returns QuerySet"]

✂️ QuerySet Slicing & Limiting

Like Cutting a Pizza

You don’t always want the whole pizza. Sometimes you want:

• Just the first 3 slices
• Slices 5 through 10
• Every other slice

Syntax is Easy

# First 5 books
Book.objects.all()[:5]

# Books 10 to 20
Book.objects.all()[10:20]

# Just the 5th book (single item)
Book.objects.all()[4]

# First 10, sorted by rating
Book.objects.order_by('-rating')[:10]

⚠️ Important Rules

1. No negative indexes: [-1] doesn’t work
2. Slicing returns a new QuerySet (lazy!)
3. Single index [4] hits the database immediately

🎭 Conditional Expressions: When/Case

Like a Traffic Light

A traffic light says:

• When green → GO
• When yellow → SLOW
• When red → STOP

Django’s Case/When does the same for data!

Simple Example

from django.db.models import Case, When, Value

Book.objects.annotate(
    price_tier=Case(
        When(price__lt=10, then=Value('cheap')),
        When(price__lt=50, then=Value('medium')),
        default=Value('expensive')
    )
)

Another Example: Conditional Counting

from django.db.models import Count, Q

# Count active vs inactive users
User.objects.aggregate(
    active=Count('id', filter=Q(is_active=True)),
    inactive=Count('id', filter=Q(is_active=False))
)

graph TD
    A["Data"] --> B{Check condition}
    B -->|price < 10| C["cheap"]
    B -->|price < 50| D["medium"]
    B -->|else| E["expensive"]

🔧 Database Functions: Built-in Helpers

Common Functions

Django provides ready-made functions for common tasks:

Examples

from django.db.models.functions import (
    Lower, Upper, Length, Concat, Coalesce
)
from django.db.models import Value

# Search case-insensitive
Book.objects.annotate(
    lower_title=Lower('title')
).filter(lower_title__contains='python')

# Full name from parts
Author.objects.annotate(
    full_name=Concat(
        'first_name', Value(' '), 'last_name'
    )
)

# Handle null values
Book.objects.annotate(
    display_name=Coalesce(
        'nickname', 'title'
    )
)

Math Functions

from django.db.models.functions import (
    Round, Abs, Ceil, Floor
)

Product.objects.annotate(
    rounded_price=Round('price', 2),
    absolute_diff=Abs(F('price') - F('cost'))
)

🎮 Putting It All Together

Let’s build a real query combining everything:

from django.db.models import (
    Q, F, Count, Sum, Avg, Case, When, Value
)
from django.db.models.functions import Lower

# Find best-selling authors in 2024
Author.objects.filter(
    Q(books__year=2024) &
    ~Q(books__status='draft')
).annotate(
    total_sales=Sum('books__sales'),
    book_count=Count('books'),
    avg_rating=Avg('books__rating'),
    status=Case(
        When(total_sales__gte=10000,
             then=Value('star')),
        default=Value('regular')
    )
).filter(
    total_sales__gt=F('target_sales')
).order_by('-total_sales')[:10]

🏆 Quick Reference

🌟 You Did It!

You now understand Django’s powerful database tools:

✅ Q expressions for complex AND/OR/NOT filters ✅ F expressions for database-side calculations ✅ Aggregation functions (Count, Sum, Avg, Max, Min) ✅ aggregate() vs annotate() - totals vs per-row ✅ QuerySet slicing for limiting results ✅ Conditional expressions for if-then logic ✅ Database functions for text, math, and more

Remember: These tools make your database do the heavy lifting, keeping your app fast and efficient! 🚀