Cheatsheet: Django ORM — Queries avanzados

Django ORM — Queries Avanzados

Referencia rápida de los patrones de queries más útiles del ORM de Django. Cada ejemplo es copy-paste listo para tu proyecto.

Filtros con Q objects

from django.db.models import Q

# OR: productos baratos O en oferta
Product.objects.filter(Q(price__lt=10) | Q(category='sale'))

# AND + NOT: activos que tengan stock
Product.objects.filter(Q(active=True) & ~Q(stock=0))

# Búsqueda dinámica con condiciones opcionales
conditions = Q()
if search_term:
    conditions &= (
        Q(name__icontains=search_term) |
        Q(description__icontains=search_term)
    )
if category:
    conditions &= Q(category=category)
if min_price:
    conditions &= Q(price__gte=min_price)

results = Product.objects.filter(conditions)

Aggregations y annotations

from django.db.models import Count, Sum, Avg, F, Value
from django.db.models.functions import Coalesce

# Contar posts por usuario
User.objects.annotate(
    post_count=Count('post'),
    total_reactions=Count('post__reaction'),
).order_by('-post_count')

# Revenue por mes
from django.db.models.functions import TruncMonth
Order.objects.annotate(
    month=TruncMonth('created_at')
).values('month').annotate(
    revenue=Sum('amount'),
    orders=Count('id'),
    avg_ticket=Avg('amount'),
).order_by('month')

# Annotation con valor por defecto (evitar None)
User.objects.annotate(
    xp=Coalesce(Sum('xplog__amount'), Value(0))
)

Subqueries

from django.db.models import Subquery, OuterRef, Exists

# Último login de cada usuario
from django.contrib.sessions.models import Session
User.objects.annotate(
    last_post_date=Subquery(
        Post.objects.filter(
            author=OuterRef('pk')
        ).order_by('-created_at').values('created_at')[:1]
    )
)

# EXISTS: usuarios que han publicado al menos un post
User.objects.filter(
    Exists(Post.objects.filter(author=OuterRef('pk')))
)

Optimización de queries

# select_related: FK y OneToOne (JOIN en SQL)
Post.objects.select_related('author', 'category').all()

# prefetch_related: ManyToMany y reverse FK (query separada)
User.objects.prefetch_related('badges', 'post_set').all()

# only() y defer(): cargar solo columnas necesarias
Post.objects.only('title', 'created_at', 'author_id')
Post.objects.defer('content')  # todo excepto content

# Bulk operations
Post.objects.filter(is_draft=True, created_at__lt=cutoff).update(is_archived=True)
Post.objects.bulk_create([Post(title=t) for t in titles], batch_size=500)

# Evaluar queries en desarrollo
from django.db import connection
print(f"Queries ejecutadas: {len(connection.queries)}")
for q in connection.queries[-5:]:
    print(f"  {q['time']}s: {q['sql'][:100]}")

Managers y QuerySets personalizados

class PublishedManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(
            is_published=True, deleted_at__isnull=True
        )

    def trending(self, days=7):
        cutoff = timezone.now() - timedelta(days=days)
        return self.get_queryset().filter(
            created_at__gte=cutoff
        ).annotate(
            score=Count('reaction') + Count('comment') * 2
        ).order_by('-score')

class Post(models.Model):
    objects = models.Manager()  # default
    published = PublishedManager()

# Uso:
Post.published.trending(days=30)[:10]