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

For `values`: http://stackoverflow.com/questions/10598940/how-to-rename-items-in-values-in-django — Ciro Santilli OurBigBook.com, Apr 23 '17 at 22:20

score 13 · Accepted Answer · answered Aug 09 '13 at 11:10

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().

score 9 · Answer 2 · edited Aug 25 '20 at 12:07

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}]>

Could you add some details why this is an answer to the question? — Risadinha, Apr 25 '19 at 08:19
Added some explanation of `F()` expressions to your answer. Also, whilst you were on the right lines, you hadn't cast the the total to `b`, so I have corrected that. — alstr, Aug 24 '20 at 17:21

score 0 · Answer 3 · edited May 23 '17 at 11:54

0

I'm not sure how to rename the first key, it seems there is a solution here: How to rename items in values() in Django? but it returns error in your case since the generated SQL would be:

SELECT collected_by__username AS a WHERE ...

And of course collected_by__username doesn't exist.

Note: this feature is requested here https://code.djangoproject.com/ticket/16735

The 2nd key can be renamed by using: annotate(b=Sum('amount')

So try this first:

m = (Mainfee.objects
     .extra(select={'a': 'collected_by.username'})  # check your DB for the exact table and field name
     .values('a')
     .distinct()
     .annotate(b=Sum('amount')))

I guess it would spit out an exception but it worths a try. Otherwise don't try to rename the first key for now since it would be very hacky anway.

Hope it helps.

edited May 23 '17 at 11:54

Community

1
1

answered Aug 09 '13 at 11:09

Hieu Nguyen

8,563
2
36
43

There's no way to do this on a SQL level without a `JOIN` statement, and that would require either hacking into the Django ORM or some raw SQL statements. Even with `Mainfee.objects.raw()` it's not possible, as that requires an id, while you can't select that without grouping by it. – knbk Aug 09 '13 at 11:27
Yeah that's why I recommend him to drop this idea. – Hieu Nguyen Aug 09 '13 at 12:08

score -1 · Answer 4 · answered Jun 29 '16 at 15:39

-1

Just in case someone stumbles on this page.

m = Mainfee.objects.extra(select = 'a':'collected_by.username'}).values('collected_by__username','a').distinct().annotate(b=Sum('amount'))

answered Jun 29 '16 at 15:39

ewianda

61
1
6

`F` appears to be the better way: http://stackoverflow.com/questions/10598940/how-to-rename-items-in-values-in-django – Ciro Santilli OurBigBook.com Apr 23 '17 at 22:20

How to rename fields of an annotated query?

4 Answers4