CODEWITHSUNDEEP
djangoorm
rjv
rjv

Reputation: 6776

How to rename fields of an annotated query?

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

Upvotes: 7

Views: 13734

Answers (4)

neighbor wang
neighbor wang

Reputation: 171

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

Upvotes: 9

ewianda
ewianda

Reputation: 61

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'))

Upvotes: -1

knbk
knbk

Reputation: 53699

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

Upvotes: 13

Hieu Nguyen
Hieu Nguyen

Reputation: 8623

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.

Upvotes: 0

Related Questions