Advanced ORM Patterns

# Advanced ORM Patterns

# 1. Custom Model Managers
from django.db import models
from django.db.models import Q, Count, Avg, Sum, Max, Min
from django.db.models.functions import Coalesce, TruncDate, ExtractYear

class PostManager(models.Manager):
    """Custom manager for Post model"""
    
    def published(self):
        """Return only published posts"""
        return self.filter(status='published')
    
    def by_author(self, author):
        """Return posts by specific author"""
        return self.filter(author=author)
    
    def by_category(self, category):
        """Return posts by category"""
        return self.filter(category=category)
    
    def with_comments(self):
        """Return posts with comment count"""
        return self.annotate(
            comment_count=Count('comments'),
            avg_rating=Avg('comments__rating')
        )
    
    def trending(self, days=7):
        """Return trending posts based on views and likes"""
        from django.utils import timezone
        from datetime import timedelta
        
        cutoff_date = timezone.now() - timedelta(days=days)
        
        return self.filter(
            created_date__gte=cutoff_date
        ).annotate(
            engagement_score=(
                Count('views') * 1 + 
                Count('likes') * 3 + 
                Count('comments') * 2
            )
        ).order_by('-engagement_score')
    
    def search(self, query):
        """Search posts by title and content"""
        return self.filter(
            Q(title__icontains=query) |
            Q(content__icontains=query) |
            Q(tags__name__icontains=query)
        ).distinct()
    
    def get_popular_authors(self, limit=10):
        """Get most popular authors by post count and engagement"""
        return self.values('author').annotate(
            post_count=Count('id'),
            total_views=Sum('views'),
            total_likes=Sum('likes'),
            total_comments=Count('comments')
        ).order_by('-post_count', '-total_views')[:limit]

class Post(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE)
    category = models.ForeignKey(Category, on_delete=models.CASCADE)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='draft')
    created_date = models.DateTimeField(auto_now_add=True)
    published_date = models.DateTimeField(null=True, blank=True)
    views = models.PositiveIntegerField(default=0)
    likes = models.PositiveIntegerField(default=0)
    
    objects = PostManager()
    
    class Meta:
        indexes = [
            models.Index(fields=['status', 'published_date']),
            models.Index(fields=['author', 'created_date']),
            models.Index(fields=['category', 'status']),
        ]

# 2. Custom QuerySet Methods
class PostQuerySet(models.QuerySet):
    """Custom QuerySet for Post model"""
    
    def active(self):
        """Return only active posts"""
        return self.filter(status='published', is_active=True)
    
    def recent(self, days=30):
        """Return recent posts"""
        from django.utils import timezone
        from datetime import timedelta
        
        cutoff_date = timezone.now() - timedelta(days=days)
        return self.filter(created_date__gte=cutoff_date)
    
    def featured(self):
        """Return featured posts"""
        return self.filter(is_featured=True, status='published')
    
    def by_date_range(self, start_date, end_date):
        """Return posts within date range"""
        return self.filter(
            created_date__range=[start_date, end_date]
        )
    
    def with_related_data(self):
        """Optimize queries with related data"""
        return self.select_related('author', 'category').prefetch_related(
            'tags',
            'comments__author',
            'likes__user'
        )
    
    def aggregate_stats(self):
        """Get aggregated statistics"""
        return self.aggregate(
            total_posts=Count('id'),
            total_views=Sum('views'),
            total_likes=Sum('likes'),
            avg_views=Avg('views'),
            avg_likes=Avg('likes')
        )
    
    def group_by_month(self):
        """Group posts by month"""
        return self.annotate(
            month=TruncDate('created_date', 'month')
        ).values('month').annotate(
            count=Count('id'),
            total_views=Sum('views')
        ).order_by('month')

# Use the custom QuerySet
Post.objects = PostQuerySet.as_manager()

# 3. Advanced Query Patterns
class AdvancedQueryExamples:
    """Examples of advanced query patterns"""
    
    @staticmethod
    def complex_search(query, filters=None):
        """Complex search with multiple filters"""
        queryset = Post.objects.all()
        
        # Text search
        if query:
            queryset = queryset.filter(
                Q(title__icontains=query) |
                Q(content__icontains=query) |
                Q(author__username__icontains=query) |
                Q(category__name__icontains=query)
            )
        
        # Apply filters
        if filters:
            if filters.get('category'):
                queryset = queryset.filter(category_id=filters['category'])
            
            if filters.get('author'):
                queryset = queryset.filter(author_id=filters['author'])
            
            if filters.get('date_from'):
                queryset = queryset.filter(created_date__gte=filters['date_from'])
            
            if filters.get('date_to'):
                queryset = queryset.filter(created_date__lte=filters['date_to'])
            
            if filters.get('min_views'):
                queryset = queryset.filter(views__gte=filters['min_views'])
        
        return queryset.distinct()
    
    @staticmethod
    def get_analytics_data(start_date, end_date):
        """Get analytics data for date range"""
        return Post.objects.filter(
            created_date__range=[start_date, end_date]
        ).aggregate(
            total_posts=Count('id'),
            total_views=Sum('views'),
            total_likes=Sum('likes'),
            total_comments=Count('comments'),
            avg_views_per_post=Avg('views'),
            avg_likes_per_post=Avg('likes'),
            posts_per_day=Count('id') / (end_date - start_date).days
        )
    
    @staticmethod
    def get_user_engagement(user_id):
        """Get user engagement metrics"""
        return Post.objects.filter(author_id=user_id).aggregate(
            total_posts=Count('id'),
            total_views=Sum('views'),
            total_likes=Sum('likes'),
            total_comments=Count('comments'),
            avg_views_per_post=Avg('views'),
            engagement_rate=(
                (Sum('likes') + Sum('comments')) / Count('id')
            )
        )

# 4. Database Functions and Expressions
from django.db.models import F, Func, Value, ExpressionWrapper
from django.db.models.functions import Concat, Lower, Upper, Length

class DatabaseFunctions:
    """Examples of database functions and expressions"""
    
    @staticmethod
    def update_view_count(post_id):
        """Update view count using F() expression"""
        Post.objects.filter(id=post_id).update(views=F('views') + 1)
    
    @staticmethod
    def get_posts_with_full_name():
        """Get posts with concatenated author full name"""
        return Post.objects.annotate(
            author_full_name=Concat(
                'author__first_name',
                Value(' '),
                'author__last_name'
            )
        )
    
    @staticmethod
    def get_posts_by_title_length():
        """Get posts grouped by title length"""
        return Post.objects.annotate(
            title_length=Length('title')
        ).values('title_length').annotate(
            count=Count('id')
        ).order_by('title_length')
    
    @staticmethod
    def get_posts_with_engagement_score():
        """Calculate engagement score for posts"""
        return Post.objects.annotate(
            engagement_score=ExpressionWrapper(
                F('views') * 1 + F('likes') * 3 + F('comments__count') * 2,
                output_field=models.IntegerField()
            )
        ).order_by('-engagement_score')

# 5. Raw SQL and Complex Queries
from django.db import connection
from django.db.models import RawSQL

class RawSQLExamples:
    """Examples of raw SQL usage"""
    
    @staticmethod
    def get_posts_with_complex_stats():
        """Get posts with complex statistics using raw SQL"""
        return Post.objects.annotate(
            comment_count=RawSQL("""
                SELECT COUNT(*) FROM blog_comment 
                WHERE blog_comment.post_id = blog_post.id
            """, []),
            avg_rating=RawSQL("""
                SELECT AVG(rating) FROM blog_comment 
                WHERE blog_comment.post_id = blog_post.id
            """, []),
            last_comment_date=RawSQL("""
                SELECT MAX(created_date) FROM blog_comment 
                WHERE blog_comment.post_id = blog_post.id
            """, [])
        )
    
    @staticmethod
    def get_trending_posts_window_function():
        """Get trending posts using window functions (PostgreSQL)"""
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT 
                    p.id,
                    p.title,
                    p.views,
                    p.likes,
                    ROW_NUMBER() OVER (
                        ORDER BY (p.views + p.likes * 3) DESC
                    ) as rank
                FROM blog_post p
                WHERE p.status = 'published'
                ORDER BY rank
                LIMIT 10
            """)
            return cursor.fetchall()
    
    @staticmethod
    def get_posts_by_time_period():
        """Get posts grouped by time periods"""
        return Post.objects.raw("""
            SELECT 
                id,
                title,
                created_date,
                CASE 
                    WHEN created_date >= NOW() - INTERVAL '1 day' THEN 'Today'
                    WHEN created_date >= NOW() - INTERVAL '7 days' THEN 'This Week'
                    WHEN created_date >= NOW() - INTERVAL '30 days' THEN 'This Month'
                    ELSE 'Older'
                END as time_period
            FROM blog_post
            ORDER BY created_date DESC
        """)

# 6. Bulk Operations and Performance
class BulkOperations:
    """Examples of bulk operations for performance"""
    
    @staticmethod
    def bulk_create_posts(posts_data):
        """Bulk create posts"""
        posts = [
            Post(**data) for data in posts_data
        ]
        return Post.objects.bulk_create(posts, batch_size=100)
    
    @staticmethod
    def bulk_update_posts(posts, fields):
        """Bulk update posts"""
        return Post.objects.bulk_update(posts, fields, batch_size=100)
    
    @staticmethod
    def bulk_delete_posts(post_ids):
        """Bulk delete posts"""
        return Post.objects.filter(id__in=post_ids).delete()
    
    @staticmethod
    def update_multiple_posts(updates):
        """Update multiple posts with different values"""
        for post_id, update_data in updates.items():
            Post.objects.filter(id=post_id).update(**update_data)

# 7. Query Optimization Techniques
class QueryOptimization:
    """Query optimization techniques"""
    
    @staticmethod
    def optimize_post_list():
        """Optimize post list query"""
        return Post.objects.select_related(
            'author', 'category'
        ).prefetch_related(
            'tags',
            Prefetch(
                'comments',
                queryset=Comment.objects.select_related('author')[:5]
            )
        ).filter(status='published').order_by('-created_date')
    
    @staticmethod
    def optimize_user_posts(user_id):
        """Optimize user posts query"""
        return Post.objects.filter(author_id=user_id).select_related(
            'category'
        ).prefetch_related(
            'tags',
            'comments'
        ).annotate(
            total_views=Sum('views'),
            total_likes=Sum('likes')
        ).order_by('-created_date')
    
    @staticmethod
    def optimize_category_posts(category_id):
        """Optimize category posts query"""
        return Post.objects.filter(
            category_id=category_id,
            status='published'
        ).select_related('author').prefetch_related(
            'tags'
        ).annotate(
            comment_count=Count('comments')
        ).order_by('-created_date')

# Usage Examples
# Get trending posts
trending_posts = Post.objects.trending(days=7)

# Search posts
search_results = Post.objects.search('Django tutorial')

# Get analytics
analytics = AdvancedQueryExamples.get_analytics_data(
    start_date=timezone.now() - timedelta(days=30),
    end_date=timezone.now()
)

# Optimize queries
optimized_posts = QueryOptimization.optimize_post_list()

# Use raw SQL
complex_stats = RawSQLExamples.get_posts_with_complex_stats()