Stock report view with conditional aggregates and prefetch
Python · Django · advanced · modification
Adds the warehouse stock report: queries up to 10k SKUs with their product group joined in, conditional aggregates (`Sum` + `Case`/`When`) for per-SKU totals and status, and a separate `prefetch_related` with a `Prefetch` object to pull individual stock-record rows for the per-warehouse breakdown. Paginates 50 SKUs per page. Filtering by warehouse narrows both the aggregate and the detail; filtering by status applies after the aggregates are computed. Tested end-to-end with filtered and unfiltered reports.
An internal warehouse dashboard used by floor managers. The `stock_records` table holds ~250k rows across 10k SKUs and 5 warehouses; the report is the most-hit endpoint, so query efficiency matters under concurrent load. `StockRecord.quantity` is a positive integer of at most 1,000,000, there is at most one record per SKU and warehouse, and a warehouse with no stock of a SKU has no record for it.
Requirements
- Implement `stock_report_view` listing every SKU with total quantity across warehouses, a count of distinct warehouses holding it, and a computed stock status.
- `SKU.category` is a required (non-null) foreign key; every SKU has a category.
- Stock status: `in_stock` when total quantity > 10, `low_stock` when 0 < total quantity ≤ 10, `out_of_stock` when total quantity = 0. The status domain is exhaustive for non-negative quantities.
- Accept optional query parameters: `warehouse` (a valid warehouse `code` — restricts the aggregate and the detail to that one warehouse) and `status` (one of `in_stock`, `low_stock`, `out_of_stock`; other values are ignored — no error).
- Use `select_related` for the `category` FK and `prefetch_related` with a `Prefetch` object for stock records to avoid N+1 queries across up to 10k SKUs. The annotation aggregate (GROUP BY sum) and the prefetch (individual rows for per-warehouse detail) serve separate purposes and must not interfere.
- Paginate with 50 SKUs per page. The `page` query parameter is a positive integer; treat non-integer values as page 1. `Paginator.get_page()` already handles out-of-range gracefully.
- Return JSON: `results` (a list of dicts with `code`, `name`, `category`, `total_quantity`, `warehouse_count`, `status`, `stock_detail`) plus `page` and `total_pages`.
Files touched
- warehouse/report.py
- warehouse/urls.py
--- warehouse/report.py
+from django.core.paginator import Paginator
+from django.db import models
+from django.db.models import Case, Count, Prefetch, Q, Sum, Value, When
+from django.db.models.functions import Coalesce
+from django.http import JsonResponse
+from django.shortcuts import get_object_or_404
+
+from warehouse.models import SKU, StockRecord, Warehouse
+
+IN_STOCK_THRESHOLD = 10
+
+
+def stock_report_view(request):
+ """Stock report across up to 10k SKUs with per-warehouse breakdown."""
+ skus = SKU.objects.select_related('category')
+
+ warehouse_code = request.GET.get('warehouse')
+ if warehouse_code:
+ warehouse = get_object_or_404(Warehouse, code=warehouse_code)
+ stock_filter = Q(stock_records__warehouse=warehouse)
+ stock_detail_qs = StockRecord.objects.filter(
+ warehouse=warehouse
+ ).select_related('warehouse')
+ else:
+ stock_filter = Q()
+ stock_detail_qs = StockRecord.objects.select_related('warehouse')
+
+ skus = skus.annotate(
+ total_qty=Coalesce(