Case/When в Django ORM | Курс Django ORM урок 3.5

from django.db.models import Case, When, Value, IntegerField

Case(
    When(условие, then=значение),
    When(условие, then=значение),
    default=значение_по_умолчанию,
    output_field=IntegerField(),
)

CASE
    WHEN условие THEN значение
    WHEN условие THEN значение
    ELSE значение_по_умолчанию
END

from django.db.models import Case, When, Value, CharField

# Добавить метку наличия товара
products = Product.objects.annotate(
    availability=Case(
        When(stock=0, then=Value("out_of_stock")),
        When(stock__lte=5, then=Value("low_stock")),
        default=Value("in_stock"),
        output_field=CharField(),
    )
)

for p in products:
    print(f"{p.name}: {p.availability}")

SELECT shop_product.*,
       CASE
           WHEN stock = 0 THEN 'out_of_stock'
           WHEN stock <= 5 THEN 'low_stock'
           ELSE 'in_stock'
       END AS availability
FROM shop_product
ORDER BY name;

from django.db.models import DecimalField
from decimal import Decimal

# Цена со скидкой в зависимости от категории
products = Product.objects.annotate(
    discounted_price=Case(
        When(category__slug="phones", then=F("price") * Decimal("0.9")),
        When(category__slug="laptops", then=F("price") * Decimal("0.85")),
        default=F("price"),
        output_field=DecimalField(max_digits=10, decimal_places=2),
    )
)

SELECT shop_product.*,
       CASE
           WHEN shop_category.slug = 'phones' THEN price * 0.9
           WHEN shop_category.slug = 'laptops' THEN price * 0.85
           ELSE price
       END AS discounted_price
FROM shop_product
LEFT OUTER JOIN shop_category ON shop_product.category_id = shop_category.id
ORDER BY name;

from django.db.models import IntegerField

# Сортировка заказов: сначала processing, потом pending, потом остальные
orders = Order.objects.annotate(
    status_priority=Case(
        When(status="processing", then=Value(1)),
        When(status="pending", then=Value(2)),
        default=Value(3),
        output_field=IntegerField(),
    )
).order_by("status_priority", "-created_at")

SELECT shop_order.*,
       CASE
           WHEN status = 'processing' THEN 1
           WHEN status = 'pending' THEN 2
           ELSE 3
       END AS status_priority
FROM shop_order
ORDER BY status_priority ASC, created_at DESC;

# Обновить статус в зависимости от текущего значения
from django.db.models import F

Order.objects.update(
    status=Case(
        When(status="pending", then=Value("processing")),
        When(status="processing", then=Value("shipped")),
        default=F("status"),  # остальные не меняем
        output_field=CharField(max_length=20),
    )
)

UPDATE shop_order
SET status = CASE
    WHEN status = 'pending' THEN 'processing'
    WHEN status = 'processing' THEN 'shipped'
    ELSE status
END;

from django.db.models import Q

Product.objects.annotate(
    price_tier=Case(
        When(Q(price__lt=100) | Q(stock=0), then=Value("budget_or_unavailable")),
        When(price__gte=100, price__lt=500, then=Value("mid_range")),
        When(price__gte=500, then=Value("premium")),
        default=Value("unknown"),
        output_field=CharField(),
    )
)

CASE
    WHEN (price < 100 OR stock = 0) THEN 'budget_or_unavailable'
    WHEN price >= 100 AND price < 500 THEN 'mid_range'
    WHEN price >= 500 THEN 'premium'
    ELSE 'unknown'
END

from django.db.models import Sum, IntegerField

# Подсчет по категориям рейтинга
Product.objects.annotate(
    high_rating_count=Sum(
        Case(
            When(reviews__rating__gte=4, then=Value(1)),
            default=Value(0),
            output_field=IntegerField(),
        )
    ),
    low_rating_count=Sum(
        Case(
            When(reviews__rating__lte=2, then=Value(1)),
            default=Value(0),
            output_field=IntegerField(),
        )
    ),
)

# Django не может вывести тип, будет ошибка
Product.objects.annotate(
    tier=Case(
        When(price__gt=500, then=Value("premium")), 
        default=Value("budget")
    )
)
# ValueError: Cannot resolve expression type

# Правильно
Product.objects.annotate(
    tier=Case(
        When(price__gt=500, then=Value("premium")),
        default=Value("budget"),
        output_field=CharField(),
    )
)

# Неверно, stock=0 попадет в первое условие (stock__lte=5), не дойдет до второго
Case(
    When(stock__lte=5, then=Value("low")),
    When(stock=0, then=Value("out")),  # никогда не выполнится
    default=Value("ok"),
)

# Правильно более специфичное условие первым
Case(
    When(stock=0, then=Value("out")),
    When(stock__lte=5, then=Value("low")),
    default=Value("ok"),
)