How to rename fields of an annotated query?

Question

I have the following query

a = Mainfee.objects.values('collected_by__username').
                       distinct().annotate(Sum('amount'))

The result looks like this

[{'collected_by__username': u'maindesk', 'amount__sum': 800}]

How can I rename the first key to a and second key to b?

I tried the following

m = Mainfee.objects.extra(select = 
   {'a':'collected_by__username'}).values('a').distinct().
    annotate(Sum('amount'))

and received this

DatabaseError: no such column: collected_by__username

I also tried

m = Mainfee.objects.extra(select = 
   {'a':'collected_by__username'}).values('collected_by__username').distinct().
    annotate(Sum('amount'))

and got

[{'collected_by__username': u'maindesk', 'amount__sum': 800}]

PS: I want to rename the second field too

Answer 1

You can change the dictionary key of the annotated value by using keyword arguments:

m = Mainfee.objects.values('collected_by__username').annotate(b=Sum('amount'))

[{'collected_by__username': u'maindesk', 'b': 800}]

There is no quick and easy way to rename a related field, though. You can convert it in Python, but you'll have to ask yourself if that is really necessary:

m = Mainfee.objects.values('collected_by__username').annotate(b=Sum('amount'))
m = [{'a': x['collected_by__username'], 'b': x['b']} for x in m]

Or maybe this is slightly faster:

m = Mainfee.objects.values_list('collected_by__username').annotate(Sum('amount'))
m = [{'a': x[0], 'b': x[1]} for x in m]

Both methods will of course force evaluation of the whole queryset and don't allow further filtering/ordering etc. through the queryset's methods.

Also note that the call to distinct() is redundant when you're using values() or values_list() together with annotate().

Answer 2

You can use an F() expression on collected_by__username:

An F() object represents the value of a model field or annotated column. It makes it possible to refer to model field values and perform database operations using them without actually having to pull them out of the database into Python memory... F() can be used to create dynamic fields on your models...

If you do this inside an annotate clause, we can 'rename' the field to a. You don't need to use an F() expression when calculating the total. See below:

from django.db.models import F
    
(Mainfee.objects.values('collected_by__username')
    .annotate(a=F('collected_by__username'), b=Sum('amount'))
    .values('a', 'b'))

Making some assumptions about your model, this would produce something like:

<QuerySet [{'a': 'item1', 'b': 1234}, {'a': 'item2', 'b': 4321}]>