Re: Trouble with annotate().values(somefield).annotate(Sum())

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'))

On Saturday, July 1, 2017 at 7:34:37 PM UTC-7, sarvi wrote:

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

On Saturday, July 1, 2017 at 5:59:41 PM UTC-7, sarvi wrote:

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

On Saturday, July 1, 2017 at 5:41:20 PM UTC-7, sarvi wrote:

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/b335311d-e23a-4e6e-bca4-23e2f379e75d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.