orm - Aggregation on 'one to many' for matrix view in Django -

i have 2 tables below.

these in 'one(history.testinfoid) many(result.testinfoid)' relationship. (result table external database)

class history(models.model): # default database     idx = models.autofield(primary_key=true)     scenario_id = models.foreignkey(scenario)     executor = models.charfield(max_length=255)     createdate = models.datetimefield()      testinfoid = models.integerfield(unique=true)   class result(models.model):  # external (result.objects.using('external'))     idx = models.autofield(primary_key=true)      testinfoid = models.foreignkey(history, to_field='testinfoid', related_name='result')     testresult = models.charfield(max_length=10)      class meta:         unique_together = (('idx', 'testinfoid'),) 

so, want express count 'testresult' field in result table.

it has condition such 'pass' or 'fail'.

i want express count query set each condition. this.

[{'idx': 1, 'pass_count': 10, 'fail_count': 5, 'executor': 'someone', ...} ...
...
{'idx': 10, 'pass_count': 1, 'fail_count': 10, 'executor': 'someone', ...}]

is possible?

it two level aggregation second level should displayed table columns - "matrix view".

a) solution python loop create columns annotations second level ("testresult").

from django.db.models import count collections import ordereddict   qs = (history.objects       .values('pk', 'executor', 'testinfoid',... 'result__testresult')       .annotate(result_count=count('pk'))       ) qs = qs.filter(...).order_by(...)  data = ordereddict() count_columns = ('pass_count', 'fail_count', 'error_count',                  'expected_failure_count', 'unexpected_success_count') row in qs:     data.setdefault(row.pk, dict.fromkeys(count_columns, 0)).update(         {(k if k != result_count else row['result__testresult'] + '_count'): v          k, v in row_items()          if k != 'result__testresult'          }     ) out = list(data.values()) 

the class ordereddict used preserve order_by().

b) solution subquery in django 1.11+ (if result should queryset. e.g. sorted or filtered in admin view clicking, , if more complicated query acceptable , number of columns *_count low.). can write solution subquery, i'm not sure if query fast enough different database backends. maybe other answers.