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 [email protected].
To post to this group, send email to [email protected].
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.
