Using annotate or extra to add field of foreignkey to queryset ? (equivalent of SQL "AS" ?)

Question

I have merged two querysets (qs1 and qs2) which individually work fine, as follows:

qlist = [qs1, qs2]
results = list(chain(qs1, qs2))

So far, so good - the above works. But now I'm trying to order the results using the following:

qlist = [qs1, qs2]
results = sorted(chain(qs1, qs2), key=attrgetter('monthly_fee'))

The problem is that the second queryset (qs2) refers to the monthly_fee through a ForeignKey; whereas qs1 has 'monthly_fee' available. Here is qs2:

qs2 = Offer.objects.select_related('subscription') 
qs2 = qs2.order_by(subscription__monthly_fee) 

And the simplified models:

class Subscription(models.Model): 
    monthly_fee = models.IntegerField(null=False, blank=True, default=0) 
    name = models.CharField(max_length=120, null=True, blank=True)

class Offer(models.Model): 
    promotion_name = models.CharField(max_length=120, null=True, blank=True)
    subscription = models.ForeignKey(Subscription)
    discount = models.IntegerField(null=False, blank=True, default=0) 

I've tried using .annotate() and .extra() to rename the subscription__monthly_fee in the query qs2 as follows:

qs2 = Offer.objects.select_related('subscription').annotate(monthly_fee=subscription__monthly_fee)

But then get the error

global name 'subscription__monthly_fee' is not defined

I am at the point of just hacking this by over-riding the .save() methods of my models to manually add the monthly_fee to each Offer instance whenever an object is created. But just wanted to check whether there isn't a better way ?

Thank you,

Michael

Answer 1

I've used an F expression to achieve this sort of renaming before. Try this:

from django.db.models import F

qs2 = Offer.objects.select_related('subscription').annotate(monthly_fee=F('subscription__monthly_fee'))

Answer 2

OK, I found a way to do this.

qs2 = Offer.objects.select_related('subscription').extra(select={'monthly_fee':'mobile_subscription.monthly_fee'})

where 'mobile' is the name of the Django app. I didn't realize that .extra DOES allow you to follow foreign keys but that you actually have to specify the actual database table and use SQL dot notation.

Is the above the actual correct way we are supposed to do it ? (i.e. dropping in raw SQL table names/fields)

I had been trying to use Django syntax such as .extra(select={'monthly_fee':'subscription__monthly_fee'}) which doesn't work!