Backend Live Coding

# ======================================================================
# Q: Increase the price of all products in a category by 5%, and update 100,000
# products from a CSV.
#
# update() and bulk_update() do not call save() or signals, and do not update
# auto_now fields. You need to say this. For new rows: bulk_create(...,
# update_conflicts=True, unique_fields=['sku'], update_fields=['price']), which
# does an upsert.
# ======================================================================

# A single UPDATE in the database, without loading objects
Product.objects.filter(category=cat).update(price=F('price') * Decimal('1.05'))

# From CSV: bulk_update in batches
by_sku = {row['sku']: Decimal(row['price']) for row in csv.DictReader(f)}
products = list(Product.objects.filter(sku__in=by_sku).only('id', 'sku', 'price'))
for p in products:
    p.price = by_sku[p.sku]
Product.objects.bulk_update(products, ['price'], batch_size=1000)

# ======================================================================
# Q: Find customers with more than 10 orders and their total quantity of items.
# What is the bug here?
#
# When you annotate with aggregates across two joins, the rows get multiplied
# (fan-out) and Sum/Count come out wrong. This is a known Django gotcha. They
# ask this question to see whether you have hit it in practice.
# ======================================================================

# WRONG: two joins on different relations multiply the rows
Customer.objects.annotate(n_orders=Count('orders'),
                          qty=Sum('orders__items__quantity'))

# CORRECT: distinct in Count, or a Subquery for the Sum
qty = (OrderItem.objects.filter(order__customer=OuterRef('pk'))
       .values('order__customer').annotate(s=Sum('quantity')).values('s'))
Customer.objects.annotate(n_orders=Count('orders', distinct=True),
                          qty=Subquery(qty)).filter(n_orders__gt=10)

# ======================================================================
# Q: Get the latest order of each customer, without N+1.
# ======================================================================

latest = Order.objects.filter(customer=OuterRef('pk')).order_by('-created_at')
customers = Customer.objects.annotate(
    last_order_at=Subquery(latest.values('created_at')[:1]),
    last_order_id=Subquery(latest.values('id')[:1]),
)

# Alternatively in PostgreSQL: DISTINCT ON
Order.objects.order_by('customer_id', '-created_at').distinct('customer_id')

# ======================================================================
# Q: Get the number of orders per status in one query, and the number of
# paid/cancelled orders per customer.
# ======================================================================

Order.objects.values('status').annotate(n=Count('id')).order_by('status')
# [{'status': 'paid', 'n': 120}, ...]

# conditional aggregation: one query, many counts
Customer.objects.annotate(
    paid=Count('orders', filter=Q(orders__status='paid')),
    cancelled=Count('orders', filter=Q(orders__status='cancelled')),
)

# ======================================================================
# Q: Find the products that have never been ordered.
# ======================================================================

Product.objects.filter(orderitem__isnull=True)
# or, often faster on large tables:
Product.objects.filter(~Exists(OrderItem.objects.filter(product=OuterRef('pk'))))