0

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)
    ...
bjorn
  • 120
  • 1
  • 5
  • This is ought to happen as you are joining a lot of tables in Django aggregating which results in cartesian product, You will have to dissect this in one more layer of subqueries. It would be good idea to post models so that there is https://stackoverflow.com/help/minimal-reproducible-example for someone who gets time to try to help you – iklinac Jan 07 '21 at 09:22
  • thank you for the advice iklinac - followed it – bjorn Jan 07 '21 at 09:42

0 Answers0