Advanced Django Queries: Aggregation, Annotation, and Q Objects

# Django Aggregation
from django.db.models import Count, Sum, Avg, Max, Min, F, Q
from .models import Post, Author, Comment

# Basic aggregation
def basic_aggregation():
    # Count all posts
    total_posts = Post.objects.count()
    
    # Using aggregate
    from django.db.models import Count
    result = Post.objects.aggregate(Count('id'))
    # Returns: {'id__count': 100}
    
    # Multiple aggregations
    stats = Post.objects.aggregate(
        total=Count('id'),
        avg_views=Avg('views'),
        max_views=Max('views'),
        min_views=Min('views'),
        total_views=Sum('views')
    )
    # Returns: {'total': 100, 'avg_views': 250.5, ...}
    
    return stats

# Aggregation with filtering
def filtered_aggregation():
    # Average views for published posts only
    avg_views = Post.objects.filter(
        status='published'
    ).aggregate(avg=Avg('views'))['avg']
    
    # Sum of views for specific author
    total_views = Post.objects.filter(
        author__username='john'
    ).aggregate(total=Sum('views'))['total']
    
    return avg_views, total_views

# Group by with aggregation (annotate)
def group_by_aggregation():
    # Count posts per author
    authors = Author.objects.annotate(
        post_count=Count('posts')
    )
    
    for author in authors:
        print(f"{author.username}: {author.post_count} posts")
    
    # Filter annotated results
    active_authors = Author.objects.annotate(
        post_count=Count('posts')
    ).filter(post_count__gt=5)
    
    return authors

# Complex aggregation
def complex_aggregation():
    # Posts with comment statistics
    posts = Post.objects.annotate(
        comment_count=Count('comments'),
        avg_rating=Avg('comments__rating')
    ).filter(comment_count__gt=0)
    
    # Author statistics
    author_stats = Author.objects.annotate(
        post_count=Count('posts'),
        total_views=Sum('posts__views'),
        avg_views=Avg('posts__views'),
        latest_post=Max('posts__published_date')
    )
    
    return posts, author_stats

# Django Annotation
from django.db.models import Count, F, Value, CharField, Case, When, Q
from django.db.models.functions import Concat, Lower, Upper, Length

# Basic annotation
def basic_annotation():
    # Add computed field to each post
    posts = Post.objects.annotate(
        comment_count=Count('comments')
    )
    
    for post in posts:
        print(f"{post.title}: {post.comment_count} comments")
    
    return posts

# F expressions (field references)
def f_expressions():
    # Posts where views > likes * 2
    popular_posts = Post.objects.filter(views__gt=F('likes') * 2)
    
    # Add computed field using F expressions
    posts = Post.objects.annotate(
        engagement=F('likes') + F('comments_count')
    ).order_by('-engagement')
    
    # Update using F expressions (avoids race conditions)
    Post.objects.filter(pk=1).update(views=F('views') + 1)
    
    return posts

# String operations
def string_annotation():
    # Concatenate fields
    authors = Author.objects.annotate(
        full_name=Concat('first_name', Value(' '), 'last_name')
    )
    
    # String functions
    posts = Post.objects.annotate(
        title_lower=Lower('title'),
        title_upper=Upper('title'),
        title_length=Length('title')
    )
    
    return authors, posts

# Conditional annotation (Case/When)
def conditional_annotation():
    # Categorize posts by view count
    posts = Post.objects.annotate(
        popularity=Case(
            When(views__gte=1000, then=Value('viral')),
            When(views__gte=100, then=Value('popular')),
            When(views__gte=10, then=Value('normal')),
            default=Value('unpopular'),
            output_field=CharField()
        )
    )
    
    # Conditional counting
    authors = Author.objects.annotate(
        published_count=Count('posts', filter=Q(posts__status='published')),
        draft_count=Count('posts', filter=Q(posts__status='draft'))
    )
    
    return posts, authors

# Subquery annotation
from django.db.models import Subquery, OuterRef

def subquery_annotation():
    # Latest comment for each post
    latest_comment = Comment.objects.filter(
        post=OuterRef('pk')
    ).order_by('-created_at')
    
    posts = Post.objects.annotate(
        latest_comment_text=Subquery(latest_comment.values('text')[:1])
    )
    
    return posts

# Django Q Objects
from django.db.models import Q
from .models import Post

# OR queries
def or_queries():
    # Posts by specific author OR with high views
    posts = Post.objects.filter(
        Q(author__username='john') | Q(views__gt=1000)
    )
    
    # Multiple OR conditions
    posts = Post.objects.filter(
        Q(status='published') | 
        Q(status='featured') | 
        Q(status='pinned')
    )
    
    return posts

# AND queries (combine with filter)
def and_queries():
    # Published posts with high views
    posts = Post.objects.filter(
        Q(status='published') & Q(views__gt=100)
    )
    
    # Same as regular filter
    posts = Post.objects.filter(status='published', views__gt=100)
    
    return posts

# NOT queries (negation)
def not_queries():
    # Posts NOT in draft status
    posts = Post.objects.filter(~Q(status='draft'))
    
    # Posts not by specific author
    posts = Post.objects.filter(~Q(author__username='john'))
    
    # Complex negation
    posts = Post.objects.filter(
        ~Q(status='draft') & ~Q(views__lt=10)
    )
    
    return posts

# Complex combinations
def complex_q_queries():
    # (published OR featured) AND (views > 100 OR likes > 50)
    posts = Post.objects.filter(
        (Q(status='published') | Q(status='featured')) &
        (Q(views__gt=100) | Q(likes__gt=50))
    )
    
    # Posts matching search terms
    search_query = 'django python'
    query = Q()
    for term in search_query.split():
        query |= Q(title__icontains=term) | Q(content__icontains=term)
    
    posts = Post.objects.filter(query)
    
    return posts

# Dynamic query building
def dynamic_query_building(filters):
    query = Q()
    
    # Build query dynamically
    if filters.get('author'):
        query &= Q(author__username=filters['author'])
    
    if filters.get('status'):
        query &= Q(status=filters['status'])
    
    if filters.get('min_views'):
        query &= Q(views__gte=filters['min_views'])
    
    if filters.get('search'):
        search_terms = filters['search'].split()
        search_query = Q()
        for term in search_terms:
            search_query |= Q(title__icontains=term)
        query &= search_query
    
    posts = Post.objects.filter(query)
    return posts

# Advanced search example
def advanced_search(request):
    query = Q()
    
    # Title search
    title = request.GET.get('title')
    if title:
        query &= Q(title__icontains=title)
    
    # Author filter
    author = request.GET.get('author')
    if author:
        query &= Q(author__username__icontains=author)
    
    # Date range
    from_date = request.GET.get('from')
    to_date = request.GET.get('to')
    if from_date:
        query &= Q(published_date__gte=from_date)
    if to_date:
        query &= Q(published_date__lte=to_date)
    
    # Category (multiple)
    categories = request.GET.getlist('category')
    if categories:
        category_query = Q()
        for cat in categories:
            category_query |= Q(category__name=cat)
        query &= category_query
    
    posts = Post.objects.filter(query).distinct()
    return posts

# Query Optimization
from django.db.models import Prefetch

# select_related (SQL JOIN for ForeignKey)
def select_related_optimization():
    # Bad: N+1 queries
    posts = Post.objects.all()
    for post in posts:
        print(post.author.username)  # Hits database each time
    
    # Good: Single query with JOIN
    posts = Post.objects.select_related('author').all()
    for post in posts:
        print(post.author.username)  # No additional queries
    
    # Multiple relations
    posts = Post.objects.select_related('author', 'category')
    
    return posts

# prefetch_related (separate queries for reverse/M2M)
def prefetch_related_optimization():
    # Bad: N+1 queries
    authors = Author.objects.all()
    for author in authors:
        for post in author.posts.all():  # Query per author
            print(post.title)
    
    # Good: Two queries total
    authors = Author.objects.prefetch_related('posts').all()
    for author in authors:
        for post in author.posts.all():  # Uses cached data
            print(post.title)
    
    return authors

# Custom prefetch
def custom_prefetch():
    # Prefetch only published posts
    published_posts = Prefetch(
        'posts',
        queryset=Post.objects.filter(status='published')
    )
    
    authors = Author.objects.prefetch_related(published_posts)
    
    return authors

# Field selection optimization
def field_selection():
    # Only specific fields
    posts = Post.objects.only('title', 'author')
    
    # Defer large fields
    posts = Post.objects.defer('content')  # Skip content field
    
    # Values (returns dicts, not objects)
    post_data = Post.objects.values('id', 'title', 'views')
    
    # Values list (returns tuples)
    post_ids = Post.objects.values_list('id', flat=True)
    
    return posts