Django Aggregation of values queryset

Question

I have a ValuesQuerySet called data.

I am trying to get a summary count of all the type for each object

data.values('type') produces this output:

[{'type': u'internal'}, {'type': u'internal'}, {'type': u'external'}, {'type': u'external'}]

I want to get a breakdown like this (there can be more then just 'internal' and 'external' as choices. This could be up to 20 different types:

internal: 2
external: 2

I'm trying this but it's just returning a empty dictionary...

data.values('type').aggregate(Count('type'))

Annotate is producing undesirbale results as well:

data.values('type').annotate(Count('type'))


[{'type': u'internal', 'type_count': 1}, {'type': u'internal', 'type_count': 1}, {'type': u'external', 'type_count': 1}, {'type': u'external', 'type_count': 1}]

Models.py

class Purchase(models.Model):    

    type = models.ForeignKey(Types)

What is the type of `data`, what is the type of `data.values('type')`? — , Feb 28 '13 at 14:34

catherine · Accepted Answer · 2013-02-28T15:34:04.643

5

 lists = ModelName.objects.values('type').annotate(count=Count('type'))

In html:

 {% for list in lists %}
     {{list.type}} - {{list.count}}<br/>
 {% endfor %}

Test:

 {{lists}}
 //don't use forloop yet. This will tests if the above query produce data or it is empty

UPDATED:

def view_name(request):
    lists = ModelName.objects.values_list('type', flat=True).distinct()
    types = []
    for list in lists:
        type_count = ModelName.objects.filter(type=list.type).count()
        types.append({'type': list.type, 'count': type_count})

    return render(request, 'page.html', {
            'types': types,
    })

{% for type in types %}
    {{type.type}} - {{type.count}}
{% endfor %}

edited Feb 28 '13 at 15:34

answered Feb 28 '13 at 14:21

catherine

22,492
12
61
85

That is still returning an empty dictionary for me. I have almost 500k objects in this model but nothing was produced from that query – Austin Feb 28 '13 at 14:43
@Austin try to change aggregate to annotate. After that in your template test like this {{lists}}, don't use forloop. It's for testing so that we will know if there are query – catherine Feb 28 '13 at 14:47
I do get somewhat desired results from using annotate. Is the problem having a ValuesQuerySet compared to QuerySet? I would like to try to avoid another database call as my 'data' variable comes from using a query that uses a filter and an exclude. I then get the values() of particular fields from there. I guess I'm just trying to understand why my original code did not work. – Austin Feb 28 '13 at 14:52
I added annotate to my original post and displayed it's result. Not sure why it's displaying this way – Austin Feb 28 '13 at 14:59
because the query filter only one model. It would be easy if the type is in another model. That's why I request for your model codes – catherine Feb 28 '13 at 15:01
model posted. It's all coming from within the same model – Austin Feb 28 '13 at 15:06
this is difficult. I will search for other solution for this – catherine Feb 28 '13 at 15:13
I apologize. I have two different things I am summarizing. One is working, the other, is not. The one that is not is indeed related to another model – Austin Feb 28 '13 at 15:23
@austin i updated my answer try it. and about what you have said i thought it's not related to other model – catherine Feb 28 '13 at 15:35

score 1 · Answer 2 · answered Feb 28 '13 at 14:24

1

They are several approach, let's supose you want results in a dictionary, the easy way is:

results = { 'internal': data.filter( value = 'internal'  ).count(),
            'external': data.filter( value = 'external'  ).count() }

For few queryset you can use itertools, this translate work from database layer to django tier. That means that it is only a solution for little querysets.

from itertools import groupby
results = groupby(data.all(), lambda x: x.type)

answered Feb 28 '13 at 14:24

dani herrera

48,760
8
117
177

I should have mentioned that there can be more than two choices. Up to 20 aside from 'internal' and 'external' – Austin Feb 28 '13 at 14:33

Django Aggregation of values queryset

2 Answers2