# Basic retrieval operations
from myapp.models import Post, Author, Category

# Get all objects
all_posts = Post.objects.all()

# Get a single object (raises DoesNotExist if not found)
post = Post.objects.get(id=1)
post = Post.objects.get(slug='my-first-post')

# Get first object or None
first_post = Post.objects.first()
last_post = Post.objects.last()

# Get or create an object
author, created = Author.objects.get_or_create(
    email='john@example.com',
    defaults={'name': 'John Doe', 'bio': 'Writer and developer'}
)

# Update or create
author, created = Author.objects.update_or_create(
    email='john@example.com',
    defaults={'name': 'John Smith', 'bio': 'Updated bio'}
)

# Check if objects exist
has_posts = Post.objects.exists()
has_published_posts = Post.objects.filter(status='published').exists()

# Count objects
total_posts = Post.objects.count()
published_count = Post.objects.filter(status='published').count()

# Creating objects
from django.utils import timezone

# Method 1: Create and save
post = Post()
post.title = 'My New Post'
post.content = 'This is the content of my post.'
post.author_id = 1
post.save()

# Method 2: Create in one step
post = Post.objects.create(
    title='Another Post',
    content='More content here.',
    author_id=1,
    status='published',
    published_at=timezone.now()
)

# Bulk create (more efficient for multiple objects)
posts = [
    Post(title=f'Post {i}', content=f'Content {i}', author_id=1)
    for i in range(1, 101)
]
Post.objects.bulk_create(posts)

# Updating objects
post = Post.objects.get(id=1)
post.title = 'Updated Title'
post.save()

# Update specific fields only
post.save(update_fields=['title', 'updated_at'])

# Bulk update
Post.objects.filter(status='draft').update(
    status='published',
    published_at=timezone.now()
)

# Update or create with bulk operations
Post.objects.bulk_update(
    posts,
    ['title', 'content'],
    batch_size=100
)

# Delete a single object
post = Post.objects.get(id=1)
post.delete()

# Bulk delete
Post.objects.filter(status='draft').delete()

# Delete all objects (be careful!)
Post.objects.all().delete()

# Soft delete pattern
class SoftDeleteManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(is_deleted=False)

class Post(models.Model):
    title = models.CharField(max_length=200)
    is_deleted = models.BooleanField(default=False)
    deleted_at = models.DateTimeField(null=True, blank=True)
    
    objects = SoftDeleteManager()
    all_objects = models.Manager()  # Include deleted objects
    
    def soft_delete(self):
        self.is_deleted = True
        self.deleted_at = timezone.now()
        self.save()
    
    def restore(self):
        self.is_deleted = False
        self.deleted_at = None
        self.save()

from django.db.models import Q
from datetime import datetime, timedelta

# OR conditions
posts = Post.objects.filter(
    Q(status='published') | Q(status='featured')
)

# AND conditions (default behavior)
posts = Post.objects.filter(
    Q(status='published') & Q(author__name='John Doe')
)

# NOT conditions
posts = Post.objects.filter(
    ~Q(status='draft')
)

# Complex combinations
recent_date = timezone.now() - timedelta(days=30)
posts = Post.objects.filter(
    (Q(status='published') | Q(status='featured')) &
    Q(created_at__gte=recent_date) &
    ~Q(author__email__endswith='@spam.com')
)

# Dynamic query building
def search_posts(title=None, author=None, category=None, status=None):
    query = Q()
    
    if title:
        query &= Q(title__icontains=title)
    
    if author:
        query &= Q(author__name__icontains=author)
    
    if category:
        query &= Q(category__slug=category)
    
    if status:
        query &= Q(status=status)
    
    return Post.objects.filter(query)

# Usage
results = search_posts(title='django', author='john', status='published')

# Exact matches
posts = Post.objects.filter(status='published')
posts = Post.objects.filter(status__exact='published')  # Same as above

# Case-insensitive exact match
posts = Post.objects.filter(title__iexact='Django Tutorial')

# Contains (case-sensitive)
posts = Post.objects.filter(title__contains='Django')

# Case-insensitive contains
posts = Post.objects.filter(title__icontains='django')

# Starts with / ends with
posts = Post.objects.filter(title__startswith='How to')
posts = Post.objects.filter(title__endswith='Tutorial')
posts = Post.objects.filter(title__istartswith='how to')  # Case-insensitive

# Null checks
posts = Post.objects.filter(published_at__isnull=True)
posts = Post.objects.filter(published_at__isnull=False)

# In a list
post_ids = [1, 2, 3, 4, 5]
posts = Post.objects.filter(id__in=post_ids)

categories = ['tech', 'programming', 'web-development']
posts = Post.objects.filter(category__slug__in=categories)

# Range queries
from datetime import date
start_date = date(2023, 1, 1)
end_date = date(2023, 12, 31)
posts = Post.objects.filter(created_at__range=(start_date, end_date))

# Greater than / less than
posts = Post.objects.filter(view_count__gt=1000)
posts = Post.objects.filter(view_count__gte=1000)  # Greater than or equal
posts = Post.objects.filter(view_count__lt=100)
posts = Post.objects.filter(view_count__lte=100)   # Less than or equal

# Date/time lookups
posts = Post.objects.filter(created_at__year=2023)
posts = Post.objects.filter(created_at__month=12)
posts = Post.objects.filter(created_at__day=25)
posts = Post.objects.filter(created_at__week_day=1)  # Sunday = 1
posts = Post.objects.filter(created_at__hour=14)

# Date comparisons
today = timezone.now().date()
posts = Post.objects.filter(created_at__date=today)
posts = Post.objects.filter(created_at__date__gte=today)

# Regular expressions (PostgreSQL, MySQL, SQLite)
posts = Post.objects.filter(title__regex=r'^(How|What|Why)')
posts = Post.objects.filter(title__iregex=r'^(how|what|why)')  # Case-insensitive

# Forward relationships (following foreign keys)
# Get posts by a specific author
author_posts = Post.objects.filter(author__name='John Doe')
author_posts = Post.objects.filter(author__email='john@example.com')

# Multiple relationship hops
posts = Post.objects.filter(author__profile__city='New York')

# Reverse relationships (following related_name)
# Get authors who have published posts
authors_with_posts = Author.objects.filter(posts__status='published')

# Get categories that have posts
categories_with_posts = Category.objects.filter(posts__isnull=False)

# Many-to-many relationships
# Posts with specific tags
posts_with_django_tag = Post.objects.filter(tags__name='Django')

# Posts with multiple tags (AND condition)
posts = Post.objects.filter(tags__name='Django').filter(tags__name='Python')

# Posts with any of multiple tags (OR condition)
posts = Post.objects.filter(tags__name__in=['Django', 'Python', 'Web'])

# Exclude posts with certain tags
posts = Post.objects.exclude(tags__name='Deprecated')

# Complex relationship queries
# Authors who have posts in multiple categories
authors = Author.objects.filter(
    posts__category__name='Technology'
).filter(
    posts__category__name='Programming'
).distinct()

# Posts by authors from specific cities with high view counts
posts = Post.objects.filter(
    author__profile__city='San Francisco',
    view_count__gte=1000,
    status='published'
)

# Without optimization (N+1 query problem)
posts = Post.objects.all()
for post in posts:
    print(post.author.name)  # Each iteration hits the database

# With select_related (for foreign keys and one-to-one)
posts = Post.objects.select_related('author', 'category').all()
for post in posts:
    print(post.author.name)  # No additional queries

# Multiple levels of relationships
posts = Post.objects.select_related(
    'author',
    'author__profile',
    'category',
    'category__parent'
).all()

# With prefetch_related (for many-to-many and reverse foreign keys)
posts = Post.objects.prefetch_related('tags').all()
for post in posts:
    for tag in post.tags.all():  # No additional queries
        print(tag.name)

# Combining select_related and prefetch_related
posts = Post.objects.select_related('author', 'category').prefetch_related('tags').all()

# Custom prefetch queries
from django.db.models import Prefetch

# Prefetch only published comments
posts = Post.objects.prefetch_related(
    Prefetch(
        'comments',
        queryset=Comment.objects.filter(is_approved=True).select_related('author')
    )
).all()

# Prefetch with ordering
posts = Post.objects.prefetch_related(
    Prefetch(
        'comments',
        queryset=Comment.objects.filter(is_approved=True).order_by('-created_at')
    )
).all()

# Only load specific fields
posts = Post.objects.only('title', 'slug', 'created_at').all()

# Defer loading of large fields
posts = Post.objects.defer('content').all()

# Combining with relationships
posts = Post.objects.select_related('author').only(
    'title', 'slug', 'author__name', 'author__email'
).all()

# Defer large fields in relationships
posts = Post.objects.select_related('author').defer(
    'content', 'author__bio'
).all()

# Get dictionaries instead of model instances
post_data = Post.objects.values('title', 'author__name', 'created_at')
# Returns: [{'title': 'Post 1', 'author__name': 'John', 'created_at': datetime(...)}, ...]

# Get tuples
post_titles = Post.objects.values_list('title', flat=True)
# Returns: ['Post 1', 'Post 2', 'Post 3', ...]

# Multiple fields as tuples
post_data = Post.objects.values_list('title', 'author__name')
# Returns: [('Post 1', 'John'), ('Post 2', 'Jane'), ...]

# Named tuples
from collections import namedtuple
PostInfo = namedtuple('PostInfo', ['title', 'author_name'])
post_data = Post.objects.values_list('title', 'author__name')
posts = [PostInfo(*data) for data in post_data]

# Distinct values
unique_authors = Post.objects.values_list('author__name', flat=True).distinct()

# Values with annotations
from django.db.models import Count
author_stats = Author.objects.values('name').annotate(
    post_count=Count('posts')
).order_by('-post_count')

# Raw SQL with model instances
posts = Post.objects.raw(
    "SELECT * FROM blog_post WHERE view_count > %s ORDER BY view_count DESC",
    [1000]
)

# Raw SQL with parameters
posts = Post.objects.raw(
    "SELECT * FROM blog_post WHERE author_id = %s AND status = %s",
    [author_id, 'published']
)

# Raw SQL with extra fields
posts = Post.objects.raw(
    """
    SELECT *, 
           (view_count + like_count) as engagement_score 
    FROM blog_post 
    WHERE status = 'published'
    ORDER BY engagement_score DESC
    """
)

for post in posts:
    print(f"{post.title}: {post.engagement_score}")

# Using connection directly for non-model queries
from django.db import connection

def get_post_statistics():
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                DATE(created_at) as date,
                COUNT(*) as post_count,
                AVG(view_count) as avg_views
            FROM blog_post 
            WHERE status = 'published'
            GROUP BY DATE(created_at)
            ORDER BY date DESC
            LIMIT 30
        """)
        
        columns = [col[0] for col in cursor.description]
        return [dict(zip(columns, row)) for row in cursor.fetchall()]

# Named queries for reusability
class PostQuerySet(models.QuerySet):
    def popular(self, min_views=1000):
        return self.filter(view_count__gte=min_views)
    
    def recent(self, days=30):
        cutoff = timezone.now() - timedelta(days=days)
        return self.filter(created_at__gte=cutoff)
    
    def by_author(self, author):
        return self.filter(author=author)
    
    def published(self):
        return self.filter(status='published', published_at__lte=timezone.now())

class PostManager(models.Manager):
    def get_queryset(self):
        return PostQuerySet(self.model, using=self._db)
    
    def popular(self, min_views=1000):
        return self.get_queryset().popular(min_views)
    
    def recent_popular(self, days=30, min_views=1000):
        return self.get_queryset().recent(days).popular(min_views)

class Post(models.Model):
    # ... field definitions ...
    
    objects = PostManager()
    
    class Meta:
        # ... meta options ...

# Usage
popular_posts = Post.objects.popular()
recent_popular = Post.objects.recent_popular(days=7, min_views=500)
chained_query = Post.objects.published().popular().recent()

from django.db.models import F, Value, Case, When
from django.db.models.functions import Concat, Lower, Upper, Length

# F expressions for field references
# Increment view count
Post.objects.filter(id=1).update(view_count=F('view_count') + 1)

# Compare fields
posts = Post.objects.filter(like_count__gt=F('view_count') / 10)

# Annotations with F expressions
posts = Post.objects.annotate(
    engagement_ratio=F('like_count') / F('view_count')
).filter(engagement_ratio__gt=0.1)

# String functions
authors = Author.objects.annotate(
    full_name=Concat('first_name', Value(' '), 'last_name'),
    name_length=Length('name'),
    name_lower=Lower('name')
)

# Conditional expressions
posts = Post.objects.annotate(
    status_display=Case(
        When(status='draft', then=Value('Draft')),
        When(status='published', then=Value('Published')),
        When(status='archived', then=Value('Archived')),
        default=Value('Unknown')
    )
)

# Date functions
from django.db.models.functions import Extract, TruncDate, TruncMonth

posts = Post.objects.annotate(
    year=Extract('created_at', 'year'),
    month=Extract('created_at', 'month'),
    created_date=TruncDate('created_at')
)

# Group by month
monthly_stats = Post.objects.annotate(
    month=TruncMonth('created_at')
).values('month').annotate(
    count=Count('id')
).order_by('month')