Pivoting Data And Complex Annotations In Django Orm

The ORM in Django lets us easily annotate (add fields to) querysets based on related data, hwoever I can't find a way to get multiple annotations for different filtered subsets of

Solution 1:

You have Python, use it.

from collections import defaultdict
summary = defaultdict( int )
for issue in Issues.objects.all():
    summary[issue.queue, issue.status] += 1

Now your summary object has queue, status as a two-tuple key. You can display it directly, using various template techniques.

Or, you can regroup it into a table-like structure, if that's simpler.

table= []
queues = list( q for q,_ in summary.keys() )
for q in sorted( queues ):
    table.append( q.id, q.name, summary.count(q,'open'), summary.count(q.'closed') )

You have lots and lots of Python techniques for doing pivot tables.

If you measure, you may find that a mostly-Python solution like this is actually faster than a pure SQL solution. Why? Mappings can be faster than SQL algorithms which require a sort as part of a GROUP-BY.

Solution 2:

Django has added a lot of functionality to the ORM since this question was originally asked. The answer to how to pivot data since Django 1.8 is to use the Case/When conditional expressions. And there is a third party app that will do that for you, PyPI and documentation