1

I have a django 3.2/mysql website. One of the tables, Flights, has two columns, angle and baseline. These two columns are combined in a view to display the value altitude using the formula altitude = baseline * tan(angle). I am building a search form for this table, and the user wants to search for flights using a minimum altitude and a maximum altitude.

My normal approach to the problem is to gather the inputs from the search form, create appropriate Q statements, and use query the table with filters based on the Q statements. However, since there is no altitude field, I have to annotate the query with the altitude value for the altitude Q statement to work.

It seems that a query annotation cannot use an expression like math.tan(field_name). For example,

Flights.objects.annotate(altitude_m=ExpressionWrapper(expression=(F('baseline') * math.tan(F("angle"))), output_field = models.FloatField(),),)

generates the error builtins.TypeError: must be real number, not F

whereas the expression

Flights.objects.annotate(altitude_m=ExpressionWrapper(expression=(F('baseline') * F("angle")), output_field = models.FloatField(),),)

does not generate an error, but also does not provide the correct value.

Is there a way to annotate a query on the Flights table with the altitude value so I can add a filter like Q(altitude__gte= min_altitude) to the query? Or, is the best approach to (1) go back to the existing ~1,000 rows in the Flights table and add a column altitude and insert the calculated value, or (2) filter the Flights query on the other search criteria and in python calculate the altitude for each Flights object in the filtered queryset and discard those that don't meet the altitude search criteria?

Note: I have simplified the problem description somewhat by only describing a single altitude value. There are actually 2 altitude values, one in meters and one in feet.

Thanks!

user1045680
  • 815
  • 2
  • 9
  • 19

1 Answers1

1

Use the Tan database function

from django.db.models.functions import Tan

Flights.objects.annotate(
    altitude=ExpressionWrapper(expression=F('baseline') * Tan('angle'), output_field=models.FloatField())
)
Iain Shelvington
  • 31,030
  • 3
  • 31
  • 50
  • Very cool, thanks! I still have a small problem in that the angles are in degrees, and Tan takes radians. I tried `flights = Flights.objects.annotate( altitude=ExpressionWrapper(expression=Tan(45 * (Pi/180)), output_field=models.FloatField()) )`, but got the error: TypeError: unsupported operand type(s) for /: 'type' and 'int'. Not sure how to convert the `angle` field to radians. – user1045680 Jun 04 '22 at 16:22
  • @user1045680 Use the Radians function to convert degrees to radians https://docs.djangoproject.com/en/3.2/ref/models/database-functions/#radians - `Tan(Radians('angle'))` – Iain Shelvington Jun 04 '22 at 16:24
  • Doh! I totally missed that function when I was reading the Django docs on Database Functions. I saw Pi in the list of functions, and went down that path. Thanks so much!!! – user1045680 Jun 04 '22 at 17:09
  • A final question. Does it make the most sense from a performance standpoint to annotate the search queryset with the altitude field and use Q expressions to filter the queryset, as opposed to looping through the queryset and doing the altitude calculation in python and filter that way (e.g create a list of excluded Flights pks and exclude them from the search queryset)? – user1045680 Jun 04 '22 at 17:14
  • @user1045680 99% of the time it will be much slower to do the annotation and filtering in Python – Iain Shelvington Jun 04 '22 at 17:18
  • I ran a little test using the annotation filter versus doing the filtering in python, and found that filtering 617 flight records to find 10 records that met a min-altitude/max_altitude criteria took 0.0277 seconds for the annotation method versus 0.0598 seconds for python filtering. The annotation method was over twice as fast as the python filtering method. Thanks again! – user1045680 Jun 04 '22 at 18:32