Django QuerySet ordering by expression [duplicate]

Question

This question already has answers here:

Closed 8 years ago.

Possible Duplicate:
django - ordering queryset by a calculated field

How can i use order_by like order_by('field1'*'field2') For example i have items with price listed in different currencies, so to order items - i have to make currency conversion.

class Currency(models.Model):
    code    = models.CharField(max_length=3, primary_key=True) 
    rateToUSD   = models.DecimalField(max_digits=20,decimal_places=10)

class Item(models.Model):
    priceRT     = models.DecimalField(max_digits=15, decimal_places=2, default=0)
    cur     = models.ForeignKey(Currency)

I would like to have something like:

Item.objects.all().order_by(F('priceRT')*F('cur__rateToUSD'))

But unfortunately it doesnt work, i also faild with annotate. How can i permorm QuerySet ordering by result of value multiplication of 2 model's fields.

Answer 1

Boring way: add an extra field to your Item class called priceUSD, and populate it with an overriden save method. means you don't have the burden of running calculations on every single query - just on every single update. so whether that's good or not will depend on whether you tend to write more or read more (given your question, maybe it's read?)

something like this:

class Item(models.Model):
    priceRT     = models.DecimalField(max_digits=15, decimal_places=2, default=0)
    cur     = models.ForeignKey(Currency)
    priceUSD = models.DecimalField(max_digits=15, decimal_places=2, default=0)

    def save(self,*args,**kwargs)
        self.priceUSD = self.priceRT * self.cur.rateToUSD
        super(Model,self).save(*args,**kwargs)

In my django stuff, whenever I've tried to implement clever calculated fields without storing them in the database, I've usually found it comes with too many disadvantages to be usefult (eg can't query on them, can't sort on them). so storing a proper field in the DB with a custom save method is how I've usually done it, and it works ok. you'll want a bit more error-checking and stuff tho.

Answer 2

Use the extra() method. Specifically the select argument to specify the equation, and the order_by argument to do the ordering.