Trouble with annotate(<calculatefield with F()>).values(somefield).annotate(Sum(<calculated fied>))

[sarvi]

Django Verion:  1.11.1

My models

class Cluster(models.Model):

....

    volume_limit = models.IntegerField('Cluster Volume Count Limit')

    volume_count = models.IntegerField('Cluster Volume Count', default=0)

....

class Node(models.Model):

    cluster = models.ForeignKey(Cluster, related_name='cluster_nodes', on_delete=models.PROTECT)

    volume_limit = models.IntegerField('Node Volume Count Limit')

    volume_count = models.IntegerField('Node Volume Count', default=0)

My query

nodes = Node.objects.filter(....)

intermediatestep = nodes.objects.annotate(volume_left=F('volume_limit')-F('volume_count')).values('cluster__id').annotate(cluster_volume_left_sum=Sum('volume_left'))

Error:

  File "/Users/sarvi/virtenv/toothless/lib/python2.7/site-packages/django/db/models/expressions.py", line 471, in resolve_expression

    return query.resolve_ref(self.name, allow_joins, reuse, summarize)

  File "/Users/sarvi/virtenv/toothless/lib/python2.7/site-packages/django/db/models/sql/query.py", line 1481, in resolve_ref

    return Ref(name, self.annotation_select[name])

KeyError: 'volume_left'

My ultimate Goal: 

    What I am trying to achieve is to get from a

    1. filtered query of Nodes, to calculate volume_left=F('volume_limit')-F('volume_count') for each node

    2. then group by cluster and sum up what volume_left in each node to cluster_volume_left_sum for each cluster. 

    3. Then get a cluster level cluster_volume_left=F('volume_limit')-F('volume_count'),

    4. Then calculate at the cluster level Min(0, F('cluster_volume_left')-F('volume_left__sum'))

I am obviously stuck way earlier :-)

From what I read of the documentation and Stackoverflow this should be possible

Any pointer on what I am doing wrong here would be of great help.

Thanks,

Sarvi

[sarvi]

This seems to work

Node.objects.values('cluster').annotate(volume_left=min(F('cluster__volume_limit')-F('cluster__volume_count'), Sum(F('volume_limit')-F('volume_count'))))

<QuerySet [{'cluster': 1, 'volume_left': 8}, {'cluster': 1, 'volume_left': 8}, {'cluster': 2, 'volume_left': 20}, {'cluster': 2, 'volume_left': 20}]>

Do I have it right? Just wanted to confirm.

Thanks

[sarvi]

A further bit of clarification

clusters = nodes.values('cluster').annotate(count_left=min(F('cluster__volume_limit') - F('cluster__volume_count'),

                                                           Sum(F('volume_limit') - F('volume_count'))),

                                            size_left=min(F('cluster__size_limit') - F('cluster__volume_size'),

                                                          Sum(F('size_limit') - F('volume_size'))))

produced duplicate entries as

<QuerySet [{'cluster': 1, 'size_left': 1960, 'count_left': 18}, {'cluster': 1, 'size_left': 1960, 'count_left': 18}, {'cluster': 2, 'size_left': 2000, 'count_left': 20}, {'cluster': 2, 'size_left': 2000, 'count_left': 20}]>

Using distinct()  remove duplicates as follows

clusters = nodes.values('cluster').annotate(count_left=min(F('cluster__volume_limit') - F('cluster__volume_count'),

                                                           Sum(F('volume_limit') - F('volume_count'))),

                                            size_left=min(F('cluster__size_limit') - F('cluster__volume_size'),

                                                          Sum(F('size_limit') - F('volume_size')))).distinct()

<QuerySet [{'cluster': 1, 'size_left': 1960, 'count_left': 18}, {'cluster': 2, 'size_left': 2000, 'count_left': 20}]>

But am not sure If that I am doing this appropriately.

Sarvi

[sarvi]

My previous version had a bug, the use of "min"

The following, using "Least()" instead of min() seems to have resolved that

    clusters = nodes.values('cluster').annotate(

        count_left=Least(F('cluster__volume_limit') - F('cluster__volume_count'),

                         Sum(F('volume_limit') - F('volume_count'))),

        size_left=Least(F('cluster__size_limit') - F('cluster__volume_size'),

                        Sum(F('size_limit') - F('volume_size')))).distinct()

    node_counts = clusters.aggregate(Sum('count_left'), Sum('size_left'))