When you annotate with aggregates across two joins, the rows get multiplied (fan-out) and Sum/Count come out wrong.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | # ====================================================================== # 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) |
