So I'd like to annotate a sum to a queryset, which has a little calculation rule with vars from remote tables.
facturen = Factuur.objects.values('inkoopopdracht').filter(
inkoopopdracht=OuterRef('pk'),)
gefactureerd = facturen.annotate(
gefactureerd=Sum(Case(
When(
factuurpost__inkooppost__btw__systematiek=2,
then=(F(
'factuurpost__inkooppost__aantal')*F('factuurpost__inkooppost__eenheidprijs'))),
default=F(
'factuurpost__inkooppost__aantal')*F('factuurpost__inkooppost__eenheidprijs')*(
1+F('factuurpost__inkooppost__btw__percentage')),
output_field=DecimalField(),
)),
).values('gefactureerd')
qs = qs.annotate(
factuursom=Subquery(gefactureerd.values(
'gefactureerd'), output_field=DecimalField()),
)
The result of the above query is satisfactory. Except when there is a multiple of 'factuurpost'. In this case the sum seems to be multiplied with factuurpost instances.
A fix for this, would be 'distinct=True' on the Sum. However since it only distincts values and not instances, this introduces a new problem. Similar values are no ignored, yielding the wrong sum.
This seems to be an issues other people have come across with as well, e.g. : Django Count and Sum annotations interfere with each other
Now, I used the solution there, with the subquery. I tried to nest the subquery to no avail. Does anyone see a potential solution to have it calculate the right sum in all cases? Thank you in advance!
EDIT The models:
class Factuur(models.Model):
inkoopopdracht = models.ForeignKey(InkoopOpdracht, models.CASCADE)
...
class FactuurPost(models.Model):
factuur = models.ForeignKey(Factuur, models.CASCADE)
inkooppost = models.ForeignKey(InkoopPost, on_delete=models.CASCADE)
aantal = models.IntegerField()
...
class InkoopPost(models.Model):
inkoopopdracht = models.ForeignKey(InkoopOpdracht, models.CASCADE)
aantal = models.IntegerField()
eenheidprijs = models.DecimalField(max_digits=10, decimal_places=2)
...