# Lazy -- no SQL executed yet
qs = (
    Article.objects
    .filter(status="published")
    .exclude(category__name="draft")
    .order_by("-published_at")
)

# SQL executes when you iterate, slice, call list(), len(), bool(), etc.
articles = list(qs[:10])

# Efficient existence check -- use .exists() not len() or bool()
if Article.objects.filter(slug=slug).exists():
    raise ValidationError("Slug already taken")

# Efficient count -- use .count() not len(qs)
total = Article.objects.filter(status="published").count()

# Get or 404
from django.shortcuts import get_object_or_404
article = get_object_or_404(Article, slug=slug, status="published")

# WITHOUT select_related -- N+1 problem
articles = Article.objects.all()
for article in articles:
    print(article.author.name)  # Each access hits the database

# WITH select_related -- single JOIN query
articles = Article.objects.select_related("author", "category").all()
for article in articles:
    print(article.author.name)  # No extra query -- already loaded

# Chain through relationships
comments = (
    Comment.objects
    .select_related("article__author", "article__category")
    .filter(is_approved=True)
)

from django.db.models import Prefetch

# Basic prefetch -- separate query for tags
articles = Article.objects.prefetch_related("tags").all()

# Custom Prefetch with filtering
articles = Article.objects.prefetch_related(
    Prefetch(
        "comments",
        queryset=Comment.objects.filter(is_approved=True).select_related("author"),
        to_attr="approved_comments",  # Access as article.approved_comments
    )
)

for article in articles:
    for comment in article.approved_comments:  # No extra queries
        print(f"{comment.author.name}: {comment.text}")

from django.db.models import F

# Increment without race condition
Article.objects.filter(id=article_id).update(view_count=F("view_count") + 1)

# Compare fields
products = Product.objects.filter(stock__lt=F("reorder_level"))

# Arithmetic between fields
orders = Order.objects.annotate(
    profit=F("revenue") - F("cost")
).filter(profit__gt=0)

# Reference related fields
articles = Article.objects.filter(
    comments_count__gt=F("category__avg_comments") * 2
)

from django.db.models import Q

# OR condition
results = Article.objects.filter(
    Q(status="published") | Q(author=current_user)
)

# AND with OR
results = Article.objects.filter(
    Q(status="published") & (Q(category="tech") | Q(category="science"))
)

# Negation
results = Article.objects.filter(~Q(status="archived"))

# Dynamic query building
filters = Q()
if search_term:
    filters &= Q(title__icontains=search_term) | Q(body__icontains=search_term)
if category:
    filters &= Q(category__slug=category)
if date_from:
    filters &= Q(published_at__gte=date_from)

articles = Article.objects.filter(filters)

from django.db.models import Count, Avg, Sum, Max, Min, Value, CharField
from django.db.models.functions import Coalesce, Upper, TruncMonth

# Aggregate -- returns a dict
stats = Order.objects.aggregate(
    total_revenue=Sum("amount"),
    avg_order=Avg("amount"),
    order_count=Count("id"),
    max_order=Max("amount"),
)

# Annotate -- adds computed fields to each object in QuerySet
authors = (
    User.objects
    .annotate(
        article_count=Count("articles"),
        avg_rating=Avg("articles__rating"),
        total_views=Sum("articles__view_count"),
    )
    .filter(article_count__gt=5)
    .order_by("-total_views")
)

# Conditional aggregation
from django.db.models import Case, When, IntegerField

articles = Article.objects.annotate(
    engagement_score=Case(
        When(view_count__gt=1000, then=Value(3)),
        When(view_count__gt=100, then=Value(2)),
        default=Value(1),
        output_field=IntegerField(),
    )
)

# Group by month
monthly_revenue = (
    Order.objects
    .annotate(month=TruncMonth("created_at"))
    .values("month")
    .annotate(
        revenue=Sum("amount"),
        count=Count("id"),
    )
    .order_by("month")
)

# Safe raw query with parameters
users = User.objects.raw(
    "SELECT * FROM auth_user WHERE email LIKE %s AND is_active = %s",
    [f"%@{domain}", True],
)

# Raw SQL for complex queries that don't map to a model
from django.db import connection

def get_revenue_report(year: int) -> list[dict]:
    with connection.cursor() as cursor:
        cursor.execute(
            """
            SELECT category, SUM(amount) as total, COUNT(*) as count
            FROM orders_order
            WHERE EXTRACT(YEAR FROM created_at) = %s
            GROUP BY category
            ORDER BY total DESC
            """,
            [year],
        )
        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]