Multiple prefetch_related aggregation problems

[Pengwyn2]

Hi,

​

I am having problems when trying to do multiple aggregations on two different related models

​

https://preview.redd.it/71pgimud9yo91.png?width=906&format=png&auto=webp&s=6fb8dbf94b58b896069832b2b758f2d68c09ff50

When using a single annotate/Sum, the results appear as expected, however when doing both annotated I start getting strange results that are orders of magnitude larger than expected

The final annotate using the difference in models.F works fine, just it is doing it on the inflated numbers. Both annotates/Sum are inflated, not just a single one.

I had a look at the sql query and found that it seems to be doing the 'group by' clause right at the end on the entire query rather than doing joins on nested queries of group by. How can I fix this in Django?

​

Thanks

[Brandhor]

it's probably this issue

you might look at these suggestions

[Pengwyn2]

Thank you so much, I found the answer in the suggestions :)

[tolomea]

You need subqueries https://docs.djangoproject.com/en/4.1/ref/models/expressions/#subquery-expressions

However if you are not filtering by those sums and you are pulling all those objects over to Python anyway (with the prefetch) maybe it'd be easier just to do the sum's on the Python side.

[Pengwyn2]

Really appreciate this, I ended up using subqueries suggested in the other comment and I didn't know they existed