0

I am trying to annotate a Django model by calculating a non-trivial expression using a property of a ManyToManyField in the model.

The situation can be translated into the following simple example structure of "payments" that should be split between a number of "companies" according to their relative sizes. Which companies participate in a given payment is stored as a ManyToManyField:

class Company:
  relative_size = models.DecimalField()   ## can be assumed to add up to 1 among all companies

class Payment(models.Model):
  companies = models.ManyToManyField(Company, related_name='payments', blank=True)
  amount = models.DecimalField()

I'm now trying to query for the total amount that each company participated in. If the share was the same in each of the payments, this would be as simple as

companies = Company.objects.annotate(total_payments=Sum(F('payments__amount')*F('relative_size')))
for company in companies:
  print("Company %s: %s" (str(company), total_payments))

But how to do this in the case where I have to calculate the correct fraction for every payment based on which companies participated? I suspect I have to use Subquery but couldn't really adapt the examples in the documentation to my case.

PS: As a workaround I have implemented this in "Python" instead of in queries as follows:

payments_annotated = Payment.objects.annotate(sum_involved_companies_sizes=Sum(F('companies__relative_size')))
companies = Company.objects.all()
for c in companies:
  c.sum = Decimal('0.0')
  for p in payments_annotated.filter(companies__id=c.id):
    c.sum += p.amount * c.relative_size/p.sum_involved_companies_sizes

But I guess this must be possible and more efficient within the queries system, so I'm hoping an expert can help out? Thanks!

Frank Siegert
  • 1
  • 2
  • For every payment, the related companies `relative_size` will equal `1`? – Iain Shelvington Mar 22 '20 at 02:31
  • @IainShelvington No, for every payment the `sum(relative_size)` can be different from `1` because not all companies participate in each payment. That's why I think I need this `SubQuery` to calculate the "total size" that participates in each payment. – Frank Siegert Mar 22 '20 at 10:06

0 Answers0