How to annotate with sum of all descendants?

[SuperMasterBlasterLaser]

Hello. I'm sorry if its simple question, but it is hard to understand for me how to do it.

I have this model:

class Branch(TreeNode):
     amount = models.IntergerField(default=0)

I want to make queryset where each of these branches has annotated sum from amount field, including all of it's descendants. I've been trying to use something like this:

Branch.objects.descendants(of=OuterRef('pk')).aggregate(sum=Sum('amount'))

But it throws an error.

[matthiask]

Hi,

Thanks for the issue. What error are you getting?

I'm not that surprised that complex scenarios do not work. The library really is a well-working, smart hack on top of the Django ORM and doesn't integrate deeply with the more advanced functionality that's there.

Maybe addressing your question; of is supposed to be either a primary key value or a model instance, and doesn't support other types at the moment. When thinking about the CTE it's maybe also somewhat obvious why it doesn't work out of the box -- the CTE builds the tree from the root nodes down, and you're aggregating from the leaves up.

If the tree is small enough and you have to load nodes anyway, maybe you could do the summation in Python code. Otherwise I'd probably try writing some raw SQL by hand for this use case.

[SuperMasterBlasterLaser]

@matthiask

Currently my code above throws error that says it expects integer but got OuterRef(pk).

Because of my data structure is not so complex, I just loaded root models (that are without parents), and calculated sum for each of them by using descendants function, then just sorted them by using python code.

I hope in future some complex calculations could be done for nodes.