django-orm-patterns

What this skill does

# Django ORM Patterns

Master Django ORM for building efficient, scalable database-driven
applications with complex queries and relationships.

## Model Definition

Define models with proper field types, constraints, and metadata.

```python
from django.db import models
from django.core.validators import MinValueValidator, MaxValueValidator

class User(models.Model):
    email = models.EmailField(unique=True, db_index=True)
    name = models.CharField(max_length=100)
    is_active = models.BooleanField(default=True)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    class Meta:
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['email']),
            models.Index(fields=['created_at', 'is_active']),
        ]
        verbose_name = 'User'
        verbose_name_plural = 'Users'

    def __str__(self):
        return self.email

class Post(models.Model):
    title = models.CharField(max_length=200)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts')
    published = models.BooleanField(default=False)
    views = models.PositiveIntegerField(default=0)
    created_at = models.DateTimeField(auto_now_add=True)

    class Meta:
        ordering = ['-created_at']
        indexes = [
            models.Index(fields=['author', 'published']),
        ]
```

## QuerySet API Basics

Use Django's QuerySet API for efficient database queries.

```python
# All records
users = User.objects.all()

# Filtering
active_users = User.objects.filter(is_active=True)
inactive_users = User.objects.exclude(is_active=True)

# Get single record (raises exception if not found or multiple found)
user = User.objects.get(email='[email protected]')

# Get or create
user, created = User.objects.get_or_create(
    email='[email protected]',
    defaults={'name': 'John Doe'}
)

# Update or create
user, created = User.objects.update_or_create(
    email='[email protected]',
    defaults={'name': 'Jane Doe', 'is_active': True}
)

# Chaining filters
posts = Post.objects.filter(published=True).filter(author__is_active=True)

# Order by
users = User.objects.order_by('-created_at', 'name')

# Limit results
recent_users = User.objects.all()[:10]

# Count
user_count = User.objects.filter(is_active=True).count()

# Exists
has_active_users = User.objects.filter(is_active=True).exists()
```

## Q Objects for Complex Queries

Build complex queries with Q objects for OR and NOT operations.

```python
from django.db.models import Q

# OR queries
users = User.objects.filter(
    Q(name__icontains='john') | Q(email__icontains='john')
)

# AND with OR
users = User.objects.filter(
    Q(is_active=True) & (Q(name__icontains='john') | Q(email__icontains='john'))
)

# NOT queries
users = User.objects.filter(~Q(is_active=True))

# Complex combinations
posts = Post.objects.filter(
    Q(published=True) &
    (Q(author__name__icontains='john') | Q(title__icontains='important')) &
    ~Q(views__lt=100)
)

# Dynamic query building
def search_users(name=None, email=None, is_active=None):
    query = Q()
    if name:
        query &= Q(name__icontains=name)
    if email:
        query &= Q(email__icontains=email)
    if is_active is not None:
        query &= Q(is_active=is_active)
    return User.objects.filter(query)
```

## F Objects for Field References

Use F objects to reference model fields in queries and updates.

```python
from django.db.models import F

# Compare fields
posts = Post.objects.filter(views__gt=F('author__posts__count'))

# Update based on current value
Post.objects.filter(published=True).update(views=F('views') + 1)

# Avoid race conditions
post = Post.objects.get(id=1)
post.views = F('views') + 1
post.save()
post.refresh_from_db()  # Get updated value

# Complex expressions
from django.db.models import ExpressionWrapper, IntegerField

Post.objects.annotate(
    adjusted_views=ExpressionWrapper(
        F('views') * 2 + 10,
        output_field=IntegerField()
    )
)
```

## Aggregation and Annotation

Perform database-level calculations and add computed fields.

```python
from django.db.models import Count, Sum, Avg, Max, Min

# Simple aggregation
from django.db.models import Avg
avg_views = Post.objects.aggregate(Avg('views'))
# Returns: {'views__avg': 42.5}

# Multiple aggregations
stats = Post.objects.aggregate(
    total_posts=Count('id'),
    avg_views=Avg('views'),
    max_views=Max('views'),
    min_views=Min('views')
)

# Annotation (adds field to each object)
users = User.objects.annotate(
    post_count=Count('posts'),
    total_views=Sum('posts__views')
)

for user in users:
    print(f"{user.name}: {user.post_count} posts, {user.total_views} views")

# Filter by annotation
popular_users = User.objects.annotate(
    post_count=Count('posts')
).filter(post_count__gt=10)

# Complex annotations
from django.db.models import Case, When, Value, CharField

User.objects.annotate(
    user_type=Case(
        When(post_count__gt=10, then=Value('prolific')),
        When(post_count__gt=5, then=Value('active')),
        default=Value('casual'),
        output_field=CharField()
    )
)
```

## Prefetch and Select Related (N+1 Prevention)

Optimize queries by reducing database hits with eager loading.

```python
# Select related (for ForeignKey and OneToOne)
posts = Post.objects.select_related('author').all()
for post in posts:
    print(post.author.name)  # No additional query

# Prefetch related (for ManyToMany and reverse ForeignKey)
from django.db.models import Prefetch

users = User.objects.prefetch_related('posts').all()
for user in users:
    for post in user.posts.all():  # No additional query
        print(post.title)

# Custom prefetch
users = User.objects.prefetch_related(
    Prefetch(
        'posts',
        queryset=Post.objects.filter(published=True).order_by('-created_at')
    )
)

# Multiple levels
posts = Post.objects.select_related(
    'author'
).prefetch_related(
    'author__posts'  # Prefetch all posts by the same author
)

# Combining both
Post.objects.select_related('author').prefetch_related('tags')
```

## Custom Managers and QuerySets

Create reusable query logic with custom managers and querysets.

```python
from django.db import models

class PublishedQuerySet(models.QuerySet):
    def published(self):
        return self.filter(published=True)

    def recent(self):
        return self.order_by('-created_at')[:10]

    def by_author(self, author):
        return self.filter(author=author)

class PublishedManager(models.Manager):
    def get_queryset(self):
        return PublishedQuerySet(self.model, using=self._db)

    def published(self):
        return self.get_queryset().published()

    def recent(self):
        return self.get_queryset().recent()

class Post(models.Model):
    # fields...
    objects = models.Manager()  # Default manager
    published_posts = PublishedManager()  # Custom manager

    class Meta:
        base_manager_name = 'objects'

# Usage
Post.published_posts.published().recent()
Post.published_posts.published().by_author(user)

# Chaining custom methods
class UserQuerySet(models.QuerySet):
    def active(self):
        return self.filter(is_active=True)

    def with_posts(self):
        return self.annotate(post_count=Count('posts')).filter(post_count__gt=0)

User.objects.active().with_posts()
```

## Transactions and Atomic Blocks

Ensure data consistency with database transactions.

```python
from django.db import transaction

# Atomic decorator
@transaction.atomic
def create_user_with_post(email, name, post_title):
    user = User.objects.create(email=email, name=name)
    Post.objects.create(title=post_title, author=user)
    return user

# Context manager
def update_user_posts(user_id):
    try:
        with transaction.atomic():
            user = User.objects.select_for_update().get(id=user_id)
            user.posts.update(published=True)
            user.is_active