ifacethoughts

Calculated Fields In Django

Django does a great job of mapping your model fields to the database columns. The ORM works for most of the common cases.

One of the cases where it does not work is working with calculated fields while displaying reports. Typically, a calculated field is output of some arithmetic calculations on other fields, and so does not need a corresponding database column.

The first solution that I thought of was a property. The set method can be empty, and the get method can do the calculation and return its output. Works well.

The biggest disadvantage of using a property is that you cannot using with the Field Lookups, which is used by most of the QuerySet API. So you cannot filter or order the query set using properties. The only way to do it is bypass the ORM and write custom SQL queries.

If you think that this is too much trouble, there is another solution. You can create a model field for the calculated field and calculate its value when the model is saved. For example,

class Person(models.Model):
    ...
    def save(self):
        self.cost_per_unit = total_cost/no_of_units
        super(Person, self).save()

You can then make it optional and exclude it from form validation. You can also hide it in the admin interface by using the fields or exclude from ModelAdmin options.

The big disadvantage of this solution is that you would keep redundant data in the database. This not only means wastage of space, but can also be a threat to data integrity. However, this lets you use it effectively with the QuerySet API and hence in the admin interface as well.

Both the solutions come with their pros and cons. An ideal solution would be ability to define this through something like annotate, where custom functions can be defined.

Do you know of any other way to represent calculated fields in Django?

Discussion [Participate or Link]

  1. Martin Diers said:

    To me, this is the single biggest thing I would like to see the Django ORM support: True SQL-based Calculated Fields. I would be more than happy if this meant writing SQL fragments to do it. But it would make life ever so much easier if, say, you could create a field that concatenated two other fields, and then sort on the calculated field – something only possible if the calc is done in the SQL.

  2. John said:

    i was searching around the web trying to figure out how to do this too. the one of the responses to this stackoverflow question suggests using the extra QuerySet method:

    http://stackoverflow.com/questions/1652577/django-ordering-queryset-by-a-calculated-field

    i’ve found that it works pretty well. the only downside is that django’s serializors don’t serialize ‘extra’ fields.

  3. Django Calculated Field On Save Requiring Two Saves said:

    [...] http://ifacethoughts.net/2009/07/14/calculated-fields-in-django/ [...]

Say your thought!

If you want to use HTML you can use these tags: <a>, <em>, <strong>, <abbr>, <code>, <blockquote>. Closing the tags will be appreciated as this site uses valid XHTML.

freshthoughts

contactme

Abhijit Nadgouda
iface Consulting
India
+91 9819820312
My bookmarks

badgesand...

This is the weblog of Abhijit Nadgouda where he writes down his thoughts on software development and related topics. You are invited to subscribe to the feed to stay updated or check out more subscription options. Or you can choose to browse by one of the topics.