python - Pivoting data and complex annotations in Django ORM -

the orm in django lets annotate (add fields to) querysets based on related data, hwoever can't find way multiple annotations different filtered subsets of related data.

this being asked in relation django-helpdesk, open-source django-powered trouble-ticket tracker. need have data pivoted charting , reporting purposes

consider these models:

choice_list = (     ('open', 'open'),     ('closed', 'closed'), )  class queue(models.model):     name = models.charfield(max_length=40)  class issue(models.model):     subject = models.charfield(max_length=40)     queue = models.foreignkey(queue)     status = models.charfield(max_length=10, choices=choice_list) 

and dataset:

queues:

id | name ---+------------------------------ 1  | product information requests 2  | service requests 

issues:

id | queue | status ---+-------+--------- 1  | 1     | open 2  | 1     | open 3  | 1     | closed 4  | 2     | open 5  | 2     | closed 6  | 2     | closed 7  | 2     | closed 

i see annotation/aggregate this:

queue id | name                          | open | closed ---------+-------------------------------+------+-------- 1        | product information requests  | 2    | 1 2        | service requests              | 1    | 3 

this crosstab or pivot table, in excel parlance. building output using custom sql queries, if can move using django orm can more filter data dynamically without doing dodgy insertion of clauses in sql.

for "bonus points": how 1 pivot field (status in example above) date, , wanted columns months / weeks / quarters / days?

you have python, use it.

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

now summary object has queue, status two-tuple key. can display directly, using various template techniques.

or, can regroup table-like structure, if that's simpler.

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

you have lots , lots of python techniques doing pivot tables.

if measure, may find mostly-python solution faster pure sql solution. why? mappings can faster sql algorithms require sort part of group-by.