0

I am wondering if Django's ORM allows us to do aggregate operations on subqueires, and then do arithmetic with the resulting values.

What would be the proper way to go about something like this:

record = PackingRecord.objects.filter(product=OuterRef('pk'))
packed = FifoLink.objects.filter(packing_record__product=OuterRef('pk'))

output = obj_set.annotate(
    in_stock=(Subquery(record.aggregate(Sum('qty'))) - Subquery(packed.aggregate(Sum('sale__qty'))))
).values('id', 'name', 'in_stock')
Adam Starrh
  • 6,428
  • 8
  • 50
  • 89

1 Answers1

1

You certainly can but as far as I have dig, you cannot use aggregate(). When trying to use aggregate() in a Subquery django complains about trying to execute a query that has OuterRefs. The way I do this (I really don't know if this is THE way - according to the docs it is -) is by using annotate(). In a case like the one you have in your example I'd do something like the following:

records_total = (PackingRecord.objects.filter(product=OuterRef('pk'))
    .values('product') # Group by product
    .annotate(total=Sum('qty')) # Sum qty for 'each' product
    .values('total')
)
packed_total = (FifoLink.objects.filter(packing_record__product=OuterRef('pk'))
    .values('packing_record__product') # Group by packing_record__product
    .annotate(total=Sum('sale__qty')) # Sum sale__qty for 'each' product
    .values('total')
)
output = obj_set.annotate(
    r_tot=Subquery(record_total[:1]),
    p_tot=Subquery(packed_total[:1])
).annotate(
    in_stock=F('r_tot')-F('p_tot')
) # Whatever you need

I did not run the example, so it may need some adjustments here and there.

ivissani
  • 2,614
  • 1
  • 18
  • 12