I want to get a count all the items in the database for a given user, grouped into buckets depending on a timestamp.
I have a dictionary which defines a set of categories like this:
age_categories = {'cat1': {'start': <timestamp>, 'end': <timestamp>}},
{'cat2': {'start': <timestamp>, 'end': <timestamp>}},
...
I want to get a result from the database that looks like this:
User | cat1 | cat2 | cat3 | ...
Joe Smith | 12 | 0 | 7 | ...
Bob Smith | 4 | 9 | 11 | ...
Based on this question, I've tried:
age_query = [key=Count(Case(When(datefield__range=(val['start'], val['end']), then=1))) for key, val in age_categories.items()]
...
results = Table.objects.filter(...).values(...).annotate(*age_query)
but I can't get that to work. It gives me an invalid syntax error on the line where I define age_query.
If I change the query to be a list of strings as
age_query = [key + "=Count(Case(When(datefield__range=('" + val['start'] + "', '" + val['end'] + "'), then=1)))" for key, val in age_categories.items()]
it tells me that Complex annotations require an alias when the intent is that the dictionary key should be the alias.
Is this achievable? I know how I'd do it in raw SQL, but I'd really rather use Django's tools if at all possible.
Thanks.
Python 3.6.5 Django 1.11.6
