django - ordering queryset by a calculated field

user_93461 0
Viewed 28642

I want to have a model with calculated fields that I can apply sorting on. For example, let's say that I have the following model:

class Foo(models.Model):
    A = models.IntegerField(..)
    B = models.IntegerField(..)
    C = models.ForeignKey(..)

I want to have a D and an E field that are calculated by the following formulas:

  1. D = A - B
  2. E = A - X (where X is a field of the relevant record of model C)

Implementing this would be trivial if I didn't need to apply sorting; I would just add properties to the model class. However, I need ordering by these fields.

A solution is to fetch all records into memory and do the sorting there, which I conceive a last resort (it will break things regarding pagination).

Is there a way to achieve what I'm trying? Any guidance is appreciated.

EDIT: Denormalization is a no-go. The value of field X changes very frequently and a lot of Foo records are related to one record of model C. An update of X will require thousands of updates of E.

django
6 Answers

I find that without the *args and **kwargs in the save method, it returns an error. And as celopes stated, this is only a solution if you don't mind materializing the computed field in the database.

class Foo(models.Model):
    A = models.IntegerField(..)
    B = models.IntegerField(..)
    C = models.ForeignKey(..)
    D = models.IntegerField(..)
    E = models.IntegerField(..)

    def save(self, *args, **kwargs):
        self.D = self.A - self.B
        self.E = self.A - self.C.X
        super(Foo, self).save(*args, **kwargs)

    class Meta:
        ordering = ["E", "D"]
user_12652700
Related