django - annotation based on field calculations

Question

Is it possible to annotate objects with calculations (which are not the trivial sum\max\min\count) based on other fields?

Examples:

To add a time annotation field based on an existing TimeField field with addition of a certain time interval
To add an integer annotation that is a certain calculation based on two IntegerFields fields

A workaround for at least some of the cases (e.g. the first of the two examples) would be having an update() command that does not save. That would allow me to change a certain field in a QuerySet, and use it locally without changing the entries in the db. Alas, AFAIK update() always saves.

A solution would be of course to list the queryset and add a field using list comprehension, but I was hoping for a more djangoic way of doing it.

score 0 · Answer 1 · answered Mar 13 '17 at 18:01

I just did this sort of thing (if I’m understanding it correctly) using a RawSQL annotation:

queryset = City.objects.annotate(distance=
        RawSQL('sqrt(((%s - latitude)/0.010)^2 + ((%s - longitude)/0.015)^2)',
               (latitude, longitude))) \
    .filter(distance__lte=radius) \
    .order_by('distance')

It annotates the cities with something akin to their distances to a point (straight through the earth and with other wacky assumptions, but that’s not relevant to this question).

You can often just implement a property on your model class in cases where you don’t need the annotation (here distance) for filtering in the same query.

django - annotation based on field calculations

1 Answers1